Creating a automated backup and restore with litespeed

Copy this code into a sqljob for and set time to whatever time you would like the backup taken this will work for daily backups and delete weekly.

SET QUOTED_IDENTIFIER ON

DECLARE @lastweekfiletime char(10)
, @dircmd varchar(500)
, @delcmd varchar(500)
, @filetime char(19)
, @sqlcmd varchar(500)


SELECT @lastweekfiletime = convert(char(4),dateadd(dd,-7,getdate()),112)+ '-' + substring(convert(char(8),dateadd(dd,-7,getdate()),112),5,2) + '-'
+ substring(convert(char(8),dateadd(dd,-7,getdate()),112),7,2);

PRINT @lastweekfiletime;

SELECT @dircmd = 'EXEC master..xp_cmdshell ''DIR d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp''';

EXEC(@dircmd);
-- deletes older files
SELECT @delcmd = 'EXEC master..xp_cmdshell ''DEL d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp /Q''';

print @delcmd;

EXEC(@delcmd);

EXEC(@dircmd);

SELECT @filetime = (
SELECT convert(char(4),getdate(),112) + '-'
+ substring(convert(char(8),getdate(),112),5,2) + '-'
+ substring(convert(char(8),getdate(),112),7,2) + '_' +
CASE len(datename(hh, getdate()))
WHEN 1 THEN
'0' + datename(hh, getdate())
ELSE
datename(hh, getdate())
END + '-' +
CASE len(datename(mi, getdate()))
WHEN 1 THEN
CASE datename(mi, getdate())
WHEN 0 THEN
datename(mi, getdate()) + '0'
ELSE
'0' + datename(mi, getdate())
END
ELSE
datename(mi, getdate())
END + '-' +
CASE len(datename(ss, getdate()))
WHEN 1 THEN
CASE datename(ss, getdate())
WHEN 0 THEN
datename(ss, getdate()) + '0'
ELSE
'0' + datename(ss, getdate())
END
ELSE
datename(ss, getdate())
END);


SET @sqlcmd = 'exec master..xp_backup_database @database = ''database'', @filename = ''d:\dir\database_' + @filetime + '_1of1.lsb.dmp''';

EXEC(@sqlcmd);

Restore if you would like to restore daily to another database on the same server you can us this job to pick up the latest full database.
Or create a copy job to copy the backup accross to another server and use this to restore daily.
SET NOCOUNT ON
declare @RestoreDateTime varchar(20)
declare @stmt varchar(500)
declare @restore_stmt varchar(1000)
declare @latest_full varchar(255)

create table #SrcFilesRAW
(
SrcFName varchar(200)
)

select @stmt = 'dir D:\dir\*database_*.dmp /b'

insert into #SrcFilesRAW
exec master..xp_cmdshell @stmt

select * from #SrcFilesRAW
set rowcount 1
select @latest_full = SrcFName from #SrcFilesRAW
where SrcFName like 'database_20%' order by SrcFName desc
set rowcount 0
select * from #SrcFilesRAW
drop table #SrcFilesRAW



select @restore_stmt = 'exec master..xp_restore_database @database = ''database1''
,@FILENAME = ''D:\dir\'+ @latest_full + '''
,@WITH = ''REPLACE'''

exec (@restore_stmt)