What are the challenges you face when working across database platforms? Take the survey

multiple restores in 1 job?

freddy12345freddy12345 Posts: 19 Bronze 2
edited April 4, 2007 5:18PM in SQL Backup Previous Versions

i need to move about 30 databases (all the user DBs) rom one server to anew server. i selected Backup all user databases and the backup job ran fine.

now i want to restore all those on the new server,and don't prefer to do it one restore job at a time. (the .SQBs are all in one folder.) is there an easy way to do this with SQL Backup? The restore DBs will have exactly the same name as they did on the source server.




  • Options
    peteypetey Posts: 2,358 New member
    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
    	IF @dbname <> 'master' AND @dbname <> 'model' AND @dbname <> 'tempdb' AND @dbname <> 'msdb'
    		PRINT 'EXEC master..sqlbackup N''-sql "RESTORE DATABASE [' + @dbname + '] FROM DISK = [' + @path + '*' + @dbname + '*.sqb]"'''
    	FETCH NEXT FROM cur_dbs INTO @dbname
    CLOSE cur_dbs
    DEALLOCATE cur_dbs

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    freddy12345freddy12345 Posts: 19 Bronze 2
    Thank you! This looks like it'll do the trick.

Sign In or Register to comment.