You will need to set up a separate backup device for the FULL and the DIFF. When you create the Backup Device it is a single file. You can't save a FULL and a DIFF in the same Backup Device.
More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx
If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore. More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx
REPLACE should be used rarely and only after careful consideration.
Restore normally prevents accidentally overwriting a database with a
different database. If the database specified in a RESTORE statement
already exists on the current server and the specified database family
GUID differs from the database family GUID recorded in the backup set,
the database is not restored. This is an important safeguard.
The REPLACE option overrides several important safety checks that
restore normally performs. The overridden checks are as follows:
Restoring over an existing database with a backup taken of another
database.
With the REPLACE option, restore allows you to overwrite an existing
database with whatever database is in the backup set, even if the
specified database name differs from the database name recorded in the
backup set. This can result in accidentally overwriting a database by
a different database.
Restoring over a database using the full or bulk-logged recovery model
where a tail-log backup has not been taken and the STOPAT option is
not used.
With the REPLACE option, you can lose committed work, because the log
written most recently has not been backed up.
Overwriting existing files.
For example, a mistake could allow overwriting files of the wrong
type, such as .xls files, or that are being used by another database
that is not online. Arbitrary data loss is possible if existing files
are overwritten, although the restored database is complete.
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.
Best Answer
To use a backup device the proper T-SQL would be:
Check the Syntax section of the Backup doc to better understand the backup options.
It seems you're trying to develop a backup solution from scratch, so I recommend you take a look at Ola Hallengren's solution as it might save you a lot of work.