SQL – Scheduled Date wise database backup

Newer posts →SQL – Scheduled Date wise database backup
Posted on January 15, 2010 by shekhardiptiman
The scenario here is that there should be backup folders for each day of the week i.e. Monday,Tuesday,…..SUNDAY ( 7 in total). The SQL backup at disc should be performed daily and it should get copied in the respective day’s folder. The folders should retain the backup for one week. The backup performed on each day should overwrite the last week’s backup for that day. This way there will be at the most 7 backups available on the disc.

Following are the steps to achieve the above :

1. Create a Stored Procedure in SQL server with the following code.

The code is given below for reference


USE [master]
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 01/15/2010 00:25:19 ******/

– =============================================
– Author:
– Create date:
– Description: Backup Database
– Parameter1: databaseName
– Parameter2: backupType F=full, D=differential, L=log
– =============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)

DECLARE @sqlCommand NVARCHAR(1000)


IF @backupType = ‘F’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”F:\SQLBackup\’ + @databaseName + ‘_Full_’ + @dateTime + ‘.BAK”’
IF @backupType = ‘D’
SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +
‘ TO DISK = ”F:\SQlBackup\’ + @databaseName + ‘_Diff_’ + @dateTime + ‘.BAK” WITH DIFFERENTIAL’
IF @backupType = ‘L’
SET @sqlCommand = ‘BACKUP LOG ‘ + @databaseName +
‘ TO DISK = ”F:\SQlBackup\’ + @databaseName + ‘_Log_’ + @dateTime + ‘.TRN”’
EXECUTE sp_executesql @sqlCommand


2. Create a file to provide the parameter values regarding the name of the database and type of the backup to be performed to the above procedure as shown below: Save it as .sql file e.g. backup.sql

3. Create a scheduled task in windows to process the above in the following screenshot ######-SVR is the server name on which SQL is installed.

■Configure Run – ‘sqlcmd -S XXXXX-SVR -E -i F:\DYNAMICSAX\Backup.sql’ . ( highlighted is the path to the backup.sql file)
■Configure the “Start in” as shown .
■Configure “Run as” as a valid user in SQL database who has admin rights for the server and the database to be backed up. Save teh user name and password for that user id.
■Set the schedule for the above task to ‘Daily’

The above would create an SQL backup daily at 8.05 pm and save at “F:\Sqlbackup” folder as setup in the stored procedure.

4. Inside F:\SQLBACKUP folder there would 7 subfolders ( one for each weekday excluded). Now the rest of the job is very simple . Create a batch file which will delete the files from the weekday folders and move the backup file from the root of the F:\SQLBackup to the subfolder of the day.

We created 7 batch files (one for each day.). In the following screenshot there are 5 batch files show. ( excluded saturday and sunday) . For example if A.bat is processed then it will delete the files in the folder named “Monday” and move the file from the root of the folder F:\SQLBACKUP as explained above into “Monday”

5. Create windows scheduled tasks which would access these batch files once in a week i.e A.bat would be processed on Monday, B.bat on Tuesday …

Configure the schedule for each task as explained and shown in the screenshot.

Important : The schedule should set such that it processes the batch files after the SQL backup has been finished and the backup file is saved .

Now the SQL backup would be performed daily with a date stamp in its name and saved in a folder for each weekday.

**************You have achieved your objective*****************

The above is an illustration for SQL 2005. I hope it would work for SQL 2008 also.