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
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
add comment

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

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

4 Answers

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
add comment

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
add comment

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
add comment

If you install SQLBackupAndFTP, you can just run "SQLBackupAndFTP.exe" -rn "C:\new.jobx" - by default it will create backups in the [DatabaseName]YYYYMMDDhhmm.zip format. What to backup, where to send the backups (network, FTP, Amazon, etc) - all of it is defined in jobx file created earlier in GUI. See more about SQLBackupAndFTP command line options

share|improve this answer
    
why would one go for 3rd party utilities, if the same functionality is available natively. The OP wants to just create a backup that is not replaced by previous job runs. Also the tool sqlbackupandftp.com/features that you suggested is NOT free. If you are affiliated to such site or tool, please do clarify that as well. –  Kin Jun 11 '13 at 21:07
add comment

Your Answer

 
discard

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