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.b ak'
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

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


"David Hills" <DavidHills (AT) discussions (DOT) microsoft.com> wrote