Loading database with the latest .bak file

Question

Loading database with the latest .bak file

sybase08

Mr or Mrs. 500

Points: 507
More actions
October 31, 2008 at 12:54 pm

#195405

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!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

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 Hall of Fame Points: 3873 More actions · Answer 2

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 Mr or Mrs. 500 Points: 507 More actions · Answer 3

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 Hall of Fame Points: 3873 More actions · Answer 4

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 😛

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

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 Mr or Mrs. 500 Points: 507 More actions · Answer 6

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 SSC Guru Points: 234013 More actions · Answer 7

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" 😉

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 8

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

sybase08 Mr or Mrs. 500 Points: 507 More actions · Answer 9

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,

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 10

you can still use the scripting or the T-SQL, but do it in an SSIS package.

You use BIDS to create a package, add a T-SQL task, and include your T-SQL code. If you need to do some scripting, there are more programmability options in SSIS for working with files. Personally I've used VBScript for years to parse out files and order them, and choose which one to restore. To give more details, you'd have to explain more about what the files look like, are you restoring all databases, all that have bak files, how often, etc. There are a lot of details here.

What I'd recommend is to look at Powershell if you can install that on your server. Some people might not allow an add-on like this. It's native in 2008, but must be added in 2005. (http://www.databasejournal.com/features/mssql/article.php/3681061)

Otherwise look at VBScript and the FileSystemObject. You can use that to find all the files in the folder and then loop through them to look for the latest one (by name or modified date) for a particular database.

You need to do some work here as it is a lot of writing to get this done for you. If oyu start working on this, you can ask specific questions about parts you don't understand.

A few links

http://www.sqlservercentral.com/scripts/Replication/31168/

http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/31995/

sybase08 Mr or Mrs. 500 Points: 507 More actions · Answer 11

Hi Steve,

Thanks for your comments! it was great! Please stay in touch with me till I get the result on this task...

It's only one database which needs to be restored once a day

(11:30 pm) and all 2-3 .bak files brought in to the target server belong to this specific database but with different day time-stamps. e.i.

dbname_backup_200810292330.bak

dbname_backup_200810302335.bak

dbname_backup_200810312337.bak

what I thought was to create a server JOB and ask'em to go to this directory/file...grab the bak file and run the load script at the end.

but i don't know how to tell the script to pick the latest file.

As you mentioned I can not add any third party tools on the server. there are some processes...even SSIS has not been installed on this machine. (sqlserver 2005)

I'll check for PowerShell...To write in VBScript what tools can be used? maybe one has been installed there.

Thank you so much and talk to you later!

vmenon SSC Veteran Points: 274 More actions · Answer 12

I will give you an outline of how to achieve this

DECLARE @InsertedRecords INT

CREATE TABLE #GetFileList

(

SrNo Int IDENTITY (1,1),

FileListOutPut Varchar(1000)

)

INSERT INTO #GetFileList (FileListOutPut)

EXEC master.dbo.xp_cmdshell 'DIR \\ \*.bak /OD /-C /B'

-- /OD is the sort order by date/time (oldest first)

-- /-C to disable display of seperator in the size value

-- /B no heading information or summary

DELETE FROM #GetFileList

WHERE FileListOutPut IS NULL

SELECT @InsertedRecords= COUNT(*) FROM #GetFileList

SELECT FileListOutPut AS BackupFile_Name FROM #GetFileList

WHERE SrNo = @InsertedRecords

DROP TABLE #GetFileList

zNick SSC Eights! Points: 842 More actions · Answer 13

DECLARE @vcCmdVARCHAR(666)

CREATE TABLE #tbl_Backups

(

BackupNameVARCHAR(200)

)

--assumes that the latest file was put in last. (order by files newest file first)

SET @vcCmd = 'DIR "J:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\WebData\*.bak" /b /o-d'

INSERT INTO #tbl_Backups

EXEC xp_cmdshell @vcCmd

DELETE FROM #tbl_Backups WHERE BackupName IS NULL

SET @vcCmd = SELECT TOP 1 FROM #tbl_Backups

RESTORE DATABASE WebData

FROM DISK='J:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\WebData\'+@vcCmd+''

WITH

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'

,REPLACE

GO

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 14

Hi again,

The link I sent you in my post the day before yesterday does EXACTLY what you want to do in C#:http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx.

There is a Step-by-Step that ends in a table with two columns, Filename and last_write_time which contains the LastWriteTime of the FileInfo object (i.e. the timestamp set by the filesystem).

You can use it as it is and just change paths.

HTH!

/Elisabeth

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