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:
下表突出显示了有关备份和还原操作的高级详细信息：
| System tables | Description |
| backupfile | The system table provides the most granular details of the backup file. It stores one row for each data file or log file of a database. The columns describe the file type, file group name, page size and file configuration information. |
| backupfilegroup | The table in-house the filegroup configuration of the database. It stores one row for each filegroup in a database. |
| backupmediafamily | It stores a row for each media family. |
| backupmediaset | It stores a row for each backup media set. |
| backupset | It contains a row for each backup set for successful backup. |
| logmarkhistory | Contains one row for each marked transaction that has been committed. It is applicable to only those databases where the recovery model is set to full or bulk-logged. |
| restorefile | It stores one row for each restored file. |
| restorefilegroup | It stores one row for each restored filegroup. |
| restorehistory | It stores one row for each restore operation |
| suspect_pages | It stores one row per page that failed with the error 823 or 824 See the link for more information How to perform a page level restore in SQL Server |
| 系统表 | 描述 |
| 备份文件 | 系统表提供了备份文件的最详细信息。 它为数据库的每个数据文件或日志文件存储一行。 这些列描述文件类型，文件组名称，页面大小和文件配置信息。 |
| 备份文件组 | 该表位于数据库的文件组配置内部。 它为数据库中的每个文件组存储一行。 |
| 备份媒体家庭 | 它为每个媒体系列存储一行。 |
| 备份媒体集 | 它为每个备份媒体集存储一行。 |
| 备份集 | 每个成功备份的备份集都包含一行。 |
| 对数历史 | 对于已提交的每个标记的事务，包含一行。 它仅适用于将恢复模型设置为完全记录或批量记录的数据库。 |
| 恢复文件 | 它为每个还原的文件存储一行。 |
| restorefilegroup | 它为每个还原的文件组存储一行。 |
| 恢复历史 | 它为每个还原操作存储一行 |
| 典藏页面 | 它每页存储一行，失败并显示错误823或824，有关详细信息，请参阅链接。 如何在SQL Server中执行页面级还原 |
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
Go
sp_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_files sys.master_files
- RESTORE HEADERONLY 轻松恢复
In the following example, you can see that the LSN is ordered in a Zig-Zag fashion.
在以下示例中，您可以看到LSN以Zig-Zag方式排序。
SELECT
bs.server_name,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.user_name,
bs.first_LSN,
bs.last_LSN,
CASE
WHEN 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 join msdb.dbo.backupmediafamily bmf
on bs.media_set_id=bmf.media_set_id
where
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_07172018.bak' WITH COMPRESSION,STATS=10Use “BEGIN TRANSACTION WITH MARK” clause to mark the transaction and perform the DML operations
使用“ BEGIN TRANSACTION WITH MARK”子句标记事务并执行DML操作
USE [SQLShackDemo] GO BEGIN TRANSACTION UpdateBackupInfoDemo WITH MARK 'UPDATE Compatibility level'; GO UPDATE dbo.BackupInfo SET compatibilitylevel=120 WHERE servername='hqdbsp18'; GO COMMIT TRANSACTION UpdateBackupInfo; GOBack up the SQLShackDemo transaction-log
备份SQLShackDemo事务日志
BACKUP LOG [SQLShackDemo] TO DISK='F:\PowerSQL\SQLShackDemo_LOG_07172018.TRN' WITH COMPRESSION,STATS=10To verify the marked transaction, run the following command
要验证标记的事务，请运行以下命令
SELECT * FROM MSDB.dbo.logmarkhistory
Restore full database backup WITH NORECOVERY option.
使用NORECOVERY选项还原完整的数据库备份。
USE MASTER GO DROP DATABASE IF EXISTS [SQLShackDemo] RESTORE DATABASE [SQLShackDemo] FROM DISK='F:\PowerSQL\SQLShackDemo_FULL_07172018.bak' WITH NORECOVERY; GORestore log WITH STOPATMARK option
使用STOPATMARK选项还原日志
RESTORE LOG [SQLShackDemo] FROM DISK='F:\PowerSQL\SQLShackDemo_LOG_07172018.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 msdb
GO
SELECT
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,
CASE
WHEN 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_name
from 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_id
where
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天数据的列表。
SELECT
b.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 destDevice
FROM msdb.dbo.restorehistory rh
INNER JOIN msdb.dbo.backupset b ON rh.backup_set_id = b.backup_set_id
INNER JOIN msdb.dbo.restorefile r ON rh.restore_history_id = r.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = b.media_set_id
WHERE
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_time
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
WHERE er.command 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
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以获取有关数据库备份历史记录的非常详细的信息。 它还讨论有关捕获基准数据库增长指标的问题。
使用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 LastBackUpTime
FROM sys.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name
GROUP 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 -TypeName Microsoft.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），备份文件位置，备份类型，备份大小和压缩列。备份大小（如果使用）
SELECT
bs.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,
CASE
WHEN 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(((bs.compressed_backup_size/1024)/1024),2) CompressedBackup_SizeMB
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
ON bmf.media_set_id = bs.media_set_id
ORDER 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 新增–对象
翻译自: https://www.sqlshack.com/sql-interview-questions-on-database-backups-restores-and-recovery-part-iv/
sql还原数据库备份数据库
1683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
