<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Loading database with the latest .bak file
15 posts, Page 1 of 2
1
2
»»
Loading database with the latest .bak file
Rate Topic
Display Mode
Topic Options
Author
Message
sybase08
sybase08
Posted Friday, October 31, 2008 12:54 PM
Forum 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
Elisabeth Rédei
Elisabeth Rédei
Posted Friday, October 31, 2008 3:36 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175,
Visits: 676
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
Elisabeth Rédei
Elisabeth Rédei
Posted Friday, October 31, 2008 5:28 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175,
Visits: 676
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
sybase08
sybase08
Posted Friday, October 31, 2008 6:24 PM
Forum 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
Elisabeth Rédei
Elisabeth Rédei
Posted Saturday, November 01, 2008 6:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175,
Visits: 676
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
(
5 views,
1.17 KB
)
Post #595426
Perry Whittle
Perry Whittle
Posted Saturday, November 01, 2008 4:50 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,242,
Visits: 11,256
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
sybase08
sybase08
Posted Sunday, November 02, 2008 4:54 AM
Forum 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
Perry Whittle
Perry Whittle
Posted Sunday, November 02, 2008 5:37 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,242,
Visits: 11,256
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Sunday, November 02, 2008 7:35 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 5:37 PM
Points: 31,521,
Visits: 13,855
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
sybase08
sybase08
Posted Sunday, November 02, 2008 8:49 AM
Forum 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 »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
