When was the last time your SQL Server database was restored
来源:互联网 发布:推广软件有哪些 编辑:程序博客网 时间:2024/06/01 10:52
When was the last time your SQL Server database was restored
Written By: Thomas LaRock -- 4/7/2009 -- 0 comments
Stay informed - get the MSSQLTips.com newsletter and win - click here
Problem
Often times we are asked the question "when was the last time my database was restored, and where was it restored from?" In this tip, we will look at some of the system tables that capture restore history information and how you can query these system tables to answer this question.
Solution
The restore history information is readily available inside the msdb, making the solution as easy as a few lines of T-SQL.
Returning the details
Here is some T-SQL that will return information about the last time a database has been restored. There are two variables, @dbname and @days, that you can configure. The first (@dbname) would be the name of the database you are searching for and would need to be enclosed in single quotation marks. If you leave it NULL than all databases will be returned. The second variable (@days) would be a negative integer (i.e., -7) which represents how many days previously you want to search. So, -7 would translate to returning the previous week's worth of history. If you leave it NULL then the script will default to searching for only the previous thirty days.
DECLARE @dbname sysname, @days intSET @dbname = NULL --substitute for whatever database name you wantSET @days = -30 --previous number of days, script will default to 30SELECT rsh.destination_database_name AS [Database], rsh.user_name AS [Restored By], CASE WHEN rsh.restore_type = 'D' THEN 'Database' WHEN rsh.restore_type = 'F' THEN 'File' WHEN rsh.restore_type = 'G' THEN 'Filegroup' WHEN rsh.restore_type = 'I' THEN 'Differential' WHEN rsh.restore_type = 'L' THEN 'Log' WHEN rsh.restore_type = 'V' THEN 'Verifyonly' WHEN rsh.restore_type = 'R' THEN 'Revert' ELSE rsh.restore_type END AS [Restore Type], rsh.restore_date AS [Restore Started], bmf.physical_device_name AS [Restored From], rf.destination_phys_name AS [Restored To]FROM msdb.dbo.restorehistory rsh INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_idWHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous daysAND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return allORDER BY rsh.restore_history_id DESCGO
The script will return the following result set:
Here is the definition of each of the result set columns.
Column Name
Description
DatabaseThe name of the target database.Restored ByThe name of the user that performed the restore.Restore TypeThe type of restore performed. The possible types include the following:- D - Database
- F - File
- G - Filegroup
- I - Differential
- L - Log
- V - Verifyonly
- R - Revert
Next Steps
- Take the above code and execute against your instance, making certain to insert the correct database name and/or number of days.
- Use this as a weekly check to see if any database restores have been done that you are unaware of.
- Take a look at these other history reports for backup and restore:
- Script to retrieve SQL Server database backup history; last week, most recent and no backups
- SQL Server Backup History Analysis
- Download the script here
- When was the last time your SQL Server database was restored
- Last packet sent to the server was 0 ms ago
- Last packet sent to the server was 0 ms ago
- When the operation was concluded
- (2)Mysql ----- The last packet successfully received from the server was *** millisecond ago.The
- The last packet sent successfully to the server was 0 milliseconds ago. The
- phoneGap the last location provider was disabled
- Communications link failure,The last packet successfully received from the server was *** millisecon
- The last packet successfully received from the server was XXX seconds ago
- mysql The last packet successfully received from the server was XXX seconds ago
- 彻底解决"The last packet successfully received from the server was * milliseconds ago"问题
- Communications link failure,The last packet successfully received from the server was *** millisecon
- [Java] 解决异常:“The last packet sent successfully to the server was 0 milliseconds ago.
- The last packet successfully received from the server was 78,682,686 milliseconds ago
- 错误Communications link failure,The last packet successfully received from the server was的解决方法
- MYSQL Communications link failure,The last packet successfully received from the server was
- The last packet sent successfully to the server was 0 milliseconds ago.
- The last packet successfully received fro m the server was 63,020,509 milliseconds ago
- 深度第一期:SSL VPN由浅入深
- 串口通信
- 在MSDB中清除SQL Server 还原备份历史数据(Purging MSDB Backup and Restore History from SQL Server)
- 深度第二期:Boson NetSim for CCNP 模拟器
- 我的PHP5 + Apache2.2 + MySql5.1配置经历
- When was the last time your SQL Server database was restored
- oracle中获取字段值存在重复的记录
- Hibernate核心接口简介
- 深度第三期:CISCO PIX防火墙系列
- 关于session与cookie区别
- 抓拍MSN中国办公室
- Auto generate change scripts in SQL Server Management Studio (SSMS) for tables
- 如何解决超出显示范围
- 装饰模式