Advanced Search

RESTORE DATABASE from a list of files

/ Published in: SQL
Save to your folder(s)
Use a file list to generate a RESTORE script. You may want to review the WITH option. This script is for advanced user only. Works only with 1 file per database backup.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. xp_cmdshell 'NET USE T: \\10.106.68.110\w578-db-zip /user:cacpx-platespin\pollus password'
  2. xp_cmdshell 'DIR T:\2009-04-14\BMSQL\*.bak'
  3.  
  4. --SNIPPET:Use a 1 file backup folder to generate RESTORE DATABASE commands
  5. CREATE TABLE #dir(filename VARCHAR(1000)) 
  6. INSERT INTO #dir EXEC master..xp_cmdshell 'DIR T:\2009-04-14\BMSQL\*.bak /b'
  7. DELETE FROM #dir WHERE filename IS NULL
  8. SELECT * FROM #dir
  9.  
  10. SELECT 'RESTORE DATABASE ['+REPLACE(filename,'.BAK','')+'] FROM DISK = ''T:\2009-04-14\BMSQL\'
  11. +filename+''' WITH REPLACE,NORECOVERY,STATS = 10;'
  12. from #dir where filename not in('master.bak','msdb.bak')
  13.  
  14. --drop table #dir

URL: migration, admin, codegenerator

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.