Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Sunday, November 2, 2008 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:06 PM
Points: 33,169, Visits: 15,304
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
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.

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
Posted Tuesday, November 4, 2008 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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
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: Yesterday @ 6:57 AM
Points: 37, Visits: 256
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
Posted Tuesday, November 4, 2008 7:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:13 AM
Points: 175, Visits: 746
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 »

Add to briefcase ««12

Permissions Expand / Collapse