sql server backup script

Question

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

nathan_jr · Accepted Answer · 2013-05-09 22:01:05Z

18

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.

edited May 9, 2013 at 22:01

answered May 21, 2012 at 19:47

nathan_jr

9,3023 gold badges43 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RThomas Over a year ago

This is a highly regarded link and considered an industry standard by many... plus one.

cableload Over a year ago

Thanks for pointing to this site..this has lot of information and i think i should be able to customize databasebackup.sql file according to my requirements...

Collectives™ on Stack Overflow

sql server backup script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related