Daily Full Backup Script-MS SQL




1.Create Full_database_Backup Procedure.


USE [master]
GO

SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[full_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 +'full_'+ @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor


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

USe Master

Go
select getdate()
GO


exec full_database_backup;
GO


exit
 

 
3.Create Batch file named full_backup_script.bat to schedule it externally.

**@@ means full path for the file.

osql -SServername/InstanceName -Usa -Ppasswordforsa -i @@\full_backup_script.sql >>@@full_Backup.log

exit

 4.Schedule this batch file by windows scheduler.

5.Schedule on weekly basis.Particularly on Every Sunday then take incremental on week days.

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response