CODEWITHSUNDEEP
sqlsql-server
Renju
Renju

Reputation: 1

Restore Partitioned database into multiple filegroups

I need to restore a partitioned database that has multiple file groups. In the restore option in the SSME I need to edit manually all the path of the filegroups restore as option it little bit tedious as it having more than 150 filegroups

eg:USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
   FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY,


      MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', 
       MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'

GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH RECOVERY
GO

Here I need to specify multiple MOVE commands for all my filegroups, this is a tedious task when having 100s of filegroups

   MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', 
           MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'

I need to move the files into the path I provided as a parameter.

Upvotes: 0

Views: 678

Answers (2)

renjucool
renjucool

Reputation: 312

Please find these queries http://code.msdn.microsoft.com/Sql2008PartionedDb/

Upvotes: 1

Andomar
Andomar

Reputation: 238196

You could generate the MOVE list with a query like:

select 'MOVE ''' + name + ''' TO ''D:\MyData\' + name + '.mdf'','
from sys.filegroups

Upvotes: 2

Related Questions