Restore last backup to a different server/database

,

Finds the last available backup for a specified database (local or remote server), then restores the backup onto an existing database on the local server.
The script will kill all non-system processes in the destination database to allow the script to be scheduled at night regardless is anyone is still connected.
The logical and physical names are preserved incase of non-standard data and log file suffixes.

I wrote this script as the developers I work with are constantly asking for copies of live databases onto testing servers.

It has been tested on multiple servers in my environment.
The source server must be a linked server to the current server to allow the script to find the last backup details.
The script also relies on the standard administrative shares to access the backupfile, so the login context must have administrative access to the source server.

/*
Script to restore the last backup of the source database specified below onto the destination database on the current server

WARNING : Will attempt to kill all non-system users in the destination database!

Author 		: Kris Lendon
Date Created 	: 28/03/2003
Version		: 1.0

*/

declare @sourceserver as nvarchar(255),
	@sourcedb as nvarchar(255),
	@destinationdb as nvarchar(255),
	@systemaccount as nvarchar(255)
/*---------------------------------------------------- Change Parameters Block Begin -----------------------------------------------*/

set @sourceserver='sourceserver'		--Server where the backup was executed
set @sourcedb='production'			--Source Database to copy from
set @destinationdb='testing'			--Destination Database (Will be overwritten)
set @systemaccount='DOMAIN\SQLSystem'		--Service Account details (Needed so the script will not attempt to kill system processes)

/*---------------------------------------------------- Change Parameters Block End -------------------------------------------------*/

--Check if source server exists and is a registered linked server
if ((select count (*) from sysservers where srvname = @sourceserver)=0)
begin
	raiserror('Source server does not exist or is not a registered linked server',1,1)
	goto finish
end

--Check if the Destination Database exists
if ((select count (*) from sysdatabases where name = @destinationdb)=0)
begin
	raiserror('Destination Database does not exist',1,1)
	goto finish
end

--Find the last backup in the set for the source database

declare @physicalname as nvarchar(255),
	@loglogical as nvarchar(255),
	@datalogical as nvarchar(255),
	@spid as nvarchar(4),
	@killcommand as nvarchar(255),
	@cmd as nvarchar(255),
	@unc as nvarchar(255),
	@exec as nvarchar(1000)


create table #backupfile
(
	filename nvarchar(255)
)

set @exec ='select top 1 physical_device_name from [' + @sourceserver + '].msdb.dbo.backupfile bf
			inner join [' + @sourceserver + '].msdb.dbo.backupset bs on bf.backup_set_id=bs.backup_set_id
			inner join [' + @sourceserver + '].msdb.dbo.backupmediafamily bm on bs.media_set_id=bm.media_set_id
			where bf.file_type=''D'' and bs.database_name=''' + @sourcedb + ''' and bs.server_name=''' + @sourceserver + ''' 
			order by bs.backup_finish_date desc'

insert #backupfile exec(@exec)
set @physicalname=(select top 1 filename from #backupfile)
drop table #backupfile

if @physicalname is null 
begin
	raiserror('No backups available for the specified database',1,1)
	goto finish
end

--Checks to see if the location of the backup is on a physical drive or a network share and sets the variable accordingly

if left(@physicalname, 1) between 'a' and 'z' set @unc='\\' + @sourceserver + '\' + left(@physicalname, 1) + '$' + right(@physicalname, len(@physicalname)-2)
if left(@physicalname, 1) ='\' set @unc=@physicalname
if @unc is null 
begin
	raiserror('Backupfile is not a file on a physical disk',1,1)
	goto finish
end

--Restore the last backup onto the destination database (local server)
create table #backupdetails
(
	LogicalName nvarchar(255),
	PhysicalName nvarchar(255),
	Type nvarchar(1),
	FileGroupName nvarchar(255),
	Size bigint,
	MaxSize bigint
)

set @exec='restore filelistonly from disk=''' + @unc + ''''
insert #backupdetails exec(@exec)
set @datalogical=(select logicalname from #backupdetails where type='d')
set @loglogical=(select logicalname from #backupdetails where type='l')
drop table #backupdetails

declare @logphysical as nvarchar(255)
declare @dataphysical as nvarchar(255)

create table #destdetails
(
	filetype char(1),
	physicalname nvarchar(255)
)
set @exec='select top 1 ''l'', filename from [' + @destinationdb + '].dbo.sysfiles where status & 0x40=0x40'
insert #destdetails exec(@exec)
set @logphysical=(select physicalname from #destdetails where filetype='l')
truncate table #destdetails
set @exec='select top 1 ''d'', filename from [' + @destinationdb + '].dbo.sysfiles where status & 0x40<>0x40'
insert #destdetails exec(@exec)
set @dataphysical=(select physicalname from #destdetails where filetype='d')
drop table #destdetails




--Kill all users in Destination Database except system users

while (select count(*) from sysprocesses pc
inner join sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.loginame<>@systemaccount and pc.spid<>@@SPID and sd.name=@destinationdb)<>0
BEGIN
	set @spid=(select top 1 pc.spid from sysprocesses pc
	inner join sysdatabases sd on pc.dbid=sd.dbid
	where pc.loginame<>'sa' and pc.loginame<>@systemaccount and pc.spid<>@@SPID and sd.name=@destinationdb)
	set @killcommand='kill ' + @spid
	--print 'Killing ' + @spid
	exec(@killcommand)
END


--Restore Database
RESTORE DATABASE @destinationdb
   FROM DISK = @unc
WITH
REPLACE,RECOVERY,
MOVE @datalogical TO @dataphysical, 
MOVE @loglogical TO @logphysical

finish:

Rate

Share

Share

Rate