Backup & Restore

Automatically Restore From An SQL Backup File Using TSQL

 

You have a bunch of SQL Database Restores you have to do, and a ton of backup files.  If you’re like me you would prefer to code everything out.  Just write the logic, and make things alot easier.  I wrote some restore logic that might help you.   All you need to do is provide the database name, and the location to backup file.

Ok so what does this do exactly?

It takes the backup file you have, and extrapolates the Logical, and Physical name from the file, and passes it into a restore.   The restore has the following ( with replace, recovery ) so it will definitely overwrite the existing database that it’s restoring too.

Dependencies:
Sp_KillAllProcessInDB
Can be found here:  http://techresource.appvui.biz/1547686/ms-sql-server-kill-processes-particular-db.html

 


use master;
set nocount on
declare @database_name varchar(255)
declare @backup_file_name varchar(255)
set @database_name = ‘MyDatabaseNameHere’
set @backup_file_name = ‘MyPathToBackupFileHere’
declare @filelistonly table
(
logicalname nvarchar(128)
, physicalname nvarchar(260)
, [type] char(1)
, filegroupname nvarchar(128)
, size numeric(20,0)
, maxsize numeric(20,0)
, fileid bigint
, createlsn numeric(25,0)
, droplsn numeric(25,0)
, uniqueid uniqueidentifier
, readonlylsn numeric(25,0)
, readwritelsn numeric(25,0)
, backupsizeinbytes bigint
, sourceblocksize int
, filegroupid int
, loggroupguid uniqueidentifier
, differentialbaselsn numeric(25,0)
, differentialbaseguid uniqueidentifier
, isreadonl bit
, ispresent bit
, tdethumbprint varbinary(32)
)
insert into
@filelistonly exec (‘restore filelistonly from disk = ”’ + @backup_file_name + ””)
declare @restore_line0 varchar (255)
declare @restore_line1 varchar (255)
declare @restore_line2 varchar (255)
declare @stats varchar (255)
declare @move_files varchar (max)
set @restore_line0 = (‘use master;’)
set @restore_line1 = (‘exec master..sp_killallprocessindb ”’ + @database_name + ”’;’)
set @restore_line2 = (select ‘restore database [‘ + @database_name + ‘] from disk = ”’ + @backup_file_name + ”’ with replace, recovery, ‘)
set @stats = (‘stats = 20;’)
set @move_files = ”
select @move_files = @move_files + ‘move ”’ + logicalname + ”’ to ”’ + physicalname + ”’,’ + char(10) from @filelistonly order by fileid asc

exec
(
@restore_line0
+ @restore_line1
+ @restore_line2
+ @move_files
+ @stats
)


 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s