How can I create a database backup replacing the previously created backup file?

Question

If I backup my SQL Server 2008 R2 database with the following command

set @BackupFileName = N'C:\backup\Production-db.BAK' BACKUP DATABASE
[Production-db] TO  DISK = @BackupFileName

and run the command twice, the backup file grows to twice the current database size. With every new run, additional data is appended.

How can I create a full backup without appending, but by replacing the previous backup file?

Thanks,

Adrian

score 4 · Accepted Answer · answered Aug 01 '11 at 12:43

4

You should specify the FORMAT clause to overwrite your media set. See the following MSDN article for more details: http://msdn.microsoft.com/en-us/library/ms191304.aspx

answered Aug 01 '11 at 12:43

Derek Kromm

INIT would work in this situation as well. – Jason Cumberland Aug 05 '11 at 20:13

1 Answers1