今天研究阿里云服务数据库的迁移，备份和还原的时候，在阿里云web后台发现了一个很好用的sql脚本,就默默地偷了过来，它可以支持全量备份，差异备份和日志备份，代码解释也都很清楚，我也尝试着跑了一下，性能也还是蛮不错的，本地电脑运行压缩后1个多G的数据备份用了二十多秒
我们可以将下面代码放到SQL作业里面，然后定期的去做全量和差异的备份，这不免也算是一种不错的数据库备份方案了，当然这种备份功能完全可以用SqlServer自带的数据库维护计划功能代替，而且阿里云等类似的云数据库都是会提供数据备份服务的，不需要开发人员自己做数据备份。
代码如下，如果有用阿里云服务器的码友可以直接到用户后台下载最新源码
USE master
GO /**
* author:
* jianming.wjm@alibaba-inc.com of Alibaba Cloud Team.
*
* @purpose:
* Backup your databases to local directory (no matter destination folder exist or not).
* You can personal your backup type, support three backup types include(FULL, DIFF, LOG).
* This script also support just check to see details or run directly.
*
* if you find something happen unhappy, please kindly tell us via the author email. thanks.
*
* @usage:
* Copy all the script (ctrl + a) into SSMS, fill out the public variables and then F5 or Execute it.
*
* @parameters:
* @backup_databases_list: databases list needed to backup
* @backup_type: which backup type needed, choice one of ['FULL', 'DIFF', 'LOG']
* @backup_folder: where backup files located
* @is_run: I want to run or just check, 1: run; 0: just check.
*
* @version:
* 1.0.0, 2018/03/02 15:20: Create script.
*
* @Tips:
* We'd like to advice you to change your databases to FULL recovery model to improve migrate to RDS.
* example: ALTER DATABASE [db_simple] SET RECOVERY FULL WITH NO_WAIT
* pay attention to the SSMS output messages, and correct the error if have any.
**/ SET NOCOUNT ON -- declare public variables
DECLARE
@backup_databases_list NVARCHAR(max),
@backup_type SYSNAME,
@backup_folder SYSNAME,
@is_run TINYINT
; -- YOU HAVE TO INIT PUBLIC VARIABLES HERE!!!!
SELECT
/**
* Databases list needed to backup, delimiter is ; or ,
* empty('') or null: means all databases excluding system database
* example: '[testdb];TestDR,Test,readonly'
**/
@backup_databases_list = N'[TestA];[TestB],readonly,Test',
@backup_type = N'FULL', -- Backup Type? FULL: FULL backup; DIFF: Differential backup; LOG: Log backup
@backup_folder = N'C:\BACKUP', -- Backup folder to store backup files.
@is_run = 1 -- Check or run? 1, run directly; 0, just check
; -- variables checking
IF @backup_type NOT IN(N'FULL', N'DIFF', N'LOG')
BEGIN
RAISERROR('Backup type %s is not supported!', 16, 1, @backup_type)
RETURN
END
; IF ISNULL(@backup_folder, N'') = N''
BEGIN
RAISERROR('backup_folder is not allow empty!', 16, 1)
RETURN
END IF @is_run NOT IN(0, 1)
BEGIN
RAISERROR('is_run parameter should be 0 or 1 !', 16, 1)
RETURN
END -- declare private variables
DECLARE
@database_name SYSNAME,
@database_state TINYINT,
@database_state_desc NVARCHAR(60),
@database_count INT,
@database_do INT,
@flag_edition BIT,
@exec_sql NVARCHAR(MAX),
@timestamp VARCHAR(20),
@context_info VARCHAR(20),
@backup_databases_list_XML XML
; -- private variables init.
SELECT
@database_name = N'',
@database_state = 11,
@database_state_desc = N'',
@database_count = 0,
@database_do = 1,
@flag_edition = CASE
WHEN CONVERT(sysname, SERVERPROPERTY(N'Edition')) LIKE N'Enterprise%' THEN 1
ELSE 0
END,
@exec_sql = N'',
@timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(),120), N'-', N''), N':', N''), CHAR(32), N''),
@backup_folder = CASE
WHEN RIGHT(@backup_folder, 1) = N'\' THEN @backup_folder
ELSE @backup_folder + N'\'
END,
@context_info = N'0x1256698449',
@backup_databases_list_XML =
N'<V><![CDATA[' +
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
@backup_databases_list, '[', ''
), ']', ''
), ';', ','
),CHAR(10),']]></V><V><![CDATA['
),',',']]></V><V><![CDATA['
),CHAR(13),']]></V><V><![CDATA['
)
+ ']]></V>'
; -- temp table declare to save databases splitted
IF OBJECT_ID('tempdb..#tmp_databases', 'U') IS NOT NULL
DROP TABLE #tmp_databases
CREATE TABLE #tmp_databases(
RowId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Database_name SYSNAME
)
; -- get databases name
IF ISNULL(@backup_databases_list, N'') > N''
BEGIN
INSERT INTO #tmp_databases
SELECT DISTINCT
[Database_name] = T.C.value('(./text())[1]','sysname')
FROM @backup_databases_list_XML.nodes('./V') AS T(C)
WHERE T.C.value('(./text())[1]','sysname') IS NOT NULL
;
END
ELSE
BEGIN
INSERT INTO #tmp_databases
SELECT DISTINCT name
FROM sys.databases WITH (NOLOCK)
WHERE name NOT IN (N'master', N'tempdb', N'model', N'msdb', N'distribution')
END -- decalre cursor to loop each database.
DECLARE CUR_DatabaseList CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT name, state, state_desc
FROM sys.databases WITH (NOLOCK)
WHERE name IN(SELECT [Database_name] FROM #tmp_databases) -- Open cursor and get the rowcount
OPEN CUR_DatabaseList
FETCH NEXT FROM CUR_DatabaseList INTO @database_name, @database_state, @database_state_desc
SET @database_count = @@CURSOR_ROWS; -- prepare folder on local host.
IF @database_count > 0
BEGIN
EXEC sys.xp_create_subdir @backup_folder
END WHILE @@FETCH_STATUS = 0
BEGIN
-- contact the backup script for each backup type.
SET
@exec_sql =
CASE
WHEN @backup_type = N'FULL'
THEN N'BACKUP DATABASE ' + QUOTENAME(@database_name)
+ N' TO DISK = N' + QUOTENAME(@backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', '''')
+ N' WITH STATS = 1' + CASE
WHEN @flag_edition = 1 THEN N', COMPRESSION;'
ELSE N';'
END
WHEN @backup_type = N'DIFF'
THEN N'BACKUP DATABASE ' + QUOTENAME(@database_name)
+ N' TO DISK = N' + QUOTENAME(@backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', '''')
+ N' WITH DIFFERENTIAL, STATS = 1' + CASE
WHEN @flag_edition = 1 THEN N', COMPRESSION;'
ELSE N';'
END
WHEN @backup_type = N'LOG'
THEN N'BACKUP LOG ' + QUOTENAME(@database_name)
+ N' TO DISK = N' + QUOTENAME(@backup_folder + @database_name + N'_' + @backup_type + N'_' + @timestamp + N'.bak', '''')
+ N' WITH STATS = 1' + CASE
WHEN @flag_edition = 1 THEN N', COMPRESSION;'
ELSE N';'
END
ELSE N''
END
; RAISERROR('--==============%d/%d: Working on database: %s', 10, 1, @database_do , @database_count, @database_name) WITH NOWAIT; --Only online database can be Backup.
IF @database_state = 0
BEGIN
RAISERROR('-- %s', 10, 1, @exec_sql) WITH NOWAIT; IF @is_run = 1
BEGIN
SET
@exec_sql = N'SET CONTEXT_INFO ' + @context_info + N';' + @exec_sql
;
EXEC sys.sp_executesql @exec_sql
END
END
ELSE
BEGIN
RAISERROR('-- database %s state: %s can''t be backup, skipped.', 10, 1, @database_name, @database_state_desc) WITH NOWAIT;
END SET @database_do = @database_do + 1;
FETCH NEXT FROM CUR_DatabaseList INTO @database_name, @database_state, @database_state_desc
END -- close and release cursor.
CLOSE CUR_DatabaseList
DEALLOCATE CUR_DatabaseList
GO
还有一点需要注意的是阿里云云数据库RDS是不支持数据库直接操作还原的，在云数据库上操作还原数据库会报如下错误
那么我们如何在阿里云数据库还原数据呢？人家阿里云爸爸也提供了相应的解决方案——OSS备份数据恢复,它可以将指定数据库文件还原到云数据库
相关功能可以参考链接 https://help.aliyun.com/document_detail/68310.html