RSS

Daily Backup strategy

08 Sep

introduction

This Article Explain how to make job to back up all database Day by day  on your PC, and put backup of Saturday in folder , Sunday in folder and so on , Then take backup from this backup file to anther place in anther PC.

in the first i use stored procedure to select all database like what i like then make job to EXEC this Stored then make job in windows to copy this backup from PC 1 to PC 2 lets see how can make this .

Step No 1

— Create Stored procedure {TakeDBsBackupDaily}

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[TakeDBsBackupDaily]
@BackupPath nvarchar(500)
AS
BEGIN
declare  @d nvarchar(2)
,@m nvarchar(2)
,@y nvarchar(4)
,@CurDate nvarchar(8)
,@h nvarchar(2)
,@min nvarchar(2)
,@sec nvarchar(2)
,@CurTime nvarchar(8)
,@FName nvarchar(500)
,@DbName nvarchar(100)
,@BackupTypeName nvarchar(200)

Set @d = DATEPART(“day”,(GETDATE()))
Set @m = DATEPART(“month”,(GETDATE()))
Set @y = DATEPART(“year”,(GETDATE()))
set @CurDate = replicate(‘0’,2-len(@d))+@d —-+ replicate(‘0’,2-len(@m))+@m + replicate(‘0’,4-len(@y))+@y —– IN this statment i Sets the time will appear Beside database name like ‘Account22072011.bak’ but now i make the database of overwrite every month so i want the database show like this ‘Account07’
Set @h = DATEPART(“hour”,(GETDATE()))
Set @min = DATEPART(“minute”,(GETDATE()))
Set @sec = DATEPART(“second”,(GETDATE()))
set @CurTime = replicate(‘0’,2-len(@h))+@h + replicate(‘0’,2-len(@min))+@min + replicate(‘0’,2-len(@sec))+@sec

DECLARE RsTr CURSOR FOR
SELECT     name
FROM         sys.databases
WHERE     database_id >4 and state_desc = ‘ONLINE’

OPEN RsTr
FETCH NEXT FROM RsTr
INTO @DbName

WHILE @@FETCH_STATUS =0
BEGIN
—             +’_T_’+@CurTime
SET @FName = @BackupPath + ‘\’ + @DbName  + ‘_D_’+@curdate+’.bak’
print ‘Backing up Database : ‘ + @DbName + ‘ …’
set @BackupTypeName =  @DbName + ‘-Full Database Backup’
PRINT @FName
BACKUP DATABASE @DbName TO  DISK = @FName   WITH NOFORMAT, INIT,  NAME = @BackupTypeName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

FETCH NEXT FROM RsTr
INTO @DbName
END

CLOSE RsTr
DEALLOCATE RsTr

END

———————————————————————————————-

STEP NO 2

In your pc make this folders

1- Folder Name (Auto_Backup) then in Auto_Backup make 7 folder (St-Su-Mo-Tu-We-Th-Fr)

STEP NO 3

—- Create Job (Backup Day By Day)To EXEC the SP (TakeDBsBackupDaily)

1- Open Sql Server Mangment Staduo

2- Open SQL Server Agent

3- Right Click on jobs , Select new job

4- Job Name [Backup Day By Day]

5- In Steps Select New , Step Name [Daily_Backup]

6- Put this Code in this step

Remarks : [Replace this path (‘D:\Backup\Auto\DayByDay\tu’) With your path]

USE [master]
GO
set dateformat dmy
declare @dayINDEX  as int
select @dayINDEX =(DATEPART(dw, getdate()) + @@DATEFIRST) % 7  — By this code i will get the index to the Current Day bigen from (0 to 6) [ST=0,Su=1,Mo=2,Tu=3,We=4,Th=5,Fr=6]

if @dayINDEX = 0
begin
exec   TakeDBsBackupDaily ‘D:\Backup\Auto\DayByDay\St’
end
if @dayINDEX = 1
begin
exec TakeDBsBackupDaily ‘D:\Backup\Auto\DayByDay\Su’
end
if @dayINDEX = 2
begin
exec  [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\mo’
end
if @dayINDEX = 3
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\tu’
end
if @dayINDEX = 4
begin
exec  [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\we’
end
if @dayINDEX = 5
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\th’
end
if @dayINDEX = 6
begin
exec [TakeDBsBackupDaily] ‘D:\Backup\Auto\DayByDay\fr’
END

——————————————————————————————————–

STEP NO 4

—Copy The backup file from the server to pc 2

In the PC2

1- Create new folder in make this folder is share to every one and give it all permision

2- the user in your pc must be administrator and must be has a paasword

In the Server

1-In folder Auto Backup make new text Document ,The name of this text [MoveDB_Backup] but make this text not .TXT make it .bat (icone must be cahnge when you make this)

2- then make Edit in this File bat and write

3- xcopy /y /s F:\backupdaybyday\*.*  \\192.168.0.9\daybydayback

4- Remarks [Replace this path (F:\backupdaybyday) by your backup folder path like E:\Auto backup not like E:\Auto Backup\St if you make this you will copy the backup in this folder only then Replace this path (\\192.168.0.9\daybydayback) by the Folder Share in PC 2] this path [\\192.168.0.9\daybydayback] must be write like this not like this ‘E:\daybydayback’ to make the server access the pc2

5- so you can open the RUN and write the IP of PC 2 and try to open the share folder then thake tha path to the folder

6- in your windows in the server make job to EXCE this file .bat Every day

 ——————————————————————————————————-

At the end of my words I hope I estimated I tell you

My best Regards

Mostafa Elmasry

 
2 Comments

Posted by on September 8, 2011 in SQl server Administration

 

2 responses to “Daily Backup strategy

  1. Motafa Elmasry

    September 10, 2011 at 10:13 PM

    Task Scheduler in windows
    1- Start >>>> All programme>>>>>Accessories>>>>>> Systrem Tools >>>>>Task Scheduler

     
  2. Motafa Elmasry

    September 10, 2011 at 10:18 PM

    —By this Code you can select the index of the day
    set dateformat dmy
    declare @dayINDEX as int
    select @dayINDEX =(DATEPART(dw, getdate()) + @@DATEFIRST) % 7
    Select @dayINDEX AS Day_index

     

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