multiple restores in 1 job?

• 
  There isn't an option in the GUI that would allow you to restore multiple databases.
  
  If your database names and backup file names are fairly unique, you can run the following script on your production server to generate the necessary restore scripts:
  

  DECLARE @path nvarchar(256)
  SET @path = 'F:\backups\'
  
  DECLARE cur_dbs CURSOR FOR SELECT name FROM master..sysdatabases 
  DECLARE @dbname sysname
  
  OPEN cur_dbs
  FETCH NEXT FROM cur_dbs INTO @dbname
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  	IF @dbname <> 'master' AND @dbname <> 'model' AND @dbname <> 'tempdb' AND @dbname <> 'msdb'
  	BEGIN
  		PRINT 'EXEC master..sqlbackup N''-sql "RESTORE DATABASE [' + @dbname + '] FROM DISK = [' + @path + '*' + @dbname + '*.sqb]"'''
  	END
  	
  	FETCH NEXT FROM cur_dbs INTO @dbname
  END
  
  CLOSE cur_dbs
  DEALLOCATE cur_dbs
  
  SET NOCOUNT OFF
  

  
  This will generate the necessary restore commands using wildcards to identify the file names e.g.
  

  EXEC master..sqlbackup N'-sql "RESTORE DATABASE [pubs] FROM DISK = [F:\backups\*pubs*.sqb]"'
  

  If you had a database named pubs_backup, this script will not work as the search pattern *pubs*.sqb will also pick up the backup file for pubs_backup.
  
  Note that the data and log files will also be restored to the exact same paths as located on the production server, and those paths must exist prior to the restore.

  petey April 02, 2007 22:31

  - 0 +
• 
  Thank you! This looks like it'll do the trick.
  
  Fred

  freddy12345 April 04, 2007 17:18

  - 0 +

Add comment

Please sign in to leave a comment.