An experienced DBA understands the meaning of backups.
Say a project manager calls you just as you are about to leave the office to spend a nice, quiet evening with your wife or husband. "Help! We just dropped our main table in production. It was a mistake! We need to recover it ASAP to the latest version we have ...."
Your blood has gone from your face to your feet and your whole existence is wondering: Do I really have a backup for this database? When was the last time I checked the maintenance plans? What would happen if I couldn't find the right backup?
Imagine that you have more than 200 SQL Server instances to administer, and you simply want to know when the last backup was taken for each database on each instance so you can anticipate if there will be trouble. You have maintenance plans and you are sending yourself e-mail messages on failure, but are you sure you have maintenance plans are in place for every new database? Are you sure the backup frequency is correct?
Here is a small stored procedure to help you verify such backup information.
TABLE OF CONTENTS
[IMAGE] p_SelectLastDatabaseBackups
[IMAGE] Stored procedure results
[IMAGE] How to run the stored procedure
[IMAGE][IMAGE] p_SelectLastDatabaseBackups[IMAGE] Return to Table of Contents
[IMAGE][IMAGE] Stored procedure results[IMAGE] Return to Table of Contents
Number of days since last backup
Backup type (D-database, L-log)
Backup_size
Database_name
104
D
12664832
My_database1
3
L
84480
My_database1
105
D
2705920
Northwin...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
d
79
L
12345
Master
79
D
1231
Msdb
50
D
11397120
My_database2
49
D
11261440
My_database3
0
NULL
NULL
NoBackupsDatabase*
Note that the last database was never backed up!
You can play with the given query as it suits you. For example, if you add the HAVING clause, you can perform such tasks as:
1. Querying backups only when the last one ran more than X days ago.
2. Querying backups only when the backup size is more than Y.
3. Capturing all backup information for reports.
[IMAGE][IMAGE] How to run the stored procedure[IMAGE] Return to Table of Contents
You can run this query on each SQL Server instance. There are several ways to do so, including:
1. Creating one or several DTS packages that connect to each instance, running the script and copying the results to the central server.
2. Running the script on each instance: running a local job and sending e-mail message with the results.
3. Building a program that connects to all the known SQL Server instances (using SQL-DMO or creating a list in a table or in a file), running the script and centralizing the data.
Tip: How to check backups in all the other known instances from one central database
Say you want to run a stored procedure only once and get the backup information from all existing database on all the known SQL Serverinstances on the network. How do you do that? Watch this:
As a DBA, I like to code things in my natural habitat. I feel more comfortable doing that. And I like to run this stored procedure from a central database. Seems impossible? Let's see how you can do that.
Step 1: Choose a SQL Server instance with a central database that will collect all of the backup information. All of the following steps must run on this central machine, SQL instance or database accordingly.
Step 2: We can use the OSQL –L command to create the list of SQL Server instances on the network. Books Online says that the -L option "Lists the locally configured servers and the names of the servers broadcasting on the network."
Run OSQL –L once from the Command Line and check the list:
- If you don't see all the servers, you should define the missing ones in the "Client Network Utility" as an Alias.
- If you see old servers, try to delete their entry from the aliases in the "Client Network Utility."
To run Client Network Utility: Go to Start --> Programs --> Microsoft SQL Server --> Client Network Utility. The "Server alias" is the logical name by which the client recognizes the server. The "Server Name" is the name of the instance or its network address. When you run a query in SQL Server, you always do so from a client tool. If you add an alias where your client is running, you can use it to impersonate a server.
The following offers an example:
[IMAGE]
[IMAGE]
If I will connect to Michelle now from the machine where this Alias was defined, I will connect to my (local) SQL Server instance.
Step 3: We also have to make sure that we have common access to the instances on the list either by:
1. Windows Authentication mode (recommended).
2. SQL Server Authentication mode (not recommended).
With SQL Server authentication mode, you have to hard code the password. In my example I use (of course) the Windows Authentication mode.
Step 4: Let's create the table that will hold the backup information (you can remove or add columns to suit your requirements).
-------------------------------------------------------------------------
Create a centralized table for backup data (run this only once)
---------------------------------------------------------------------------
Step 5: Run the following script/SP on a regular basis from the central database (i.e: run a job from SQL SERVER Agent):
--------------------------------------------------------------------------
Fetch all SQL SERVER known instances with OSQL -L
---------------------------------------------------------------------------
--------------------------------------------------------------------------
If the current server is not connected to the network, the string "-- NONE --" will be the result from OSQL -L. There is no meaning for this script to run if there are no other servers.
--------------------------------------------------------------------------
--------------------------------------------------------------------------
For each server run the script and insert data into a centralized table
--------------------------------------------------------------------------
Step 6: The table CentralTableForBackupInfo will look something like this:
After executing the last Stored Procedure (p_SelectAllServersLastDatabaseBackup), the table CentralTableForBackupInfo will look something like this:
InstanceName
DaysFromLastBackup
Backup Type
BackupSizeKB
DatabaseName
(local)
104
D
12664832
My_database1
(local)
3
L
84480
My_database1
(local)
105
D
2705920
Northwind
(local)
70
L
12345
master
(local)
0
NULL
NULL
msdb
(local)
50
D
11397120
My_database2
(local)
49
D
11261440
My_database3
Remote1
0
D
12664899
My_database1
Remote1
0
L
84420
My_database1
Remote1
13
D
98665
My_database6
Remote1
0
D
1232
master
Conclusion
It is a good practice to verify on a regular basis that you have all the backups to avoid unnecessary problems in the future. I know that running this small utility has saved me from disasters many times.
About the author: Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an information technology consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. She She has worked exclusively with SQL Server for the last 10 years, consulting for a diverse group of clients. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
More from SearchSQLServer.com
Top five: Check out our top five T-SQL stored procedures
Tips: View the complete collection of stored procedures
Glossary: SQL Server slang stumping you? Look it up in our glossary
Updates made to the above tip
I got the following comments from the reader Steve H. (thank you Steve!), and I've decided to make some changes to my initial document. The remarks were:
"1. You may want to include Type = 'I' to include Differentials if they are being used.
2. I had to fight the query string being passed to #tmp2 to strip out spaces because I was getting an error, Input Query String too long. But once I got that figured out, it ran fine.
3. I saw was that this job doesn't clean up old data, so every time it runs it just adds another set of rows for the server. You may want to mention that you'll want to truncate the CentralTableForBackupInfo at the beginning of each run.
4. I don't use the OSQL –L much. It is too client dependent for my tastes. We generally use a Configuration Management Database that has all of the servers under our control. We have about 1,200, so obviously, I'm not setting them all up in EM or the Client Network Utility."
So I changed the original SP to select the max value for each type (solving the First mentioned problem):
I also changed the second stored procedure accordingly:
a. Changed the basic query as above (this will also solve the problem of the error that occurs due to "Input Query String too long" in #tmp2).
b. Added an input parameter to get number of days for keeping old data in the CentralTableForBackupInfo table (default = 0 – don't delete). In order to be able to delete the old data, I had to add a Date column to the table and an index – for performance:
So here it is:
-----------------------------------------------------------------------------------------------
Fetch all SQL SERVER known instances with OSQL -L
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
If the current server is not connected to the network, the string "-- NONE --"
will be the result from OSQL -L. There is no meaning for this script to run if
there are no other servers.
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
For each server - run the script and insert data into a centralized table
-----------------------------------------------------------------------------------------------------
If you manage your instances name in a central table, this is even better, so just take out the #tmp table and use yours instead. The SP will then look like that:
-----------------------------------------------------------------------------------------------
For each server - run the script and insert data into a centralized table
-----------------------------------------------------------------------------------------------------
