May 29, 2007 at 12:09 pm
I have a question I was hoping you can answer. I’m using the following T-SQL command to restore a database:
RESTORE DATABASE MailServer FROM DISK = 'D:\MailServer Backup\MailServer.bak'
My problem is I want to restore the most recent File. However, if I don’t specify the “File=12” parameter, it always defaults to File=1, which has the oldest data. Is there a way to specify File=something, so that it always pulls the lastest backup, without me having to know what the last File number is? For instance, can I use a wildcard character like File=@, or something like that?
THX
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
May 29, 2007 at 1:32 pm
You might be able to retrieve the latest file number using RESTORE FILELISTONLY command. Put that into a variable and use it in the restore command. I've never tried it, but you could try it.
-SQLBill
May 30, 2007 at 2:33 am
I use this type of script:
-- Find the latest backup
DECLARE @lastbackupID int
, @backupname nvarchar(260)
, @dbname sysname
SET @dbname = '<dbname>'
SET @lastbackupID = (SELECT MAX(media_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @dbname and type = 'D')
-- Match it with a physical filename
SET @backupname = (SELECT physical_device_name
FROM msdb.dbo.backupmediafamily
WHERE media_set_id = @lastbackupID)
-- Start restoring
USE master
RESTORE DATABASE @dbname
FROM DISK = @backupname
WITH REPLACE
May 30, 2007 at 12:01 pm
Thanks Willem, looks good.
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
May 30, 2007 at 12:31 pm
Another option is to use the WITH INIT clause as part of your BACKUP command. That way, you don't have to deal with media sets at all.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply