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

Answer 1

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