DATABASE DEVELOPMENT
Stored procedure: Determine last database backup
Michelle Gutzait, Contributor
04.06.2006
Rating: -4.40- (out of 5)
|
[TABLE]
[TABLE]
[TABLE]
[TABLE]
Note that the last database was never backed up!
[TABLE]
[TABLE] [TABLE] 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:
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:
Wit
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
h 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:
[TABLE] Conclusion
[TABLE] 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
-----------------------------------------------------------------------------------------------------
|
