[Database-Tự Học SQL] Backup SQL Server - Script To Backup All SQL Server Databases - T-SQL

NỘI DUNG:

1. Thực hiện trên Microsoft SQL Server Management Studio

• Tạo đường dẫn để lưu các file backup  (1)
• Định nghĩa tên file  (2)
• Trích lọc chọn các database cần backup  (3)
• Thực hiện vòng lặp để duyệt hết list các database (3) cần backup
• Tạo script backup với các thông tin từ (1), (2), (3)
• Thực hiện backup test

2. Thực hiện trên SQL Server Configuration Manager

• Enable TCP/IP và gán port sẽ sử dụng cho SQL Server
• Restart SQL Server Service

3. Thực hiện trên Windows

• Allow inbound rules trên Windows Firewall và kiểm tra
• Tạo file script kết hợp SQL command line lưu với tên file OSQL_call_bk_SQL.bat
• Tạo file script tìm xóa chỉ giữ lại các file trong 3 ngày gần nhất  và lưu lại với tên forfile_del.bat
• Tạo file script có tên schedule_cmd.bat call và thực thi OSQL_call_bk_SQL.bat và forfile_del.bat
• Khởi tạo lập lịch back up định kỳ.

THỰC HIỆN

1. Thực hiện trên Microsoft SQL Server Management Studio

• Tạo đường dẫn để lưu các file backup
• Định nghĩa tên file 
• Trích lọc chọn các database cần backup
• Thực hiện vòng lặp để duyệt hết list các database (mục 3) cần backup
• Tạo script backup với các thông tin từ mục 1, mục 2, mục 3
• Thực hiện backup test

CODE:

Tạo file bk_QSL.sql

DECLARE @name NVARCHAR(256) -- ten database 
DECLARE @path NVARCHAR(512) -- duong dan de luu backup files  
DECLARE @fileName NVARCHAR(512) -- ten file backup  
DECLARE @fileDate NVARCHAR(40) -- lay ngay gio he thong luu vao bien nay ket hop voi fileName
 
-- (1) cai dat duong dan, ban phai dam bao folder dang ton tai
SET @path = 'C:\SQL_backups\BAKs' 
 
-- thuc hien lay ngay gio he thong luu vao bien
SELECT @fileDate = (SELECT FORMAT (getdate(), 'yyyymmdd_hhmmss'))
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT [name] -- (3) thuc hien trich loc file can backup
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- khong lay cac database he thong
AND state = 0 -- cac database online
AND is_in_standby = 0 -- database is not read only for log shipping
AND [name] not like 'ReportServer%' -- databases he thong (tuy version ma 'ReportServer...' co database nay hay khong)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   -- lap duyet qua tat ca database
BEGIN   
   SET @fileName = @path + '\' + @fileDate + '_' + @name + '.BAK'  -- (2) ten file gom: duong dan + ngay gio + ten cua database
   BACKUP DATABASE @name TO DISK = @fileName  -- thuc hien backup FULL
   -- WITH DIFFERENTIAL -- co the thuc hien backup thay doi
   
	RESTORE VERIFYONLY -- kiem tra xem back up thanh cong hay khong, co doc duoc hay khong
	FROM DISK = @fileName
	

   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Tùy nhu cầu, chúng ta có thể backup log với cấu trúc lệnh

BACKUP LOG [ten_database]
TO DISK = 'C:\SQL_backups\tenfile_canluu.trn'
GO

Hoặc tham khảo thêm các lựa chọn khác tại đây 

Noted: Ghi chú tiếng việt có dấu trong phần code T-SQL sẽ bị lỗi khi sử dụng lệnh osql nên chúng tôi ghi chú tiếng việt không dấu

CHẠY THỬ:

Processed 496 pages for database 'QLDA', file 'QLDA' on file 1.

Processed 1 pages for database 'QLDA', file 'QLDA_log' on file 1.

BACKUP DATABASE successfully processed 497 pages in 0.068 seconds (57.006 MB/sec).

Processed 432 pages for database 'QL_NhaTro', file 'QL_NhaTro' on file 1.

Processed 1 pages for database 'QL_NhaTro', file 'QL_NhaTro_log' on file 1.

BACKUP DATABASE successfully processed 433 pages in 0.061 seconds (55.351 MB/sec).

Processed 400 pages for database 'QLDA_COM2034', file 'QLDA' on file 1.

Processed 2 pages for database 'QLDA_COM2034', file 'QLDA_log' on file 1.

BACKUP DATABASE successfully processed 402 pages in 0.194 seconds (16.168 MB/sec).

Completion time: 2022-05-11T16:34:49.5008303+07:00

KẾT QUẢ:

2. Thực hiện trên SQL Server Configuration Manager

