Stored procedures: Who is running backups and restores and when

Backup and restore commands can be issued by running T-SQL, Enterprise Manager or Maintenance Plans. However, it's still not easy to determine who issued backups and restores -- and when.

Such information is stored in the msdb database; an entry is logged in msdb whenever a backup or restore runs. This data is not easy to access via the GUI tools, but it can be retrieved through queries. The following tables contain backup and restore information whenever one of these commands is issued.

Backup tables

Restore tables

You can query these tables individually, but to fully make sense of the data you must query the tables together. To simplify the process, the following stored procedures can be created in the msdb database or in one of your user databases. The first stored procedure queries the backup history and the second queries the restore his

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery The keys to database backup protection for SQL Server Choosing a SQL Server disaster recovery solution Licensing a standby server for SQL Server replication Can I encrypt and restore a database backup in SQL Server 2005? SQL Server errors, failures and other problems fixed from the trenches Get SQL Server log shipping functionality without Enterprise Edition SQL Server 2008 backup compression pros and cons SQL Server backups using SAN database snapshots Tips for scheduling and testing SQL Server backups Code to restore SQL Server databases in VB.NET SQL Server Backup and Recovery Research SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Database Administration Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server Implementing SQL Server 2008 FILESTREAM functionality Improving SQL Server full-text search performance Using the OPENROWSET function in SQL Server New replication features in SQL Server 2008 and what they mean to you

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary rollback  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

tory.

[TABLE]

[TABLE]

[TABLE]

[TABLE]

These two simple, but effective stored procedures can help shed light on when backups and restores are being issued against your server. You can run the stored procedures every day, so you can be sure backups are running properly. Also, when you need to restore, use the history to see the order of the backups and which backup files exist and where they are physically located.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the Backup and Recovery expert welcomes your questions.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.