This procedure should do what you want:
create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer
select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc
declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;
open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go
execution goes:
get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device
Then build up a union query consisting of:
The last full database backup (type=D) in the device
the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.
the transaction logs (type=L) taken after the above diff.
returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)
Loop through the result restoring them individually, and then a final restore with recovery to finish it off.
Call it as:
exec dbrestore 'db2restore'
I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.
Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.
This is old, but I had the same issue. My SQL logs and backup tables were indicating that backups were happening, but even when I used the vssadmin command above, I could not locate the backup files.
I listed the writers, but none matched the long string in the SQL logs, so I tried some of the other "vssadmin list" commands:
---- Commands Supported ----
List Providers - List registered volume shadow copy providers
List Shadows - List existing volume shadow copies
List ShadowStorage - List volume shadow copy storage associations
List Volumes - List volumes eligible for shadow copies
List Writers - List subscribed volume shadow copy writers
"List Providers" turned out to be the golden nugget. Although nothing was returned for existing copies or storage associations, the Providers listed the info I needed - it named my SAN provider. I looked in my storage tool and saw it was using Microsoft VSS, and the snapshot times correlated with what was in my SQL log.
Three things of note:
nowhere could I find anything to correlate to the TYPE=VIRTUAL_DEVICE: number listed in my SQL Logs (even though there were plenty of guids all over)
"List Shadows" did not return anything, which was scary until I looked at my storage tool and saw the snapshots there.
My DBs are all currently in "SIMPLE" recovery model, so I never have to do anything but attach database mdf/ldf files from the snap. This would be more complicated with databases that need to live in full recovery mode.
HTH future searchers - this has been bugging me forever. Where were my backups?
Best Answer
Imagine that you may have lot of backup-jobs. Full backups, differential backups, transaction log backups... Now imagine that you need to move all your backups from local disk to SAN. if you use backup devices, you just need to re-create the devices with paths to new disk. But if your backup scripts are written to use full paths, you need to rewrite every job, every job-step to change these paths.
But there can be scenario when you need no to use the backup devices, for example, when you use some custom scenario where you dynamically changes the backup path or file name (for example, adding date/time to folder or file name).