sql还原数据库备份数据库
In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more.
在本文中，我们将看到备份和还原元数据表如何将信息存储在MSDB数据库中。 另外，讨论一些T-SQL语句以参考数据清除，数据库增长，备份报告，还原历史记录等来派生最有用的信息。
In this article, we’ll discuss the following topics:
在本文中，我们将讨论以下主题：
How do you delete six months old data to reduce the size of the backup and restore history tables? 如何删除六个月前的数据以减小备份和还原历史记录表的大小？ How do you get the Backup History for a specific database including the size, location, and LSN? 如何获得特定数据库的备份历史记录，包括大小，位置和LSN？ How do you create and restore a marked transaction? 您如何创建和还原标记的交易？ How do you find the RESTORE HISTORY of the database? 您如何找到数据库的还原历史？ How do you list the last 30 days restore history at the instance level? 您如何在实例级别列出最近30天的还原历史记录？ How do you measure the database backup or database restore operation progress? 您如何衡量数据库备份或数据库还原操作的进度？ How do you measure the database growth using backup size? 您如何使用备份大小来衡量数据库的增长？ How do you define or estimate the storage required for database backup? 您如何定义或估计数据库备份所需的存储？ How do you get most recent database backup time for each database? 如何获得每个数据库的最新数据库备份时间？ How do you get recent database backup time for each database using PowerShell? 如何使用PowerShell获得每个数据库的最近数据库备份时间？ How do you get recent database backup time for each database across multiple servers using PowerShell? 如何使用PowerShell获得跨多个服务器的每个数据库的最近数据库备份时间？ How do you find the backup history with duration and compressed backup size columns? 如何找到包含“持续时间”和“压缩的备份大小”列的备份历史记录？
问题 (Questions)
MSDB database is a log-store and it stores a complete history of all SQL Server backup-and-restore operations.
MSDB数据库是一个日志存储，它存储所有SQL Server备份和还原操作的完整历史记录。
The following table highlights the high-level detail about the backup-and-restore operation:
下表突出显示了有关备份和还原操作的高级详细信息：
1.如何删除六个月前的数据以减小备份和还原历史记录表的大小？ (1. How do you delete six months old data to reduce the size of the backup and restore history tables?)
To reduce the size of the backup and restore history tables, delete the entries of backup sets that are older than the specified date-time. It is recommended to run sp_delete_backuphistory frequently to clean-up the entries from the MSDB database.
要减少备份和还原历史记录表的大小，请删除早于指定日期时间的备份集条目。 建议经常运行sp_delete_backuphistory以清除MSDB数据库中的条目。
USE msdb GO Declare @date Datetime = dateadd(mm,-6,getdate()) EXEC sp_delete_backuphistory @date
If you want to remove all the entries of backup history for a specific database, run sp_delete_database_backuphistory <database name>
如果要删除特定数据库的备份历史记录的所有条目，请运行sp_delete_database_backuphistory <数据库名称>
USE msdb Gosp_delete_database_backuphistory 'SQLShackDemo'
2.如何获取特定数据库的备份历史记录，包括大小，位置和LSN？ (2. How do you get the Backup History for a specific database including the size, location, and LSN?)
The following T-SQL provides you the backup information and LSN details of a given database.
以下T-SQL为您提供给定数据库的备份信息和LSN详细信息。
The LSN is viewable using the following system-tables and using the restore command:
使用以下系统表和restore命令可以查看LSN：
backupset备份集 backupfile备份文件 sys.database_files;sys.database_files; sys.master_filessys.master_files RESTORE HEADERONLY轻松恢复
In the following example, you can see that the LSN is ordered in a Zig-Zag fashion.
在以下示例中，您可以看到LSN以Zig-Zag方式排序。
SELECTbs.server_name,bs.database_name,bs.backup_start_date,bs.backup_finish_date,bs.user_name,bs.first_LSN,bs.last_LSN,CASEWHEN bs.[type] = 'D'THEN 'Full Backup'WHEN bs.[type] = 'I'THEN 'Differential Database'WHEN bs.[type] = 'L'THEN 'Log'WHEN bs.[type] = 'F'THEN 'File/Filegroup'WHEN bs.[type] = 'G'THEN 'Differential File'WHEN bs.[type] = 'P'THEN 'Partial'WHEN bs.[type] = 'Q'THEN 'Differential partial'END,cast((bs.backup_size/1024)/1024 as numeric(8,0)) 'backup_size MB',bmf.physical_device_name from msdb.dbo.backupset bs inner joinmsdb.dbo.backupmediafamily bmfon bs.media_set_id=bmf.media_set_idwhere bs.database_name='SQLShackDemo' order by bs.backup_start_date desc
3.如何创建和还原标记的交易？ (3. How do you create and restore a marked transaction?)
Marked transactions are very useful to recover the database to a logically consistent point.
标记的事务对于将数据库恢复到逻辑上一致的点非常有用。
To create a marked transaction and restore the marked transaction follow the steps:
要创建标记交易并还原标记交易，请执行以下步骤：
Create full/differential database backup
创建完整/差异数据库备份
BACKUP DATABASE [SQLShackDemo] TO DISK='F:\PowerSQL\SQLShackDemo_FULL_0717.bak' WITH COMPRESSION,STATS=10
Use “BEGIN TRANSACTION WITH MARK” clause to mark the transaction and perform the DML operations
使用“ BEGIN TRANSACTION WITH MARK”子句标记事务并执行DML操作
USE [SQLShackDemo]GOBEGIN TRANSACTION UpdateBackupInfoDemoWITH MARK 'UPDATE Compatibility level';GOUPDATE dbo.BackupInfoSET compatibilitylevel=120WHERE servername='hqdbsp18';GOCOMMIT TRANSACTION UpdateBackupInfo;GO
Back up the SQLShackDemo transaction-log
备份SQLShackDemo事务日志
BACKUP LOG [SQLShackDemo] TO DISK='F:\PowerSQL\SQLShackDemo_LOG_0717.TRN' WITH COMPRESSION,STATS=10
To verify the marked transaction, run the following command
要验证标记的事务，请运行以下命令
SELECT * FROM MSDB.dbo.logmarkhistory
Restore full database backup WITH NORECOVERY option.
使用NORECOVERY选项还原完整的数据库备份。
USE MASTERGODROP DATABASE IF EXISTS [SQLShackDemo]RESTORE DATABASE [SQLShackDemo]FROM DISK='F:\PowerSQL\SQLShackDemo_FULL_0717.bak' WITH NORECOVERY;GO
Restore log WITH STOPATMARK option
使用STOPATMARK选项还原日志
RESTORE LOG [SQLShackDemo]FROMDISK='F:\PowerSQL\SQLShackDemo_LOG_0717.TRN'WITH RECOVERY, STOPATMARK = 'UpdateBackupInfo';
Verify the data
验证数据
SELECT * FROM dbo.BackupInfo WHERE servername='aqdbsp18';
4.您如何找到数据库的还原历史？ (4. How do you find the RESTORE HISTORY of the database?)
This following T-SQL provides you with information about a particular database with the restore history and source, destination, start, end time and type of the restore operation.
下面的T-SQL为您提供有关特定数据库的信息，包括还原历史记录以及还原操作的源，目的地，开始时间，结束时间和类型。
USE msdbGOSELECT bs.server_name,bs.database_name Source_database_name,rh.destination_database_name,bs.backup_set_id,bs.backup_start_date,bs.backup_start_date,bs.backup_finish_date,bs.user_name,bs.first_LSN,bs.last_LSN,CASEWHEN bs.[type] = 'D'THEN 'Full Backup'WHEN bs.[type] = 'I'THEN 'Differential Database'WHEN bs.[type] = 'L'THEN 'Log'WHEN bs.[type] = 'F'THEN 'File/Filegroup'WHEN bs.[type] = 'G'THEN 'Differential File'WHEN bs.[type] = 'P'THEN 'Partial'WHEN bs.[type] = 'Q'THEN 'Differential partial'END,bmf.physical_device_name,rh.restore_date ,rh.stop_at_mark_namefrom backupset bs inner join backupmediafamily bmf on bs.media_set_id=bmf.media_set_id inner join restorehistory rh on bs.backup_set_id=rh.backup_set_idwhere bs.database_name='SQLShackDemo' order by rh.restore_date desc
5.您如何在实例级别列出最近30天的还原历史记录？ (5. How do you list the last 30 days restore history at the instance level?)
The following T-SQL provides you a list of last 30 days data of database restore history.
以下T-SQL为您提供了数据库还原历史记录的最近30天数据的列表。
SELECTb.database_name source_database_name,rh.destination_database_name,rh.user_name AS [Restored By],CASE WHEN rh.restore_type = 'D' THEN 'Database Restore'WHEN rh.restore_type = 'F' THEN 'File Restore'WHEN rh.restore_type = 'G' THEN 'Filegroup Restore'WHEN rh.restore_type = 'I' THEN 'Differential Restore'WHEN rh.restore_type = 'L' THEN 'Log Restore'ELSE rh.restore_type END AS [Restore Type],rh.restore_date,bm.physical_device_name SourceDevice,rf.destination_phys_name destDeviceFROM msdb.dbo.restorehistory rhINNER JOIN msdb.dbo.backupset b ON rh.backup_set_id = b.backup_set_idINNER JOIN msdb.dbo.restorefile r ON rh.restore_history_id = r.restore_history_idINNER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = b.media_set_idWHERE rh.restore_date >= DATEADD(dd, -30,getdate())ORDER BY rh.restore_history_id desc
Output:
输出：
6.您如何衡量数据库备份或数据库还原操作的进度？ (6. How do you measure the database backup or database restore operation progress?)
To measure the backup operation progress or estimate the time and percentage completed, you can query the DMV—sys.dm_exec_requests.
要测量备份操作进度或估计完成的时间和百分比，可以查询DMV — sys.dm_exec_requests。
This script provides the output with backup estimation time and percentage completed.
该脚本为输出提供备份估计时间和完成百分比。
SELECT command,s.text,er.start_time,er.percent_complete,CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hr(s), '+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min(s), '+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' ss' as running_time,CAST((estimated_completion_time/3600000) as varchar) + ' hr(s), '+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min(s), '+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' ss' as est_time_to_go,dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_timeFROM sys.dm_exec_requests erCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) sWHERE mand in ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')
7.如何使用备份大小来衡量数据库的增长？ (7. How do you measure the database growth using backup size?)
You can construct the T-SQL using backup system table to analyze the growth of the database over a given timeframe. The T-SQL script used in the below-mentioned link is used to calculate the capacity planning of the databases. The metrics are useful for capacity planning and forecasting.
您可以使用备份系统表构造T-SQL，以分析给定时间范围内数据库的增长。 以下链接中使用的T-SQL脚本用于计算数据库的容量计划。 这些指标对于容量计划和预测很有用。
Backup and Restore (or Recovery) strategies for SQL Server database
SQL Server数据库的备份和还原（或恢复）策略
8.您如何定义或估计数据库备份所需的存储？ (8. How do you define or estimate the storage required for database backup?)
You can refer the T-SQL to get very detailed information about the database backup history. It also talks about capturing the baseline database growth metrics.
您可以参考T-SQL以获取有关数据库备份历史记录的非常详细的信息。 它还讨论有关捕获基准数据库增长指标的问题。
Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL
使用PowerShell和T-SQL在多服务器环境中规划SQL Server备份和还原策略
9.如何获得每个数据库的最新数据库备份时间？ (9. How do you get most recent database backup time for each database?)
The following T-SQL provides the most recent backup completion time of all databases along with the database name
以下T-SQL提供了所有数据库的最新备份完成时间以及数据库名称
SELECT sd.Name AS DatabaseName,COALESCE(CONVERT(VARCHAR(12), MAX(bs.backup_finish_date), 101),'-') AS LastBackUpTimeFROM sys.sysdatabases sdLEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.nameGROUP BY sd.Name
10.如何使用PowerShell获得每个数据库的最近数据库备份时间？ (10. How do you get recent database backup time for each database using PowerShell?)
The following PowerShell script provides the most recent backup completion time of all databases with the database name
以下PowerShell脚本使用数据库名称提供了所有数据库的最新备份完成时间
Load the SQLServer Module 加载SQLServer模块 Instantiate the SMO class library 实例化SMO类库 Invoke the database property 调用数据库属性
TRY {If (Get-Module SQLServer -ListAvailable) {Write-Verbose "Preferred SQLServer module found"} else{Install-Module -Name SqlServer }} CATCH {Write-Host "Check the Module and version"}$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'hqdbt01'$databases=$srv.Databases$databases | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize
11.如何使用PowerShell获得跨多个服务器的每个数据库的最近数据库备份时间？ (11. How do you get recent database backup time for each database across multiple servers using PowerShell?)
This can be done using looping construct in PowerShell with very few lines of code.
这可以通过使用PowerShell中的循环构造以及很少的代码行来完成。
Load the SQLServer Module 加载SQLServer模块 List the SQL Server instances 列出SQL Server实例 Use looping construct to Instantiate the SMO class library and list the database properties 使用循环构造实例化SMO类库并列出数据库属性
Install-Module -Name SqlServer $List = @('aqdbsp18','aqdbs19','adbs201')$list|%{(New-Object -TypeNameMicrosoft.SQLServer.Management.Smo.Server($ServerName)).Databases| SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize}
12.如何找到包含“持续时间”和“压缩的备份大小”列的备份历史记录？ (12. How do you find the backup history with duration and compressed backup size columns?)
The following T-SQL get the latest successful backups and it includes columns such as database name, backup start time, backup end time, a derived column named duration (mins), backup file location, type of the backup, backup size, and compressed backup size (if used)
以下T-SQL获得了最新的成功备份，并且包括数据库名称，备份开始时间，备份结束时间，派生的列，其名称为持续时间（mins），备份文件位置，备份类型，备份大小和压缩列。备份大小（如果使用）
SELECTbs.database_name DatabaseName,bs.backup_start_date Backup_Start_Date,bs.backup_finish_date Backup_Finished_Date,DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) Duration_Mins,bmf.physical_device_name Backup_File_Location,CASEWHEN bs.[type] = 'D'THEN 'Full_Backup'WHEN bs.[type] = 'I'THEN 'Differential_Backup'WHEN bs.[type] = 'L'THEN 'Log_Backup'WHEN bs.[type] = 'F'THEN 'File/Filegroup backup'WHEN bs.[type] = 'G'THEN 'DifferentialFile_Backup'WHEN bs.[type] = 'P'THEN 'Partial_Backup'WHEN bs.[type] = 'Q'THEN 'Differentialpartial_Backup'END 'Backup_Type',ROUND(((bs.backup_size/1024)/1024),2) Backup_SizeMB,ROUND(((pressed_backup_size/1024)/1024),2) CompressedBackup_SizeMBFROM msdb.dbo.backupmediafamily bmfINNER JOIN msdb.dbo.backupset bsON bmf.media_set_id = bs.media_set_idORDER BY bs.backup_start_date DESC
Output:
输出：
That’s all for now…
目前为止就这样了…
结语 (Wrapping up)
Thus far, we’ve covered most of the concepts of “database backup-and-restore” operations. Please refer the TOC for more information.
到目前为止，我们已经涵盖了“数据库备份和还原”操作的大多数概念。 请参考目录以获取更多信息。
目录 (Table of contents)
参考资料 (References)
Introduction to Log Sequence Numbers 日志序号简介 sp_delete_backuphistory (Transact – SQL) sp_delete_backuphistory（事务– SQL） New – Object 新增–对象翻译自: /sql-interview-questions-on-database-backups-restores-and-recovery-part-iv/
sql还原数据库备份数据库
