I want to do a full weekly backup of my SQL Server 2012 Express database.

I have created a SQL script and batch file to automatically do a full backup of my database each week and overwrite the existing backup file each time this script is executed.

I am wondering if my syntax is correct and is doing what I intend it to do.

DISK = N'\\path\to\backup\folder\TestBackup.bak' 
    , INIT  
    , NAME = N'test Backup'
    , SKIP
    , STATS = 10;

Is there anything incorrect in the above backup statement?

  • NOREWIND, NOUNLOAD are superfluous in your case. since they are tape drive-specific options that will have no effect when performing the backup to disk. NOFORMAT simply indicates not to overwrite the header in the backup file - this is the default behavior, and is unnecessary when combined with the INIT option which overwrites all backups in the backup file. You may want to look at the SQL Server Books Online topic about the BACKUP command at msdn.microsoft.com/en-us/library/ms186865.aspx – Max Vernon Mar 17 '15 at 13:30

Yes that is correct.

It will backup your database to \\path\to\backup\folder\TestBackup.bak.

INIT will overwrite the file everytime it backups up.

Are you sure you just want one FULL backup ? What about transaction log backups? What if that backup gets corrupted (since you have only one backup and that is being overwritten everytime you take a new full backup) ?

Better append datetime at the end of backup.

I would suggest you to use the script at How to schedule and automate backups of SQL Server databases in SQL Server Express ?

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.