Daily Incremental Backup Script-MS SQL





1.Create Incr_database_Backup Procedure.

USE [master]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[Incr_database_backup]
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = '\\255.255.253.0\Full_Backup\ServerName_Instance_Name\'  --SET UNC PATH


SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),105)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
       SET @fileName = @path +'Incr_'+ @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName  WITH DIFFERENTIAL

       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor



2.Create Incremental_Backup.sql File that contains execution of procedure


USe Master

Go
select getdate()
GO


exec Incr_database_backup;
GO


exit
 

 
3.Create Batch file named Incr_Backup_Script.bat to schedule it externally.
**@@ means full path for the file.

osql -SServername/InstanceName -Usa -Ppasswordforsa -i @@incremental_Backup.sql >>@@Incremental_Backup.log
exit

 4.Schedule this batch file by windows scheduler.


5.Schedule on daily basis.Particularly on Every  week days.

2 comments:

  1. can we have script for incremental backup in sql azure database

    ReplyDelete
  2. Hi please let me know is there any script to take incremental backup in sql azure database

    ReplyDelete

Dear User,

Thank you very much for your kind response