Sql-server – Restoring database without worring about partitions

backuprestoresql-server-2005

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

Best Answer

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.

Related Question