Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql server backup script

I have made an attempt at writing a backup script for one of my very small sql server express 2008 database. My requirements are to do a full backup every night, keep the last five backups. This is my attempt at writing one and would like to get feedback on whether i am doing it right? Thanks for your assistance.

    declare @backupfilename nvarchar(100)
    set @backupfilename='c:\...location..\filename_'+convert(varchar(10),getdate(),112) + '.bak'

    BACKUP DATABASE [dbname] TO  DISK = @backupfilename 
    WITH  RETAINDAYS = 5, NOFORMAT, NOINIT,  NAME = N'Full Database Backup Name', NOSKIP,
    NOREWIND, NOUNLOAD,  STATS = 10
    GO
    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'dbname'
    and backup_set_id=(select max (backup_set_id) from msdb..backupset where 
    database_name=N'dbname' )
    if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database  
    ''dbname'' not found.', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK =@backupfilename  WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
    GO
like image 950
cableload Avatar asked May 21 '12 15:05

cableload


1 Answers

I highly recommend Ola's backup stored procedures. They are well supported and extremely solid. If you don't feel comfortable using them you can at least look to them while you write your own routine.

like image 77
nathan_jr Avatar answered Oct 09 '22 10:10

nathan_jr