Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Sunday, November 2, 2008 9:43 AM



Group: Administrators
Last Login: Today @ 8:38 AM
Points: 34,366, Visits: 18,586
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. (

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

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #595583
Posted Sunday, November 2, 2008 12:56 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 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!
Post #595601
Posted Tuesday, November 4, 2008 1:59 AM


Group: General Forum Members
Last Login: Monday, September 6, 2010 11:19 PM
Points: 22, Visits: 97
I will give you an outline of how to achieve this

DECLARE @InsertedRecords INT

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


SELECT @InsertedRecords= COUNT(*) FROM #GetFileList

SELECT FileListOutPut AS BackupFile_Name FROM #GetFileList
WHERE SrNo = @InsertedRecords


Post #596381
Posted Tuesday, November 4, 2008 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 12, 2016 2:17 AM
Points: 49, Visits: 377

CREATE TABLE #tbl_Backups
BackupName VARCHAR(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

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'
Post #596406
Posted Tuesday, November 4, 2008 7:30 AM



Group: General Forum Members
Last Login: Thursday, March 31, 2016 8:57 AM
Points: 177, Visits: 788
Hi again,

The link I sent you in my post the day before yesterday does EXACTLY what you want to do in C#:

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.


Post #596563
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse