4

On our our production database there is a partitioned table called FullLog. This is updated via triggers so we know who changed the database and when. Which is a problem for me when I restore the production database on our development servers as it doesn't have an F drive. Is there a easier way I can tell it to move to the specified location without knowing how many ndf files are there.

RESTORE DATABASE MartinUAT
  FROM DISK = 'C:\DBBaks\MARTIN_PreChange_2015_06_09_0800.bak'
  WITH MOVE 'CMTS_dat' TO 'e:\SQLRestore\MARTIN_UAT_.mdf',
  MOVE 'P1' TO 'e:\SQLRestore\MARTIN_UAT__1.ndf',
  MOVE 'P2' TO 'e:\SQLRestore\MARTIN_UAT__2.ndf',
  MOVE 'P3' TO 'e:\SQLRestore\MARTIN_UAT__3.ndf',
  MOVE 'P4' TO 'e:\SQLRestore\MARTIN_UAT__4.ndf',
  MOVE 'P5' TO 'e:\SQLRestore\MARTIN_UAT__5.ndf',
  MOVE 'P6' TO 'e:\SQLRestore\MARTIN_UAT__6.ndf',
  MOVE 'P7' TO 'e:\SQLRestore\MARTIN_UAT__7.ndf',
  MOVE 'P8' TO 'e:\SQLRestore\MARTIN_UAT__8.ndf',
  MOVE 'P9' TO 'e:\SQLRestore\MARTIN_UAT__9.ndf',
  MOVE 'P10' TO 'e:\SQLRestore\MARTIN_UAT__10.ndf',
  MOVE 'P11' TO 'e:\SQLRestore\MARTIN_UAT__11.ndf',
  MOVE 'P12' TO 'e:\SQLRestore\MARTIN_UAT__12.ndf',
  MOVE 'P13' TO 'e:\SQLRestore\MARTIN_UAT__13.ndf',
  MOVE 'P14' TO 'e:\SQLRestore\MARTIN_UAT__14.ndf',
  MOVE 'P15' TO 'e:\SQLRestore\MARTIN_UAT__15.ndf',
  MOVE 'P16' TO 'e:\SQLRestore\MARTIN_UAT__16.ndf',
  MOVE 'P17' TO 'e:\SQLRestore\MARTIN_UAT__17.ndf',
  MOVE 'P18' TO 'e:\SQLRestore\MARTIN_UAT__18.ndf',
  MOVE 'P19' TO 'e:\SQLRestore\MARTIN_UAT__19.ndf',
  MOVE 'P20' TO 'e:\SQLRestore\MARTIN_UAT__20.ndf',
  MOVE 'CMTS_log' TO 'e:\SQLRestore\MARTIN_UAT__21.ldf'
  ,REPLACE 
3

I would suggest automating generation of the restore script. There are a few versions of this out there but Paul Brewer has a T-SQL and PowerShell version available.

I have seen some setups as well that just include generating a restore script when the backup is accomplished.

Which, just a note that a new open source backup solution was released by Sean and Jen McCown called Minion Backup. It includes the functionality of generating your restore scripts and migrating backups to development.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.