This next post is going to be in three parts, because it touches on three different areas within SMO.
In my production environment I have maintenance plans set up to backup the databases every night, and backup the transaction logs every hour. That handles most of the transaction load so I'm fairly safe from data loss. There are times, though, when a high amount of activity will cause the transaction log to expand dramatically between hourly backups, so I set up an alert to watch for Percent Log Used value for each database and, if it exceeds 50 percent, automatically kick off a transaction log backup for that database.
This is handled through a stored procedure, which is passed the name of the database to be backed up, a SQL Server Agent job, which is triggered by a Performance Condition Alert. In this post I'll describe and share the code that creates the stored procedure which performs the backup.
The first thing we'll do is connect to the server, and grab the location of the backup directory from the Server.Settings object.
Dim strBackupDir As String
Dim dbDatabase As Database
' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True
strBackupDir = srvMgmtServer.Settings.BackupDirectory
Next, we need to attach to a database and build the stored procedure framework. I've chosen to place the procedure into the msdb database because 1) it's not the master database, and 2) because I know it exists on every instance of SQL Server. I haven't had any ill effects of this decision, but please leave me comments if this would be considered outside "best practices".
Once I've connected to the database I create a StoredProcedure object and name it 'db_log_dump'. I'm showing my Sybase roots here, because that's what we called them pre-SQL Server 7. We also need to add a parameter to the stored procedure for the name of the database to be backed up.
Dim spStoredProc As StoredProcedure
Dim prmDBName As StoredProcedureParameter
Dim strSPText As String
dbDatabase = srvMgmtServer.Databases("msdb")
spStoredProc = dbDatabase.StoredProcedures("db_log_dump")
If Not (spStoredProc Is Nothing) Then
spStoredProc.Drop()
End If
spStoredProc = New StoredProcedure(dbDatabase, "db_log_dump")
spStoredProc.TextMode = False
spStoredProc.AnsiNullsStatus = False
spStoredProc.QuotedIdentifierStatus = False
prmDBName = New StoredProcedureParameter(spStoredProc, "@database", DataType.VarChar(50))
spStoredProc.Parameters.Add(prmDBName)
Note that we check to see if the db_log_dump stored procedure already exists, and drop it if it does, just to make sure we don't run into an error.
Lastly, we build the text of the stored procedure. I've declared two variables, one for the backup device name, and the other for the string holding the date and time value to be part of the log backup file name. The date value will hold the date and time (to the second) that the backup was initiated. Once the date string is built, then it's concatenated to the backup directory, the database name and the '_tlog_' designator to let me know it's a log backup. Tack the '.TRN' extension on and we've got the full pathname of the backup file to pass to the BACKUP command.
strSPText = "declare @strbackup varchar(500), @strDate as varchar(30)" + vbCrLf
strSPText = strSPText + "set @strDate = CONVERT(varchar, getdate() , 112)" + vbCrLf
strSPText = strSPText + "set @strDate = @strDate + Left(CONVERT(varchar, getdate() , 108),2)" + vbCrLf
strSPText = strSPText + "set @strDate = @strDate + SubString(CONVERT(varchar, getdate() , 108),4,2)" + vbCrLf
strSPText = strSPText + "set @strDate = @strDate + SubString(CONVERT(varchar, getdate() , 108),7,2)" + vbCrLf
strSPText = strSPText + "set @strbackup = '" + strBackupDir + "' + '\' + @database + '_tlog_' + @strDate + '.TRN'" + vbCrLf
strSPText = strSPText + "BACKUP log @database to disk = @strbackup" + vbCrLf
spStoredProc.TextBody = strSPText
spStoredProc.Create()
At this point the stored procedure exists in msdb, to be called by the SQL Agent job we'll create in my next post.
Allen