Backed Up Databases
In a development environment the number of SQL servers can become quite extensive. If a production environment exists within the same infrastructure as the development environment keeping everyone informed about the state of backups can become quite difficult. In addition, backing up all the data can require a lot of space. In some instances the data may be a duplicate of other databases that are backed up in other places.
In order to efficiently backup data and maintain the ability to recover from disasters it is important to keep track of not only the SQL server but also the databases contained on each server. The ability then for the users in the enviroment to keep track of which data is backed up is imperative.
The following script queries the maintenance plans on a server to find which databases are backed up. The script uses a VBscript class and could easily be ported to an ASP page to keep an up-to-date record of when a database was last backed up. This script simply pulls the information about the databases currently in the maintenance plans. In part two and three of this series this information will be pulled together with other scripts to develop an ability to poll the servers in an environment and determing the backups occuring on those servers.
There are some assumptions made concerning how the backups will be performed. The backups should be set up using a maintenance plan. If a maintenance plan is used it is then possible to query the MSDB database to find out which databases are backed up. The table that holds this information is sysdbmaintplan_history. The nice thing is this table holds all the information for all the maintenance plans. If a number of seperate maintenance plans are setup it is possible, programmatically, to search for only the desired information.
The first step in programmatically getting the information back will be to find out which databases are currently being backed up using the various maintence plans on the server. An example of how this can be done using a class is demonstrated below.
The following is all the code that is needed, when the class module is used, to get a list of databases backed up via the various maintenance plans. The code for this article can be found here.
strDBList = strDBList & " Database " set objDBList = new clsServerBackupInfoList objdblist.SQLServer = "MyServer" if not objDBList.EOF then do while not objDBList.EOF strDBList = strDBList & objDBList.Database & vbCRLF objDBList.MoveNext loop end if set objDBList = nothing msgbox strDBList
The code above does the following. It instantiates the class. Once this is done the desired server is assigned to the server property of the object. It is then possible to cycle through the databases using the MoveNext method and list the servers by getting the database property. In this case each database is appended to a string and the string displays the list of databases that have backups in the maintenance plan using a message box.
It should be noted that this table is historical so unless it is cleaned out there could be databases listed that are no longer on the server or have been removed from the backup plans. For this reason it will be important to use another routine to extract the dates of the last backup.
The following code shows the class and the properties associated with the class. Below an explanation of the code will explain how it works and elaborate on key points.
1. Class clsServerBackupInfoList 2. 3. private strSQLServer 4. private strDataBase 5. private objCon 6. private SQL2 7. private RS1 8. private str 9. private fd 10. private ConnectionString 11. private boolEOF 12. 13. Private Sub Class_Initialize() 14. boolEOF = true 15. End Sub 16. 17. Private Sub Class_Terminate() 18. if RS1.State <> 0 then 19. RS1.close 20. Set RS1 = Nothing 21. end if 22. Call closeConnection 23. End Sub 24. 25. Public Property Let SQLServer ( byVal tmpSQLServer ) 26. strSQLServer = tmpSQLServer 27. if len(strSQLServer) > 0 then 28. Set objCon = WScript.CreateObject("ADODB.Connection") 29. 30. ConnectionString= "Provider=sqloledb;" 31. ConnectionString= ConnectionString&"Data Source=" & strSQLServer & ";" 32. ConnectionString= ConnectionString& "Initial Catalog=MSDB;" 33. ConnectionString= ConnectionString& "Integrated Security=SSPI;" 34. 35. objCon.Open ConnectionString 36. SQL2 = SQL2 & "SELECT distinct database_name FROM sysdbmaintplan_history " 37. SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ")" 38. 39. Set RS1 = objCon.Execute(SQL2) 40. 41. if not RS1.eof then 42. strDatabase = RS1("database_name") 43. boolEOF = False 44. else 45. boolEOF = True 46. end if 47. end if 48. End Property 49. 50. Public Property Let Database ( byVal tmpDatabase ) 51. strDatabase = tmpDatabase 52. End Property 53. 54. Public Property Get SQLServer 55. SQLServer = strSQLServer 56. End Property 57. 58. Public Property Get Database 59. Database = strDatabase 60. End Property 61. 62. Public Property Get EOF 63. EOF = boolEOF 64. End Property 65. 66. Public Sub MoveNext 67. RS1.MoveNext 68. if not RS1.EOF then 69. strDatabase = RS1("Database_name") 70. boolEOF = false 71. else 72. boolEOF = True 73. end if 74. end sub 75. 76. Private Sub closeConnection() 77. objCon.Close 78. End Sub 79. 80. End Class
Line 1 defines the class and lines 3 - 11 set up the private variables needed. In the initialize subroutine the end of file (EOF) property is set to true so when the object is instantiated the user knows no records exist. Lines 17 - 23 set up the termination subroutine which is called when the object is destroyed. This routine cleans up any of the connections and recordsets used by the class.
The first of the two let statements in this routine is SQLServer. The SQLServer property allows one to set the server to be queried. Once the server is set it is verified to be set (line 27) and then a connection object is created(line 28). The connection string for the object is put together on lines 30 - 33 and the connection is open on line 35. This class uses integrated security. If one wanted to use SQL security the connection string would have to be modified to include a username and password.
In order to query the database a SQL statement is needed. This statement is constructed on lines 36 and 37. This SQL statement could be used in Query Analyzer to show the information that the class will bring back. The recordset is retrieved on line 39 but it is lines 41 - 46 that check to make sure the database property is set correctly. Line 41 checks to make sure at least one record was brought back, if not, the EOF property is set to true. Lines 50-52 set the database property for input although it is not used here. The other property gets are set on lines 54-64. These are the properties the user is able to access. The last supporting method is setup on lines 66-74. This method is needed in order to cycle through the databases on the server. The final routine on lines 76-78 is used to close the connection to the database and is called in the terminate routine.
A class is presented that can be used to query the maintenance plans on a server for the database backups. This class can be used in conjunction with others to help create a web page displaying databse backup information. In the next few articles these classes will be presented. These classes could be further coupled to SQL-DMO in order to automatially query the SQL servers in an environment and gather the backup information.