今天研究阿里云服务数据库的迁移，备份和还原的时候，在阿里云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