SQL Agent Backup Database Menggunakan Store Procedure

Jika kita menggunakan SQL server versi Enterprise mungkin tidak akan kesulitan untuk backup database, karena telah disediakan maintenance plan wizard yang memudahkan beberapa administration task seperti berikut ini :

  • check database integrity
  • perform index maintenance
  • update database statistics
  • perform database backup

Namun dalam kenyataan dilapangan, seringkali kita menghadapi environment SQL yang mungkin saja lisensinya masih Standart. Banyak cara agar kita bisa melakukan backup seperti menggunakan store procedure atau menggunakan third party yang banyak dijual di pasaran maupun yang gratisan juga banyak :p.

Berikut ini contoh backup database menggunakan store procedure yang dijalankan oleh SQL server agent.

1. copas contoh code create SP berikut ini

-- Copyright © Microsoft Corporation. All Rights Reserved.
-- This code released under the terms of the
-- Microsoft Public License (MS-PL,http://opensource.org/licenses/ms-pl.html.)
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— =============================================
— Author: Microsoft
— Create date: 2010-02-06
— Description: Backup Databases
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS

SET NOCOUNT ON;

DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)

— Pick out only databases which are online in case ALL databases are chosen to be backed up
— If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name

— Filter out databases which do not need to backed up
IF @backupType=’F’
BEGIN
DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’AdventureWorks’)
END
ELSE IF @backupType=’D’
BEGIN
DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
END
ELSE IF @backupType=’L’
BEGIN
DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
END
ELSE
BEGIN
RETURN
END

— Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int

— Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs

WHILE @Loop IS NOT NULL
BEGIN

— Database Names have to be in [dbname] formate since some have – or _ in their name
SET @DBNAME = ‘[‘+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+’]’

— Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),’/’,”) + ‘_’ + REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”)

— Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = ‘F’
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_FULL_’+ @dateTime+ ‘.BAK’
ELSE IF @backupType = ‘D’
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_DIFF_’+ @dateTime+ ‘.BAK’
ELSE IF @backupType = ‘L’
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_LOG_’+ @dateTime+ ‘.TRN’

— Provide the backup a name for storing in the media
IF @backupType = ‘F’
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ full backup for ‘+ @dateTime
IF @backupType = ‘D’
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ differential backup for ‘+ @dateTime
IF @backupType = ‘L’
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ log backup for ‘+ @dateTime

— Generate the dynamic SQL command to be executed

IF @backupType = ‘F’
BEGIN
SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+ ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
END
IF @backupType = ‘D’
BEGIN
SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+ ‘ TO DISK = ”’+@BackupFile+ ”’ WITH DIFFERENTIAL, INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
END
IF @backupType = ‘L’
BEGIN
SET @sqlCommand = ‘BACKUP LOG ‘ +@DBNAME+ ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
END

— Execute the generated SQL command
EXEC(@sqlCommand)

— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

2. Buka MSMS (Microsoft SQL Server Management Studio) dan klik New Query

3. pastekan code diataske query dan klik execute

4. Cek Store Procedure di Database->System Database->Master->Programmability->Stored Procedures->dbo.sp_BackupDatabases

sp_bd

 

5. Buat SQL Server Agent Job, klik kanan Job-> New Job

6. Isi Nama Job tersebut dan Pada bagian Steps isikan command untuk menjalankan Store Procedure

sp_bd2

 

7. Pada bagian Schedules, bisa kita setting untuk dijalankan harian, mingguan atau bulanan

8. Untuk mengetesnya klik kanan nama Job tersebut, kemudian klik Start Job at Step..

9. Lihat di direktory C:\Backup, dan pastikan ada file .bak yang berhasil di generate

10. Done

Iklan

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s