ronmichael
6/6/2013 - 6:44 PM

This script will generate the scripts you need to backup all of your databases to a specific folder (@sourcebackupfolder) and then restore t

This script will generate the scripts you need to backup all of your databases to a specific folder (@sourcebackupfolder) and then restore them from a specific folder (@targetbackupfolder) on the new server. You could enable COMPRESSION in the backup if your server supports that.

script-backup-and-restore.sql
content_copyfile_download
declare @sourcebackupfolder varchar(255), @targetbackupfolder varchar(255), @targetdatafolder varchar(255), @targetlogfilder varchar(255)

select @sourcebackupfolder = 'c:\moving\', @targetbackupfolder='d:\moving\', @targetdatafolder='c:\sql\data\', @targetlogfilder='c:\sql\logs\';

 
select 'BACKUP DATABASE [' + name + '] TO  DISK = ''' + @sourcebackupfolder + name 
    + '.bak'' WITH INIT ,  NOUNLOAD ,  NAME = ''' + name + ''',  NOSKIP ,  STATS = 100,  NOFORMAT'
from sys.databases
where name not in ('master','msdb','model','tempdb');


select 'RESTORE DATABASE [' + name + '] FROM  DISK = ''' + @targetbackupfolder + name 
    + '.bak'' WITH REPLACE, STATS=100, '
+ 'MOVE ''' + (select name from master.sys.master_files mf where mf.database_id=db.database_id and mf.type=0)
    + ''' to ''' + @targetdatafolder + name + '.mdf'', '
+ 'MOVE ''' + (select name from master.sys.master_files mf where mf.database_id=db.database_id and mf.type=1)
     + ''' to ''' + @targetlogfilder + name + '.ldf''' 
from sys.databases db
where db.name not in ('master','msdb','model','tempdb');