Loading database with the latest .bak file

  • 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!

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



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




  • 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!

  • 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 🙂




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

  • 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,

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

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

  • 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,

  • 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



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




    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!

  • 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

    DROP TABLE #GetFileList

  • DECLARE @vcCmdVARCHAR(666)

    CREATE TABLE #tbl_Backups




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


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


    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'



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




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

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