0

I am working with a database that has a dynamic filegroup structure. The number of filegroups and filegroup files can and do change.

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?

Metaphor
  • 906
  • 1
  • 11
  • 30

2 Answers2

1

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?

You just have to use a good existing tool (hint : restoregene). It does take care of multiple filegroups dynamically.

dont put all the filegroups in one folder - as if the drive fails, you loose your entire database. The whole point of having filegroups is to have them on different disks/LUNs.

EDIT: Based on your comment

The purpose of the restore script is to restore a copy of the production database to QA and CI servers.

PowerShell is the way to go - Start-SqlServerMigration.ps1

Basically, you have to use WITH MOVE option in restore command since the disk and folder layouts wont match from PROD to QA or other servers.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
1

No, SQL Server doesn't have a "put all data files or log files or files from filegroup x in folder y" command. If the folder structure on the destination server doesn't match the folder structure on the source, or if there are conflicting files in the destination server, you have to supply WITH MOVE for every file that can't find a home identical to the source.

Now, whether you have to manually script that or you build it dynamically is a different issue - it is certainly possible to automate that (say, at the time of backup, or at the time you know you're going to restore, though closer to the backup is safer because of the changes you mentioned), and it is certainly possible to make sure the destination server has a matching folder structure so that it isn't necessary.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624