多数の投稿があり、それらは1つの単純な真実を主張しています-継続的にバックアップを行う必要があります。 ただし、人々は常に2つのカテゴリに分けられます。誰がまだバックアップを行っていないか、もう誰がバックアップを行っているかです。 そのようなアドバイスを無視する最初のカテゴリは、ほぼ同じ質問を持つ専門のフォーラムで見つけることができます。
-ディスクが飛んだ/誰かがデータベースを削除した...データを復元するにはどうすればよいですか？
-新しいバックアップはありますか？
-いいえ
そのような状況の主人公にならないためには、最小限の労力を費やす必要があります。 最初に、バックアップを保存するディスクアレイを割り当てます。 データベースファイルと一緒にバックアップを保存することは、明らかに私たちの選択ではありません。 2番目は、データベースのバックアップメンテナンス計画を作成することです。
次に行うこと、およびその後のバックアップに関連するいくつかの微妙な点について説明します。
バックアップの作成時にエラーメッセージが記録されるテーブルを作成します。
USE [master] GO IF OBJECT_ID('dbo.BackupError', 'U') IS NOT NULL DROP TABLE dbo.BackupError GO CREATE TABLE dbo.BackupError ( db SYSNAME PRIMARY KEY, dt DATETIME NOT NULL DEFAULT GETDATE(), msg NVARCHAR(2048) ) GO
私がこれを毎日使用するためのデータベースをバックアップするためのスクリプト：
USE [master] GO SET NOCOUNT ON TRUNCATE TABLE dbo.BackupError DECLARE @db SYSNAME , @sql NVARCHAR(MAX) , @can_compress BIT , @path NVARCHAR(4000) , @name SYSNAME , @include_time BIT --SET @path = '\\pub\backup' -- IF @path IS NULL -- EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @path OUTPUT, 'no_output' SET @can_compress = ISNULL(CAST(( -- SELECT value FROM sys.configurations WHERE name = 'backup compression default') AS BIT), 0) DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT d.name FROM sys.databases d WHERE d.[state] = 0 AND d.name NOT IN ('tempdb') -- OPEN cur FETCH NEXT FROM cur INTO @db WHILE @@FETCH_STATUS = 0 BEGIN IF DB_ID(@db) IS NULL BEGIN INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db is missing') END ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db state != ONLINE') END ELSE BEGIN BEGIN TRY SET @name = @path + '\T' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + @db + '.bak' SET @sql = ' BACKUP DATABASE ' + QUOTENAME(@db) + ' TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT' + CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END --PRINT @sql EXEC sys.sp_executesql @sql END TRY BEGIN CATCH INSERT INTO dbo.BackupError (db, msg) VALUES (@db, ERROR_MESSAGE()) END CATCH END FETCH NEXT FROM cur INTO @db END CLOSE cur DEALLOCATE cur
データベースメールコンポーネントがサーバーで構成されている場合、スクリプトで発生した問題に関するメールによる通知を追加できます。
IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN DECLARE @report NVARCHAR(MAX) SET @report = '<table border="1"><tr><th>database</th><th>date</th><th>message</th></tr>' + CAST(( SELECT td = db, '', td = dt, '', td = msg FROM dbo.BackupError FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX)) + '</table>' EXEC msdb.dbo.sp_send_dbmail @recipients = 'your_account@mail.ru', @subject = 'Backup Problems', @body = @report, @body_format = 'HTML' END
実際、この段階で、バックアップを自動的に作成するための作業スクリプトが用意されています。 このスクリプトをスケジュールに従って実行するjobを作成します。
Expressエディションの所有者については、 SQL Server ExpressエディションでSQL Server エージェントを使用する方法がないため、別途言及する必要があります 。 これらの言葉の後にどんな悲しみが来ても、実際にはすべてが解決されます。 最も簡単な方法は、ほぼ同じ内容のbatファイルを作成することです。
sqlcmd -S <ComputerName>\<InstanceName> -i D:\backup.sql
次に、 タスクスケジューラを開き、その中に新しいタスクを作成します。
2番目の方法は、スケジュールに従ってタスクを実行できるサードパーティ開発を使用することです。 その中でも、便利で無料のツールであるSQL Schedulerを強調できます。 私のインストーラーは失われたので、誰かが読者のために機能するリンクを共有してくれたらありがたいです。
次に、バックアップに関連する有用なささいなことについて話しましょう。
絞る...
バックアップ圧縮機能は、 SQL Server 2008で初めて登場しました。 2005年のバージョンで作業しているときにバックアップを圧縮しなければならなかった時代を懐かしく思い出します。 今、すべてがはるかに簡単になりました。
ただし、 COMPRESSIONパラメーターを指定してBACKUPコマンドを実行するか、次のコマンドを使用してデフォルトの圧縮を有効にした場合にのみ、バックアップ圧縮が使用されることに注意する必要があります。
USE [master] GO EXEC sp_configure 'backup compression default', 1 RECONFIGURE WITH OVERRIDE GO
ちなみに、圧縮バックアップにはいくつかの利点があると言われます：それらを保存するために必要なスペースが少なく、圧縮バックアップからデータベースを復元するのは通常少し速く実行され、 I / O操作が少ないのでより速く作成されます。 ところで、欠点もあります-圧縮されたバックアップを使用する場合、プロセッサの負荷が増加します。
このクエリを使用すると、圧縮の有無にかかわらず最後の完全バックアップのサイズを返すことができます。
SELECT database_name , backup_size_mb = backup_size / 1048576.0 , compressed_backup_size_mb = compressed_backup_size / 1048576.0 , compress_ratio_percent = 100 - compressed_backup_size * 100. / backup_size FROM ( SELECT database_name , backup_size , compressed_backup_size = NULLIF(compressed_backup_size, backup_size) , RowNumber = ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) FROM msdb.dbo.backupset WHERE [type] = 'D' ) t WHERE t.RowNumber = 1
通常、バイナリデータを考慮しない場合、圧縮は40〜90％に達します。
database_name backup_size_mb compressed_backup_size_mb compress_ratio_percent ------------------------------- ---------------- -------------------------- ------------------------ AdventureWorks2012 190.077148437 44.652716636 76.50810894222767 DB_Dev 1530.483398437 295.859273910 80.66890015190163 BinDocuments 334.264648437 309.219978332 7.49246748707956 locateme 37.268554687 17.247792243 53.72025454546944 master 3.643554687 0.654214859 82.04459888434736 model 2.707031250 0.450525283 83.35721895292208 msdb 17.147460937 2.956551551 82.75807967958028 OnlineFormat 125.078125000 23.639108657 81.10052524545207 Refactoring 286.076171875 35.803841590 87.48450758543927 ReportServer$SQL_2012 4.045898437 0.696615219 82.78218719828627 ReportServer$SQL_2012TempDB 2.516601562 0.428588867 82.96953822273962
前の要求を変更すると、バックアップが作成されたデータベースを監視できます。
SELECT d.name , rec_model = d.recovery_model_desc , f.full_time , f.full_last_date , f.full_size , f.log_time , f.log_last_date , f.log_size FROM sys.databases d LEFT JOIN ( SELECT database_name , full_time = MAX(CASE WHEN [type] = 'D' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END) , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END) , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END) , log_time = MAX(CASE WHEN [type] = 'L' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END) , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END) , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END) FROM ( SELECT s.database_name , s.[type] , s.backup_start_date , s.backup_finish_date , backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN s.backup_size ELSE s.compressed_backup_size END / 1048576.0 , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC) FROM msdb.dbo.backupset s WHERE s.[type] IN ('D', 'L') ) f WHERE f.RowNum = 1 GROUP BY f.database_name ) f ON f.database_name = d.name
SQL Server 2005を使用している場合、次の行：
backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN ...
変更する必要があります：
backup_size = s.backup_size / 1048576.0
このクエリの結果は、多くの問題の防止に役立ちます。
name rec_model full_time full_last_date full_size log_time log_size ------------------- --------- --------- ------------------- ----------------- --------- ------------ master SIMPLE 00:00:01 2015-11-06 15:08:12 0.654214859 NULL NULL tempdb SIMPLE NULL NULL NULL NULL NULL model FULL 00:00:00 2015-11-06 15:08:12 0.450525283 NULL NULL msdb SIMPLE 00:00:00 2015-11-06 15:08:12 2.956551551 NULL NULL DB_Dev FULL 00:00:13 2015-11-06 15:08:26 295.859273910 00:00:04 72.522538642 BinDocuments FULL 00:00:05 2015-11-06 15:08:31 309.219978332 00:00:01 2.012338638 Refactoring SIMPLE 00:00:02 2015-11-06 15:08:33 35.803841590 NULL NULL locateme SIMPLE 00:00:01 2015-11-06 15:08:34 17.247792243 NULL NULL AdventureWorks2012 FULL 00:00:02 2015-11-06 15:08:36 44.652716636 NULL NULL OnlineFormat SIMPLE 00:00:01 2015-11-06 15:08:39 23.639108657 NULL NULL
すべてのデータベースについて、現在の日付の完全バックアップがあることがすぐにわかります。
次に、バックアップの作成時間を確認できます。 なぜ求めているのですか？ DB_Devデータベースのバックアップが5秒かかる前に、1時間かかり始めたとします。 これには多くの理由があります。ディスクが負荷に対応できず、データベース内のデータが不適切なボリュームになり、ディスクがRAIDになり、書き込み速度が低下しました。
データベースの復旧モデルがFULLまたはBULK_LOGGEDである場合、 LDFファイルが絶えず増大するという苦痛にサーバーが耐えられないように、時々ログをバックアップすることをお勧めします。 データベースのデータファイルとログの充填の程度は、次のクエリで表示できます。
IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL DROP TABLE #space CREATE TABLE #space ( database_id INT PRIMARY KEY, data_used_size DECIMAL(18,6), log_used_size DECIMAL(18,6) ) DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT ' USE [' + d.name + '] INSERT INTO #space (database_id, data_used_size, log_used_size) SELECT DB_ID() , SUM(CASE WHEN [type] = 0 THEN space_used END) , SUM(CASE WHEN [type] = 1 THEN space_used END) FROM ( SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024) FROM sys.database_files s GROUP BY s.[type] ) t;' FROM sys.databases d WHERE d.[state] = 0 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') EXEC sys.sp_executesql @SQL SELECT database_name = DB_NAME(t.database_id) , t.data_size , s.data_used_size , t.log_size , s.log_used_size , t.total_size FROM ( SELECT database_id , log_size = SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 , data_size = SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 , total_size = SUM(size) * 8. / 1024 FROM sys.master_files GROUP BY database_id ) t LEFT JOIN #space s ON t.database_id = s.database_id
ローカルインスタンスでのクエリ結果：
database_name data_size data_used_size log_size log_used_size total_size --------------------- ------------- --------------- ------------- -------------- -------------- master 4.875000 3.562500 1.750000 0.781250 6.625000 tempdb 8.000000 4.500000 0.500000 0.632812 8.500000 model 4.062500 2.562500 1.250000 0.609375 5.312500 msdb 16.687500 16.062500 26.187500 2.804687 42.875000 DB_Dev 1782.812500 1529.562500 7286.125000 42.570312 9068.937500 BinDocuments 334.000000 333.500000 459.125000 12.031250 793.125000 Refactoring 333.125000 285.625000 127.882812 0.851562 461.007812 locateme 591.000000 36.500000 459.125000 8.585937 1050.125000 AdventureWorks2012 205.000000 189.125000 0.750000 0.453125 205.750000 OnlineFormat 125.375000 124.437500 1.015625 0.414062 126.390625
私はまた、生活を楽にすることができるいくつかの興味深いトリックを見せたかった。 BACKUPコマンドの実行時に複数のパスを指定すると、結果のバックアップファイルはほぼ同じサイズの断片に分割されます。
BACKUP DATABASE AdventureWorks2012 TO DISK = 'D:\AdventureWorks2012_1.bak', DISK = 'D:\AdventureWorks2012_2.bak', DISK = 'D:\AdventureWorks2012_3.bak'
FAT32ファイルシステムを使用してUSBフラッシュドライブにバックアップをコピーする必要が生じたときに便利になりました。これには最大ファイルサイズの制限があります。
もう1つの興味深い機会は、バックアップコピーを作成することです。 個人的な経験から言うと、最初にデフォルトのフォルダにバックアップを作成し、それを手またはスクリプトでディスクボールにコピーした人に会ったことがあります。 そして、次のコマンドを使用する必要がありました。
BACKUP DATABASE AdventureWorks2012 TO DISK = 'D:\AdventureWorks2012.bak' MIRROR TO DISK = 'E:\AdventureWorks2012_copy.bak' WITH FORMAT
「毎日」のタスクの自動化に関する以前の投稿：
パート1：自動デフラグインデックス
パート2：統計の自動更新