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
- 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. - 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.