Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 6 '13 at 14:18

This question came from our site for professional and enthusiast programmers.

1  
There are some existing questions with answers that may help you: stackoverflow.com/questions/880487/… and an article that will show how to append the date: howtogeek.com/50299/… –  Cougar9000 Jun 6 '13 at 15:17

3 Answers 3

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 ServerName\InstanceName -i C:\YourScriptDir\Backup.sql
share|improve this answer

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.