SQLServerで複数DBをバックアップ＆リストア
SQLServerで「Transact-SQL」を利用して、複数のDBのバックアップを取得し、リストアを行う方法について。
バックアップの取得
バックアップ元のSQLServerで以下のクエリを実行する。
※「ｓｙｓｄａｔａｂａｓｅｓ」は、全角となっているので半角に変更して実行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | use master go /*************/ /* バックアップ対象ＤＢ名を控える変数 */ /*************/ DECLARE @dbname varchar(80); /**************/ /* バックアップ対象ＤＢ一覧を取得するカーソル */ /**************/ DECLARE DbNameList CURSOR FOR select name from ｓｙｓｄａｔａｂａｓｅｓ /********/ /* ＤＢの完全バックアップ取得を開始 */ /********/ OPEN DbNameList; print '開始'; /************/ /* バックアップ対象となるＤＢ名を取得 */ /************/ FETCH NEXT FROM DbNameList INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN IF @dbname <> 'tempdb' and @dbname <> 'pubs' and @dbname <> 'Northwind' and @dbname <> 'msdb' and @dbname <> 'model' and @dbname <> 'master' BEGIN /************/ /* ＤＢの完全バックアップ */ /************/ print '→バックアップ対象ＤＢ：' + @dbname; EXEC ('BACKUP DATABASE [' + @dbname + '] TO DISK = ''D:\TEMP\MSSQL\MSSQL_' + @dbname + '_full.bak'' WITH STATS;'); END; /*************/ /* 次のバックアップ対象となるＤＢ名を取得 */ /*************/ FETCH NEXT FROM DbNameList INTO @dbname; END; CLOSE DbNameList; DEALLOCATE DbNameList; print '終了'; GO |
バックアップ対象リストの取得
バックアップ元のSQLServerで以下のクエリを実行する。
※「ｓｙｓｄａｔａｂａｓｅｓ」は、全角となっているので半角に変更して実行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | use master go /*************/ /* バックアップ対象ＤＢ名を控える変数 */ /*************/ DECLARE @dbname varchar(80); DECLARE @dbList varchar(1000); /**************/ /* バックアップ対象ＤＢ一覧を取得するカーソル */ /**************/ DECLARE DbNameList CURSOR FOR select name from ｓｙｓｄａｔａｂａｓｅｓ /********/ /* バックアップ対象DBのリストアップを開始 */ /********/ OPEN DbNameList; /************/ /* バックアップ対象となるＤＢ名を取得 */ /************/ FETCH NEXT FROM DbNameList INTO @dbname; SET @dbList = '' WHILE @@FETCH_STATUS = 0 BEGIN /************/ /* バックアップ対象ＤＢのリストを出力 */ /************/ /* システムDB以外の場合 */ IF @dbname <> 'tempdb' and @dbname <> 'pubs' and @dbname <> 'Northwind' and @dbname <> 'msdb' and @dbname <> 'model' and @dbname <> 'master' BEGIN /* バックアップ対象DBの場合 */ IF @dbname <> 'XXX' and @dbname <> 'YYY' and @dbname <> 'ZZZ' BEGIN IF @dbList <> '' SET @dbList = @dbList + '|'; SET @dbList = @dbList + @dbname; END END /*************/ /* 次のバックアップ対象となるＤＢ名を取得 */ /*************/ FETCH NEXT FROM DbNameList INTO @dbname; END; print 'SELECT @dbNameAll = ''' + @dbList + '''' CLOSE DbNameList; DEALLOCATE DbNameList; GO |
リストア
リストア先のSQLServerで以下のクエリを実行する。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | use master go --変数定義 DECLARE @dbNameAll varchar(1000); --データベースの名称リスト DECLARE @bkPath varchar(100); --読み込みバックアップファイルのPATH DECLARE @outPath varchar(100); --mdf、ldfファイルの配置先（PATH） DECLARE @dbname varchar(100); --データベースの名称 DECLARE @bkFname varchar(200); -- 読み込みバックアップファイルのPATH + ファイル名 DECLARE @mdfName varchar(100); --mdfファイルの名称 DECLARE @ldfName varchar(100); --ldfファイルの名称 DECLARE @outMdf varchar(200); --mdfファイルのPATH + ファイル名 DECLARE @outLdf varchar(200); --ldfファイルのPATH + ファイル名 --データベースの名称リスト SELECT @dbNameAll = 'ReportServer|ReportServerTempDB|JDB|KAIGAI|KANSA|KYOIKU|TEIAN|JDBT|AUTOREGI|AUTOREGI_T' --読み込みバックアップファイルのPATH SELECT @bkPath = 'D:\TEMP\MSSQL\' --mdf、ldfファイルの配置先（PATH） SELECT @outPath = 'D:\MSSQL\' print '開始'; WHILE @dbNameAll <> '' BEGIN --データベースの名称セット IF charindex('|', @dbNameAll) > 0 BEGIN set @dbname = ltrim(rtrim( substring(@dbNameAll, 1, charindex('|',@dbNameAll)-1) )) ; set @dbNameAll = ltrim(rtrim( substring(@dbNameAll, charindex('|',@dbNameAll)+1, len(@dbNameAll)) )); END ELSE BEGIN set @dbname = @dbNameAll; set @dbNameAll = ''; END --ログ出力 print '' print '****************リストアＤＢ：' + @dbname; --読み込みDISKのファイル名 IF @dbname = 'AAA' --データベースの論理名とファイル名が異なる場合（論理名が「AAA」、ファイル名が「BBB.mdf」「BBB_log.ldf」） BEGIN SELECT @bkFname = @bkPath + 'MSSQL_' + @dbname + '_full.bak' SELECT @mdfName = 'BBB' SELECT @ldfName = 'BBB_log' SELECT @outMdf = @outPath + @dbname + '.mdf' SELECT @outLdf = @outPath + @dbname + '.ldf' END ELSE IF @dbname = 'XXX' --データベースの論理名とファイル名が異なる場合（論理名が「XXX」、ファイル名が「YYY.mdf」「YYY_log.ldf」） BEGIN SELECT @bkFname = @bkPath + 'MSSQL_' + @dbname + '_full.bak' SELECT @mdfName = 'YYY' SELECT @ldfName = 'YYY_log' SELECT @outMdf = @outPath + @dbname + '.mdf' SELECT @outLdf = @outPath + @dbname + '.ldf' END ELSE --データベースの論理名とファイル名が同じ場合 BEGIN SELECT @bkFname = @bkPath + 'MSSQL_' + @dbname + '_full.bak' SELECT @mdfName = @dbname SELECT @ldfName = @dbname + '_log' SELECT @outMdf = @outPath + @mdfName + '.mdf' SELECT @outLdf = @outPath + @ldfName + '.ldf' END --リストア実行 RESTORE DATABASE @dbname --復元するデータベースの名称 FROM DISK = @bkFname --復元元ファイル名 WITH REPLACE --同じ名称のデータベースがあれば、既存を削除 , MOVE @mdfName TO @outMdf , MOVE @ldfName TO @outLdf END; print '終了'; GO |
