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

multiple restores in 1 job?

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

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.

THanks,

Fred

Comments

  • 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
    
    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.
    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.

    Fred
Sign In or Register to comment.