Return
-
Title
Backing up vRanger database with automated script creates a large backup file -
Description
Running a backup using the following script every day creates a large single file:
SQLCMD -S localhost\VRANGERPRO -Q"BACKUP DATABASE vRangerPro TO DISK = 'C:\Backups\vRanger.bak'"
-
Cause
If the script doesn't use the "WITH INIT" option, the SQLCMD backup will append the new backup to the existing file. -
Resolution
The "WITH INIT" option can be used to initialize the file prior writing to it. The query should be as follows:"BACKUP DATABASE vRangerPro TO DISK = 'C:\Backups\vRanger.bak' WITH INIT"As an alternative to this, you can change the file name of the backup by declaring a variable that would get you the date of the backup. You can do this by creating a SQL file with the query and then invoking it using SQLCMD. This is a sample you can use:DECLARE@DatabaseName sysname = N'vRangerPro',@DatabaseBackupFileName varchar(255);SET @DatabaseBackupFileName ='C:\Backups\' + @DatabaseName + REPLACE(CONVERT(char(8), GETDATE(), 103), '/', '_') + '.bak';BACKUP DATABASE @DatabaseNameTO DISK = @DatabaseBackupFileName;Then you can use the invoke the SQL script by using the following command:SQLCMD -S VRANGER\VRANGERPRO -i C:\Backups\backupdatabase.sql