SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loading database with the latest .bak file


Loading database with the latest .bak file

Author
Message
sybase08
sybase08
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
Hi everyone,

I have .bak files which getting copied from a source server and brought into the target server, however, I have 2-3 .bak files for each database with different time-stamps. I want to load the database with the latest backup file (latest time-stamp). How can i acheive this? this is the script i am using: use master
goRestore database WebData from
disk='J:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\WebData\*.bak'
with Replace,
MOVE 'WebData' TO 'E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData.mdf',
MOVE 'WebDatalog' TO 'E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData_log.ldf'
go Thanks in advance for your help!
Elisabeth Rédei
Elisabeth Rédei
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 788
Hi,

It's probably the late hour but I don't quite understand... what kind of backups are in the .bak files? You can use RESTORE HEADERONLY to find out if you don't know; this will also show you first and last LSN (Log sequence numbers) which tells you in which order they were taken.

If they are all full backups your script looks fine (assuming you want to replace the existing database rather than creating a new).

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Elisabeth Rédei
Elisabeth Rédei
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 788
Oh now I get it.... you want it to automagically grab the last file? http://www.sqlservercentral.com/articles/Log+Shipping/62676/ has one type of solution for this.

Optionally, not necessarily preferably... you can use xp_Cmdshell and Dir the directory and do something clever with the output (i.e. parse out the timestamp etc.):

Insert myFileListTable Exec master.dbo.xp_cmdshell 'dir c:\Backupfiles\'

This blog contains a solution http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx that avoids xp_cmdshell and having to parse the output.

http://www.codecomments.com/archive352-2006-1-786972.html has some other options involving scripting.

HTH!

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
sybase08
sybase08
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
Hi Elizabeth,

Thanks for your response!

The backup scripts have been generated by MP, that's why they all have year-month-day-hours at the end of backup files.

This is a server to server loading...I've already automated a script to bring the backup files to my target server.

In order to load my database in the target, I need to add some coding before the load SQL-script so it goes and grab the latest backup file.

I'm very new in sql server and need more explanation with the solution.

I really appreciate your help!
Elisabeth Rédei
Elisabeth Rédei
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 788
Hi,

Sigh.. the solution in http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx is MUCH prettier and I am squirming a bit as I am writing this post Tongue

First of all you need to enable xp_cmdshell (which by default is disabled for a reason Smile ).

sp_configure 'xp_cmdshell',1
reconfigure with override

Then PLEASE PLEASE set up a proxy account for xp_cmdshell with a low privilege Windows user (see BoL "sp_xp_cmdshell_proxy_account" for instructions).

The following script is not doing anything meaningful, it is just grabbing a bunch of timestamped files into a table and then I am doing a backup using the name of the last file (it was easier to setup).

This works for me because of how the files look: SamplesDB_20081029184501.trn, you might have to be little bit more clever than me to be absolutely positive that you are indeed grabbing the last file Smile

Smile

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Attachments
ParseBakFiles.txt (49 views, 1.00 KB)
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26774 Visits: 17343
you shouldnt need to and personally i wouldnt use xp_cmdshell. Use one of the file access extended stored procedures to find the files in the directory and list them to a recordset

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
sybase08
sybase08
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
Hi Perry,

can you pls elaborate more what steps should be taken using File Access SPs to acheive this.

I'm very new to sqlserver.


Thanks a lot,
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26774 Visits: 17343
me and my big mouth, forgive me it looks as though XP_FileExist doesnt accept wildcards Sad

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81875 Visits: 19217
Honestly I wouldn't do this in T-sQL, at least not directly in the server. Do it in a package, contain it, use a proxy for Agent, and then start the job if you need to restore the backups (or schedule it).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sybase08
sybase08
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
like how exactly? I need the steps.
I really don't know what "package" you're referring to.

pls explain it in a way you are explaining to a junior dba.


Thanks a lot,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search