Blog của Lê Văn Luật

Đời người thì có hạn mà sự học thì vô hạn!

Backup dữ liệu từ SQL Server Express ra thư mục chia sẻ

SQL Server Express không có cơ chế backup tự động (không có Server Agent) như SQL Server Pro do đó cần phải thực hiện thông qua cơ chế Task Scheduler của Windows. Ngoài ra, khi trong server ảo (guest) trong Hyper-V, việc truy cập đĩa trên Host khá khó khăn (với Hyper-V 3.0 có thể truy cập tuy nhiên cũng không dễ dàng).

Các thao tác sau nhằm backup tự động CSDL SQL Server Express ra một thư mục chia sẻ trên mạng.

1. Tạo Stored Procedure trong SQL Server


USE [master]
 GO
 /****** Object: StoredProcedure [dbo].[sp_BackupDatabases] Script Date: 05/01/2015 9:39:47 SA ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

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

ALTER 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] format 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. Tạo file .BAT (giả sử đặt tên là DataBackup.BAT)

Đoạn chương trình BAT sau thực hiện:

+ Full Backup CSDL có tên MYDATA vào thư mục C:\Backups (dòng 1)
+ Xóa các file backup đã quá 1 ngày, chỉ giữ lại các file backup ngày hôm trước (tức là ta để lại 02 file backup ngày hôm qua và ngày hôm nay). Có thể xóa tất cả các file backup từ hôm qua bằng cách thay “@file : date >= 2 days” thành “@file : date >= 1 days” (dòng 2).
+ Xóa tất cả các file có trong thư mục \\SERVER\DataBackups (dòng 3)
+ Sao chép tất cả các file backup trong thư mục C:\Backups trong ngày sang thư mục chia sẻ \\SERVER\DataBackups (dòng 4)


sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups\', @databaseName='MYDATA', @backupType='F'"
forfiles /p "C:\Backups" /s /d -2 /c "cmd /c del @file : date >= 2 days >NUL"
del "\\SERVER\DataBackups\" /Q > "C:\Backups\emisbk_del.log"
robocopy "C:\Backups" "\\SERVER\DataBackups" /maxage:1 > "C:\Backups\bk_copy.log"

3. Tạo tác vụ tự động trong Windows

Mở Task Scheduler và tạo Task mới. Thiết lập các thông số như sau:

taskscheduler01 taskscheduler02 taskscheduler03

 

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

Information

This entry was posted on 05/01/2015 by in Cơ sở dữ liệu.

Điều hướng

%d bloggers like this: