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.

Advertisements
 
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
All About Dynamics 365

Dynamics 365, D365, Implementor

syedrafayali

This blog contains information about Functional techniques and guidelines in Microsoft Dynamics AX, including tips, tricks, tutorials, tools and upcoming news enhancement in Microsoft Dynamics Ax

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Finite Minds

Adventures in IoT

Dynamics Ax

Technical Knowledge

timsaxblog

A blog about implementing Microsoft Dynamics AX and Dynamics 365 for Operations

Microsoft Dynamics 365 Blog

All Things Dynamics 365 - Enterprise Edition, A Blog by Sandeep Chaudhury

DEVSerra - Dynamics AX development blog

Your official Microsoft Dynamics AX blog.

OrganicAX

Discovering Dynamics

AX

A blog by Hai Nguyen

Learn Dynamics Ax with Johnkrish

Live as if you were to die tomorrow. Learn as if you were to live forever - Mahatma Gandhi ****** The more I learn, the less I know - Albert Einstein

Twisted Untwirled

Just another WordPress.com site

ramdynamicsax

Just another WordPress.com site

guyterry's Dynamics AX blog

Just another Dynamics AX blog

Chaitanya kumar

Happy DAXing...!!! :)

%d bloggers like this: