I keep finding that I need to mix Transact-SQL with my SMO code to get things done. I have a server where we keep databases for a lot of different web sites. The application uses database data to configure and present information on each web site, dependent on design data in the database, so using one application we can host many very different web sites. When the application changes I have to change all of the databases with the same changes, which prompted yesterday's blog post.
Problems occur with these sites on occasion when the people who design the sites inadvertently delete or significantly modify the site data. I get a call asking me to restore the data to its previous state. Prior to upgrading to SQL 2005 I always restored the database to a separate database and allowed the users to copy back the data they'd lost to the production database, bringing them back to the point they were before making their mistake.
SQL Server 2005 has a great new feature called Database Snapshot, which allows me to make a point-in-time image of the database, accessible as a read-only database, as it existed when the snapshot was taken. It uses NTFS magic to keep the actual disk space at a minimum, only taking space for those pages in the database that have actually changed in the base database since the snapshot was taken. I decided to take advantage of this feature to allow the users to get their data back without my having to perform a restore.
Architecturally, I decided that it'd be best to have a week's worth of these snapshots available, in the event the user realized that they deleted the data on Friday that they now critically need on Monday. Based on this decision I appended the string "_snapshot_" and the first three letters of the day of the week to the end of the database name.
Because I like SMO to automate processes, I created a console app to build the snapshots. First, I need to drop the databases from last week's job:
Dim lisDBs As DatabaseCollection
lisDBs = srvMgmtServer.Databases
Dim objDB As Database
strSQL = ""
For Each objDB In lisDBs ' Drop the snapshots with today's day name
If Right(objDB.Name, 13) = "_snapshot_" + strWeekDay Then
strSQL = strSQL + "DROP DATABASE " + objDB.Name + "; "
End If
Next
objDB = srvMgmtServer.Databases("master")
objDB.ExecuteNonQuery(strSQL)
I'd first attempted to use the SMO Drop() method, but kept getting errors because I'd changed the collection while looping through it. (I'm mostly a DBA, and there's probably a way to use the Drop() method, but this accomplishes the task at hand.)
The Database object in SMO has read-only properties to tell you if a given database is a snapshot or not, but I didn't find anything to allow me to set the properties before creating the database so I returned to Transact-SQL to create the database. Because snapshots are based on the file system features you need to specify each file used by the database (not the log, because the snapshot is read-only), so you need to loop through the FileGroups collection, evaluate each Filegroup to find the DataFileCollection, then loop through that to find each data file. The DataFile.Name property contains the logical name, while the DataFile.FileName property contains the physical file name. I lop off the extension from the base database filename, add the day of the week, then the extension ".snap" so anyone browsing the file system can know what the file is.
For Each objDB In lisDBs ' Loop through the databases on the server
If Left(objDB.Name, 4) = "xxx_" Or Left(objDB.Name, 4) = "yyy_" Then
' Create a snapshot of the database
Dim objFileGroups As FileGroupCollection
Dim objFileGroup As FileGroup
Dim intFileCtr As Integer
Dim strSnapDBName As String
strSnapDBName = objDB.Name + "_snapshot_" + strWeekDay
If Not objDB.IsDatabaseSnapshot Then
strSQL = "CREATE DATABASE " + strSnapDBName + " ON "
intFileCtr = 0
objFileGroups = objDB.FileGroups
For Each objFileGroup In objFileGroups
Dim objDataFiles As DataFileCollection
Dim objDataFile As DataFile
objDataFiles = objFileGroup.Files
For Each objDataFile In objDataFiles
Dim strFileName As String
strFileName = Left(objDataFile.FileName, Len(objDataFile.FileName) - 4)
If intFileCtr > 0 Then
strSQL = strSQL + ", "
End If
intFileCtr += 1
strSQL = strSQL + "( NAME = " + objDataFile.Name + ", FILENAME = '" + strFileName + strWeekDay + ".snap')"
Next
Next
strSQL = strSQL + " AS SNAPSHOT OF " + objDB.Name
objDB.ExecuteNonQuery(strSQL)
End If
End If
Next
The net of this code is that for each base database it creates Transact-SQL that looks like this:
CREATE DATABASE xxx_sales_snapshot_Wed ON
( NAME = SPri1_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SPri1datWed.snap'),
( NAME = SPri2_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SPri2dtWed.snap'),
( NAME = SGrp1Fi1_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG1Fi1dtWed.snap'),
( NAME = SGrp1Fi2_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG1Fi2dtWed.snap'),
( NAME = SGrp2Fi1_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG2Fi1dtWed.snap'),
( NAME = SGrp2Fi2_dat, FILENAME =
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG2Fi2dtWed.snap')
AS SNAPSHOT OF xxx_sales
Once it creates the SQL script it uses the ExecuteNonQuery() method to create the snapshot. I'm not sure how elegant this solution is, but it certainly works and should make my user community happier.
Allen