Daily Backup strategy

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