multiple restores in 1 job?

freddy12345

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

petey

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.

freddy12345

Thank you! This looks like it'll do the trick.

Fred