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 = N'test_migr', @backup_type = N'FULL', @backup_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\FULL.bak', @is_run = 1; -- 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'[TestMigration];[TestFG],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 = 0 -- 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), @version_major FLOAT, @edition SYSNAME, @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, @version_major = CASE PARSENAME(CAST(SERVERPROPERTY('productversion') AS nvarchar(128)), 4) WHEN 7 THEN 7.0 WHEN 8 THEN 2000 WHEN 9 THEN 2005 WHEN 10 THEN CASE PARSENAME(CAST(SERVERPROPERTY('productversion') AS nvarchar(128)), 3) WHEN 0 THEN 2008 WHEN 50 THEN 2008.5 ELSE 0.0 END WHEN 11 THEN 2012 WHEN 12 THEN 2014 WHEN 13 THEN 2016 WHEN 14 THEN 2017 WHEN 15 THEN 2019 ELSE 0.0 END, @edition = CONVERT(sysname, SERVERPROPERTY(N'Edition')), @flag_edition = CASE WHEN @version_major < 2008 THEN 0 -- lower then SQL 2008, fg: SQL 2000 / 2005 WHEN @version_major >= 2008 and @version_major < 2012 -- SQL 2008 & 2008R2 THEN CASE WHEN @edition LIKE N'Enterprise%' OR @edition LIKE N'Developer%' THEN 1 ELSE 0 END WHEN @version_major >= 2012 -- 2012 or later on THEN CASE WHEN @edition LIKE N'Enterprise%' OR @edition LIKE N'Developer%' OR @edition LIKE N'Standard%' THEN 1 ELSE 0 END 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