Introduction
A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since I think that most tools for database tools that ship with databases (like Enterprise Manager in Microsoft SQL Server) are a bit too big for creating simple backup and restoring it with ease.
As the thought evolved, along came Visual Studio 2005 and SQL Server 2005 and especially the Express version that is available for free. I have to admit: I was quite blown off the first time I saw the way SQL Server 2005 Express handled databases: database files lying around folders without any kind of consistency. OK, the good point is that they are easily related to projects and are portable more easily than with centralized versions of SQL Server but managing them would be awful. Luckily, I use the Express version only for developing, and with compatibility, I can upgrade stuff in case needed.
Okay, back to the issue: why I thought this tool could be cool for developers is that sometimes we (all?) come across situations where we have an application that handles data in the underlying database but the data is wrong and we need to get a backup from somewhere to restore, and then we get going or worse yet: we have to programmatically or by hand "generate" the data in the database.
Simple examples could be a situation where you do unit testing with the data access layer objects and you want some actual data to exist in the database. Maybe not too much to keep the testing quite fast but still something to get datasets filled and data processed - this would be an excellent place to restore a snapshot of a database that is labeled "For Unit Testing". Another situation could be reporting that should be tested to handle very large datasets and another snapshot is needed including large data amounts. Also a third snapshot might be required for actual production data copy (with limitations or modifications) for final testing of the application. More snapshots might be needed for testing data handling if a third party (webservice or some other application) modifies the data without contracting with your application and scrambles the data in the database - how does your application handle such cases?
DevShot is an application that does the simple tasks for backing up/restoring databases into/from files and provides a simple API for doing this. I know that SQL Server's DMO and SMO APIs are very good, but for this kind of a functionality, are filled with options that are not needed like tape backups, more fine tuning transaction log backups etc.
How I've arranged this is as a simple framework (kind of) that includes a DBAdapter abstract class that has a few, quite self explanatory methods and properties for backing up and restoring data. The provided solution also includes a simple WinForms application including a sample database with one table and functionality samples for backing up and restoring data. Also is provided a few ready snapshots with a few rows of data (actually snapshot 4 is an empty table).
As in the WinForms application, we can see that, the actual thing to do (with SQL Server 2005 Express) is quite easy. The following lines are all that are needed:
Dim dsfw As New DevShot.Framework
Dim dba1 As New DevShot.MSSQL2005Adapter
dba1.ConnectionString = My.Settings.TestDatabaseConnectionString
dba1.DatabaseName = Application.StartupPath & "\TestDatabase.MDF"
dba1.BackupFile = Application.StartupPath & _
"\TestDatabase_State_" & _
Me.ComboBox1.SelectedItem & ".BAK"
dsfw.DBAdaptersCollection.Add(dba1)
dsfw.Backup()
The dsfw.Backup() could be also Restore() since the methods don't need anything else for doing the trick. What the code above actually does is that it initiates a simple collection of DBAdapter objects (the base class for MSSQL2005Adapter), and adds an object of the MSSQL2005Adapter to the DBAdaptersCollection which is a collection in the framework containing all the items to be backed up.
Note that the DatabaseName property is provided as a file name since the database is to be attached at runtime. If this should be some online database, like Northwind, you should use the name "Northwind", not the file path. Also, this path refers to the startup directory where the application is started. This is for debugging purposes since Visual Studio actually copies the created database during the build to the "bin\Debug" (and "bin\Release") subdirectories under the application's root directory. This was first quite confusing since the Server Explorer in Visual Studio modifies the database in the root directory of the application and then copies the modified database to the bin\Debug folder, and in case the software does any changes to the database, all the changes are lost with the new copy of the database when the application is rerun (actually this is one more reason why I started screaming for this kind of a tool).
Another cool point is the way the connection strings that are built with Visual Studio can be used with the SMO.Server object in the ConnectionContext.ConnectionString property of the Server object. The only thing with the Server object is that while constructing the object, it needs to connect to some SQL Server database available. So even if you are connecting to an external server with your connection string, you want to provide a server name to the Backup() and Restore() methods (optional) - the default value is ".\SQLEXPRESS", that connects to your machine's SQL Server, if available. For instance, the following connection string connects to the database while connecting to the server:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|
\TestDatabase.mdf;Integrated Security=True;User Instance=True
Now, when running this sample, you should remember that this is still a work in progress and updates are to follow. However, I appreciate your feedback - could you find this kind of application useful? You might have some problems connecting to SQL Server 2005 Standard, Workgroup or Enterprise but this will be fixed when I can get a decent server to put an instance of SQL Server 2005 running.
Also, more adapters are probably about to come like a File Adapter (for file copying), Oracle, DB2, MySQL, PostgreSQL just to mention some. Also, downgrading would be great to create an MS SQL Server 2000 Adapter to use the DMO COM+ objects to backup/restore data in SQL Server 2000 databases (or MSDE, for that matters).