• Enable TCP/IP và gán port sẽ sử dụng cho SQL Server: Đảm bảo TCP/IP đã Enable, và thiết lập port cho SQL Server (ở đây chúng tôi dùng port 1433)

- SQL Server Configuration Manager -> Protocols for SQL...-> TCP/IP -> Enable

- SQL Server Configuration Manager -> Protocols for SQL...-> TCP/IP -> Properties -> Tab IP Addresses -> IPAll -> TCPPort điền 1433 -> OK

• Restart SQL Server Service: Khởi động lại SQL Server

Mở SQL Server Configuration Manager -> SQL Server Services -> SQL Server -> Restart

3. Thực hiện trên Windows

• Allow inbound rules trên Windows Firewall và kiểm tra

    Mở cmd run as Administrator

netsh advfirewall firewall add rule name="Allow TCP Port 1433" dir=in action=allow protocol=TCP localport=1433

Ngoài ra chúng ta cũng có thể thêm port vào Windows Firewall bằng giao diện có thể tham khảo ở link này (mục 1.3)

    Kiểm tra port đã lắng nghe trên máy chưa (Listening là đã lắng nghe)

netstat -ano | find "1433"

    Kết quả:

Có thể kiểm tra thêm bằng phần mềm tcping cho chắc ăn (đảm bảo các máy khác cũng có thể truy cập được SQL với port 1433)

tcping -h localhost 1433

    Kết quả: Nếu 1433 is Open -> ổn

Noted: có thể thay localhost bằng IP hoặc tên máy tính của các bạn


Download phần mềm TCPING tại đây

• Tạo file script kết hợp SQL command line lưu với tên file OSQL_call_bk_SQL.bat

Tạo file có tên OSQL_call_bk_SQL.bat

@echo off
set _DateTime=%date%%time%
set _DateTime=%_DateTime: =_%
set _DateTime=%_DateTime::=-%
set _DateTime=%_DateTime:/=-%
set _DateTime=%_DateTime:.=_%

set _srcScript="C:\SQL_backups\Script\bk_SQL.sql"
set _dstLogs="c:\SQL_Backups\Logs"

if NOT EXIST %_dstLogs% MD %_dstLogs%

::osql -E -S "localhost" -i "C:\SQL_backups\Script\bk_SQL.sql" 

osql -E -S "localhost" -i %_srcScript% -o "%_dstLogs%\%_DateTime%.txt"

@echo on

Noted: Tùy theo từng version mà sql command có thể là: iSQL, oSQL, SQLcmd,...... Đường dẫn đến file thường là (150 màu đỏ là tùy theo version mà chúng ta chọn cho hợp lý) C:\Program Files\Microsoft SQL Server\150\Tools\Binn

Tham khảo cách lấy ngày giờ hệ thống trên command tại link

• Tạo file script tìm xóa chỉ giữ lại các file trong 3 ngày gần nhất  và lưu lại với tên forfile_del.bat (tùy theo nhu cầu thực tế)

@echo off
set _DateTime=%date%%time%
set _DateTime=%_DateTime: =_%
set _DateTime=%_DateTime::=-%
set _DateTime=%_DateTime:/=-%
set _DateTime=%_DateTime:.=_%

set _path=C:\SQL_backups
set _dirfile=BAKs
set _logs=Logs

set _days=3
forfiles /p "%_path%\%_dirfile%" /s /m *.BAK /D -%_days% /C "cmd /c del /q @path&echo @path >>%_path%\%_logs%\%_DateTime%_DelFileList.txt"

@echo on 

Tham khảo tìm và xóa file với Forfiles tại link

• Tạo file script có tên schedule_cmd.bat call và thực thi OSQL_call_bk_SQL.bat và forfile_del.bat 

call C:\SQL_backups\Script\OSQL_call_bk_SQL.bat
call C:\SQL_backups\Script\forfile_del.bat

Noted: Các bạn đảm bảo rằng trong đĩa C:\ (đây là Lab nên chúng tôi lưu như vậy) có các folder và file như hình dưới:

• C:\SQL_backups\Logs: là nơi lưu trữ file log khi backup SQL và các file đã xóa file của lệnh forfiles
• C:\SQL_backups\BAKs: chứa các file .bak là định dạng file backup của SQL
• C:\SQL_backups\Script: chứa các file script

Các bạn có thể gọi thêm các script khác tùy theo nhu cầu, ví dụ: sau khi backup xong nén file log lại gửi đến email cho các bạn.

• Khởi tạo lập lịch back up định kỳ.

Task Schedule -> Create Basic Task... -> đặt tên -> Next -> Daily (Tùy các bạn chọn) -> Next

Cài đặt thời gian chạy file backup -> Next -> Start a program -> Next

Chọn đường dẫn đến nơi chứa file cần thực thi -> Next -> Finish

Kiểm tra lại Task Schedule vừa tạo

Kết quả thực thi

Link tham khảo

Xong!