SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loading database with the latest .bak file


Loading database with the latest .bak file

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62833 Visits: 19111
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
My Blog: www.voiceofthedba.com
sybase08
sybase08
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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!
vmenon
vmenon
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Zksod
Zksod
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 377
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
Elisabeth Rédei
Elisabeth Rédei
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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#: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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search