automatic bak file name problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

automatic bak file name problem

Author

Topic

davidhills
Starting Member

14 Posts

Posted - 2009-01-20 : 08:05:36

Good Morning

I have a sql server maintance plan that creates a full backup every day and
calls the bak file

Live_YYYYMMDDHHMM.bak

where yyyy =2009
mm=01
dd=19
hh=03
mm=05

for example

The problem I face is that I want to automatically restore the backup into
another sql server and I dont know how to find the backup file name that I
want.(it always the latest one available-- this might help!)

My restore sql looks like this

USE master
GO

SELECT GETDATE()
GO

ALTER DATABASE [ROV_Live]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [ROV_Live]
**************problem here how do i fill in the correct yyyymmddhhmm value
FROM DISK = '\\nas\atlantis$\Rov_Live_Kent\Live_yyyymmddhhmm.bak'
WITH FILE = 1,
MOVE 'ROV_Live' TO 'E:\Data\ROV_Live.mdf',
MOVE 'ROV_Live_log' TO 'E:\Data\ROV_Live_log.LDF',
NOUNLOAD,
REPLACE, STATS = 10
GO

ALTER DATABASE [ROV_Live]
SET MULTI_USER
GO

SELECT GETDATE()
GO

--
Thanks

David Hills
Was this post

davidhills
Starting Member

14 Posts

Posted - 2009-01-21 : 06:12:53

David,

Perhaps you can do something like this:

DECLARE @FileName NVARCHAR(255)
CREATE TABLE #TempDirectory (BackupFile NVARCHAR(255))

INSERT INTO #TempDirectory
EXECUTE master..xp_cmdshell 'dir \\nas\atlantis$\Rov_Live_Kent\Live_*.bak
/B'

SELECT TOP 1 @FileName = '\\nas\atlantis$\Rov_Live_Kent\' + BackupFile
FROM #TempDirectory
WHERE filenm LIKE '%.bak'
ORDER BY RIGHT(BackupFile,16) DESC

DROP TABLE #TempDirectory

RESTORE DATABASE [ROV_Live]
FROM DISK = @FileName ...

RLF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources