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.

Comments

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.