Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Loading database with the latest .bak file Expand / Collapse
Author
Message
Posted Friday, October 31, 2008 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 28, 2009 7:03 AM
Points: 5, 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!
Post #595224
Posted Friday, October 31, 2008 3:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 7:59 AM
Points: 177, Visits: 757
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
Post #595316
Posted Friday, October 31, 2008 5:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 7:59 AM
Points: 177, Visits: 757
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
Post #595340
Posted Friday, October 31, 2008 6:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 28, 2009 7:03 AM
Points: 5, 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!
Post #595346
Posted Saturday, November 1, 2008 6:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 7:59 AM
Points: 177, Visits: 757
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 :P

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

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 :)

:)

/Elisabeth



elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei


  Post Attachments 
ParseBakFiles.txt (6 views, 1.17 KB)
Post #595426
Posted Saturday, November 1, 2008 4:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #595496
Posted Sunday, November 2, 2008 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 28, 2009 7:03 AM
Points: 5, 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,
Post #595542
Posted Sunday, November 2, 2008 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
me and my big mouth, forgive me it looks as though XP_FileExist doesnt accept wildcards

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

"Ya can't make an omelette without breaking just a few eggs"
Post #595545
Posted Sunday, November 2, 2008 7:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #595563
Posted Sunday, November 2, 2008 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 28, 2009 7:03 AM
Points: 5, 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,
Post #595577
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse