I'd recommend Ola's scripts for SQL Server maintenance - backups, re-indexing, checkDB, house-keeping, everything! It takes care of naming conventions too! They are very clever, and award winning too!!!! :)
Personally, if your databases are small, I'd keep to a full daily backup schedule. I believe Microsoft recommend that as well - simplicity is best approach.
Ola's scripts:
http://ola.hallengren.com/
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
Method1: The Best method to achieve this would be using Olla hallengren Backup scripts.
You can find the scripts at https://ola.hallengren.com/downloads.html
First thing you need to do is execute CommandExecute.sql script and then run the databasebackup.sql script.
Once done you can execute below via SQL Agent job as per you're required schedule:
Here Backup type can be changed from FULL, DIFF to LOG(If any DB in full recovery model exist) as per the need. And fro @ cleanup time- Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.
Refer to below link on further details:
https://ola.hallengren.com/sql-server-backup.html
Method 2: You can always refer to Maintenance plans for clean up within SQL server
Use below query as an example to delete the old backup files as per the need:.
Refer to below link for using setting up cleanup task.
http://www.biztalkadminsblogging.com/index.php/blog-archive/item/20-automatically-remove-old-backup-files