Backup not creating a new file

Posted on

Question :

I run a daily backup of my database through command prompt:

c:sqlcmd -H localhost -Q "BACKUP DATABASE test TO DISK='c:test.bak'"

But the new backup replaces the previous backup. Please tell me what to change so that all backups will be stored on disk.

Answer :

Instead of writing your own backup solution, I would suggest you to look at SQL Server Maintenance Solution by Ola Hallengren.

If you want to use sqlcmd then you can do as below:

Save below file as backup.sql

DECLARE 
    @DatabaseName sysname = N'test_kin'
    ,@DatabaseBackupFileName varchar(255);

SET @DatabaseBackupFileName = 
    'C:test' + @DatabaseName 
      + REPLACE(CONVERT(char(8), GETDATE(), 103), '/', '_') + '.bak';

BACKUP DATABASE @DatabaseName
TO DISK = @DatabaseBackupFileName with init, stats =10;

Then using sqlcmd you can do as below :

sqlcmd -S ServerNameInstanceName -i C:YourScriptDirBackup.sql

At least a couple of choices

  1. Whenever you create a backup, arrange that the backup file test.bak has a unique name. There are lots of ways to do this, I usually embed the date into the filename (in the format yyyy-mm-dd, e.g. 2013-06-05 for yesterday) to ensure that alphabetical order of filename is the same as chronological order.
  2. Use SQL agent to schedule the backup – it will take care of the names for you

If you are using an edition of SQL Server other than Express then you can use a Maintenance Plan to generate a new backup file every time a backup is made, as well as to remove old backup files if wished. Also see Maintenance Plan Wizard.

Leave a Reply

Your email address will not be published. Required fields are marked *