SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create Restore Command from DB Backup File
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/07/2006 :  18:16:06  Show Profile  Reply with Quote
This script will read the contents of a DB backup file, and generate a restore command.

Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.

This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.

Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.


-- Create Restore Database Command from DB Backup File

set nocount on
go

declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file 
'\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'


create table #header (
BackupName		nvarchar(128)	null,
BackupDescription	nvarchar(128)	null,
BackupType		int		not null,
ExpirationDate		datetime	null,
Compressed		int		not null,
Position		int		not null,
DeviceType		int		not null,
UserName		nvarchar(128)	not null,
ServerName		nvarchar(128)	not null,
DatabaseName		nvarchar(128)	not null,
DatabaseVersion		int		not null,
DatabaseCreationDate	datetime	not null,
BackupSize		decimal(28,0)	not null,
FirstLsn		decimal(28,0)	not null,
LastLsn			decimal(28,0)	not null,
CheckpointLsn		decimal(28,0)	not null,
DatabaseBackupLsn	decimal(28,0)	not null,
BackupStartDate		datetime	not null,
BackupFinishDate	datetime	not null,
SortOrder		int		not null,
CodePage		int		not null,
UnicodeLocaleId		int		not null,
UnicodeComparisonStyle	int		not null,
CompatibilityLevel	int		not null,
SoftwareVendorId	int		null,
SoftwareVersionMajor	int		null,
SoftwareVersionMinor	int		null,
SoftwareVersionBuild	int		null,
MachineName		nvarchar(128)	not null,
Flags			int		null,
BindingID		uniqueidentifier null,
RecoveryForkID		uniqueidentifier null,
Collation		nvarchar(128)	null,
Seq			int		not null
	identity(1,1),
)

create table #filelist (
LogicalName	nvarchar(128)	not null,
PhysicalName	nvarchar(128)	not null,
Type		nvarchar(10)	not null,
FileGroupName	nvarchar(128)	null,
Size		decimal(28,0)	not null,
MaxSize		decimal(28,0)	not null,
Seq		int		not null
	identity(1,1),
)

insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')

insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')

declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)

select
	[--Restore--]	=
	case
	when a.Seq = 1
	then
	@cr+
	@cr+'restore database '+c.DatabaseName+
	@cr+'from disk ='+@cr+@tab+''''+
	@backup_path+''''+@cr+'with'+@cr
	else ''
	end+
	@tab+'move '''+a.LogicalName+
	'''	to '''+a.PhysicalName+''' ,'+
	case
	when a.Seq = b.Seq
	then
	@cr+@tab+'replace, stats = 5 , recovery'
	else ''
	end
from
	#filelist a
	cross join
	( select Seq = max(b1.Seq) from #filelist b1 ) b
	cross join
	( select DatabaseName = max(c1.DatabaseName) 
	  from #header c1 ) c
order by
	a.Seq
go
drop table #header
drop table #filelist

Results, modify as needed:

--Restore--
--------------------------------------------------------------------

restore database MY_DB
from disk =
	'\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'
with
	move 'PRIMARY'	to 'D:\MSSQL\DATA\MY_DB_PRIMARY.MDF' ,
	move 'DATA1'	to 'D:\MSSQL\DATA\MY_DB_DATA1.NDF' ,
	move 'DATA2'	to 'D:\MSSQL\DATA\MY_DB_DATA2.NDF' ,
	move 'LOG'	to 'D:\MSSQL\DATA\MY_DB_LOG.LDF' ,
	replace, stats = 5 , recovery








CODO ERGO SUM

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 09/07/2006 :  18:34:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
Hey, you need a blog for all of these! They will get more exposure...

Tara Kizer
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/07/2006 :  18:42:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
i second that.
just so you know MVJ, i've cursed you a few times for not having a blog
when i tried to find one of your scripts




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/07/2006 :  21:09:23  Show Profile  Reply with Quote
This script one was something I hacked together in a few minutes from a considerably more useful and flexible stored procedure that I wrote. I didn't feel like posting that; I don't want to give away everything. I just wanted to put something out there that would give people a good start.


If you're looking for my scripts, they're in the Script Library.

If you're looking for my datetime related scripts, I have links to all of them in this post.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762





CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 09/08/2006 :  01:19:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm just looking for your blog!

Tara Kizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000