RSS

SQL – Scheduled Date wise database backup

20 Feb

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]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 01/15/2010 00:25:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

– =============================================
– 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)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/’,”) +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”)

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
END

========================================================

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.

 
Leave a comment

Posted by on February 20, 2012 in SQL

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
365 by Thijs

Blogging about Microsoft 365, Azure and Automation!

axhowto

Blog about ax with sample codes, tips and tricks.

GM Dynamics Blogs

Transform IT with Digital Trasformation and Social Innovation

Ms. Dynamic Millennial

Unboxing Microsoft Dynamics 365 for Finance and Operations

DynaD365Power

Exploring the new vision of the Power platform

Dynamics Vision 365

The FinOps pulse

Breaking Bong

I write when no one is watching

ErpCoder

Microsoft Dynamics 365 Business Management Solution Enthusiast

Dynamics 365 Finance

Dynamics 365 Finance & Much More.

Microsoft Dynamics AX

“ANYONE WHO STOPS LEARNING IS OLD, WHETHER AT TWENTY OR EIGHTY.” —HENRY FORD

Sumit Potbhare

Dynamics 365 for Commerce

iotsolution.se

This is your site about D365 (Finance and Supply Chain Management), IoT (Internet of Things) and HoloLens

Omni 365

Dynamics 365 Finance and Operations Blog

DIY D365

Power Platform Done Your Way

%d bloggers like this: