Loading database with the latest .bak file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Loading database with the latest .bak file

15 posts, Page 2 of 2

««1 2

Loading database with the latest .bak file

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Sunday, November 02, 2008 9:43 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

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/

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #595583

sybase08

Posted Sunday, November 02, 2008 12:56 PM

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

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!

Post #595601

vmenon

Posted Tuesday, November 04, 2008 1:59 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 06, 2010 11:19 PM
Points: 22, Visits: 97

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

Post #596381

Zksod

Posted Tuesday, November 04, 2008 3:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 19, 2012 2:53 AM
Points: 34, Visits: 171

DECLARE @vcCmd VARCHAR(666)

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

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

Post #596406

Elisabeth Rédei

Posted Tuesday, November 04, 2008 7:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175, Visits: 676

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

Post #596563

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions