Batch backup and restore all databases from one sql server to another

Batch Backup Script:

 

/*
-- script to generate batch backup script for all databases on one sql server 
-- author: George Wen  http://technet.erpsalon.org
-- data written: 14/10/2011
*/

set nocount on
if exists (select * from sys.objects where name='tempdatabases')  drop table tempdatabases
declare @databaseid int
declare @dbname varchar(250)
declare @sql varchar(max)
declare @backupsql varchar(max)
declare @backuppath varchar(250)
set @backuppath= '\\backupserver\sql_backups\'

select database_id,name into tempdatabases from sys.databases  where name not in ('master','tempdb','model','msdb')
select @databaseid=min(database_id) from tempdatabases
select @dbname = name from tempdatabases where database_id=@databaseid

while @databaseid is not null
begin
--begin loop

select @backupsql ='backup database [' + @dbname +'] to disk=''' + @backuppath + @dbname + '.bak'''

print @backupsql
exec(@backupsql)  --run the backup
--end loop
select @databaseid = min(database_id) from tempdatabases where database_id>@databaseid
select @dbname = name from tempdatabases where database_id=@databaseid
end

Batch Restore Script:

 

/*
-- script to generate batch script for restoring all databases from one sql server to another
-- author: George Wen  http://technet.erpsalon.org
-- data written: 14/10/2011
-- assign @oldpath,@newpath,@backuppath appropriate value according to your env,it is also assumed that backup file are named as database_name.bak.
*/

set nocount on

if exists (select * from sys.objects where name='tempdatabases')  drop table tempdatabases

declare @databaseid int
declare @dbname varchar(50)
declare @sql varchar(max)
declare @restoresql varchar(max)
declare @fname varchar(50)
declare @filename varchar(250)
declare @fileid int,@maxfileid int
declare @oldpath varchar(250)
declare @newpath varchar(250)
declare @backuppath varchar(250)

set @oldpath = 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'
set @newpath = 'M:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'
set @backuppath= '\\backupserver\sql_backups\'

select database_id,name into tempdatabases from sys.databases  where name not in ('master','tempdb','model','msdb')

select @databaseid=min(database_id) from tempdatabases
select @dbname = name from tempdatabases where database_id=@databaseid

while @databaseid is not null
begin
--begin loop
set @sql = 'select * into tempsysfiles from [' + @dbname + '].sys.sysfiles'
exec (@sql)

select @restoresql ='restore database [' + @dbname +'] from disk=''' + @backuppath + @dbname + '.bak''' + ' with  '
select @maxfileid = max(fileid) from tempsysfiles
select @fileid = min(fileid) from tempsysfiles

select @fname=name, @filename=filename from tempsysfiles where fileid=@fileid
while @fileid is not null
begin
select @restoresql = @restoresql + ' move ''' + @fname + ''' to ''' + @filename+''''
if @fileid <> @maxfileid
select @restoresql = @restoresql + ','
select @fileid=min(fileid) from tempsysfiles where fileid>@fileid
select @fname=name, @filename=filename from tempsysfiles where fileid=@fileid
end

print @restoresql
-- this script is to be run on source server with a linkedserver named 'DestinationServer' linked to the restore destination.
-- exec(@restoresql) at DestinationServer 
if OBJECT)ID('tempsysfiles') is not null drop table tempsysfiles
--end loop
select @databaseid = min(database_id) from tempdatabases where database_id>@databaseid
select @dbname = name from tempdatabases where database_id=@databaseid
end