With SQL Server 2005 up and running, there are some things you can do to make life easier as a developer -- without having to become a database administrator. One of the most common pains in the rear end is change management. While most developers are familiar with Microsoft Source Safe or other source control technologies, database developers have had a very limited set of tools to help make change management easier. SQL Server 2005 includes Visual Source Safe integration. I won't go into Source Safe integration in this article. I'll focus instead on techniques and technologies native to SQL Server that help you move the project along. In this article I will answer two big questions:
- How do I roll back a change made by mistake?
- How do I move the changes from one database to another?
Realize at development time that you could make a mistake, and before each change make a copy of the database. You might find yourself with hundreds of copies of the database -- I've seen this happen. It could become unwieldy. If your approach is more like mine, you'll want to work in one development database and simply find a way to manage mistakes. There are two general approaches to handling mistakes: Database backup and Database Snapshot. The first approach I'll discuss is the database backup. (Note: You can perform each technique using the SQL Server Management Studio -- but because we're developers, we'll do these tasks in Transact-SQL.)
First, let's create a full database backup.
BACKUP DATABASE [AdventureWorksDW] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorksDW.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
In this example, I have backed up the AdventureWorks DW sample database to the backup folder for the SQL Server default instance. The backup stands as our most reliable eraser. If you don't want to do backups constantly, try the new database feature called Database Snapshot. Database Snapshot is the second approach. It allows us to take a point in time picture of the database. As changes are made to the principal database (or source database), the snapshot database logs the state of the principal before the change by making a copy of the database. Essentially, the database snapshot uses a copy on write technology that moves over changed pages. Thus, you could use a snapshot for recovery. To create a database snapshot, I execute the following T-SQL:
CREATE DATABASE AdventureWorks_DWBEFORECHANGE ON ( NAME = AdventureWorksDW_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_DW.ss'AS SNAPSHOT OF AdventureWorksDW;
The tricky part here is knowing the name of the data file that is supplied to the NAME clause. You can find the database data file name by looking at the files property in the database Object Explorer in SQL Server Management Studio (yes, we cheated).
Now let's say we make some changes, and then, Oh-Man! We need to roll them back! Here's the magic:
RESTORE DATABASE [AdventureWorksDW] FROM DATABASE_SNAPSHOT = 'AdventureWorks_DWBEFORECHANGE';
It's cool, but it has some limitations. After you recover using the snapshot, you should do a full database backup -- just redo the script above. SQL Server rebuilds the log file, so executing a complete backup will make it right again. Also, since database snapshots don't capture everything about a database, just the parts that have changed, you may need to re-enter any test data using the full backup we created above or by scripting the data. In many cases, we keep the data in a separate insert data script, so we can reproduce it in a hurry.
I like to use a tool called EMS DB Extract. It's a great tool for generating scripts for database creating and data insertion. SQL Server does provide the Database Import and Export Wizard,
but I found it to be limited, and it forced me to use a wizard, which meant changing my modus operandi. If you do choose to use the Database Import and Export Wizard, you'll notice that it creates and executes a SQL Server Integration Services (SSIS) package. This can be very handy, or not. The database Import and Export Wizard can use a database destination or several kinds of file formats. Choosing a database destination would mean creating a new database each time you run it or overwriting a current database. I prefer to keep my server tidy, so one copy of the database is enough -- I just overwrite the old backup.
We now have three ways to manage database change and manage errors:
- Use a database backup to protect the principal work database -- this is the most reliable.
- Use a database snapshot to undo errors to the database -- this is a limited but viable approach.
- Use the Database Import and Export Wizard to push data and the schema to a backup database -- this works for creating secondary copies and for doing transfers of data and tables.
With our development process complete, we'll want to move the test database into production. In the real world, rarely are production databases ripped out and replaced.
In the previous section of the article, we used a full backup to handle error removal. Backups are also handy for moving databases from development/test to production. The use case for this method is if the development database starts as a copy of the production database -- with development introducing new database objects and possibly even data. Most of the successes using this technique come from carefully documenting the differences between the databases and making sure nothing breaks. If we wanted to restore the development database as the production database, we would execute the following:
RESTORE DATABASE [AdventureWorksDW] = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorksDW.bak' WITH FILE = 2, NOUNLOAD, REPLACE, STATS = 10
In typical production environments, using a full backup to update the production database is tricky. And, if you do that during working hours, it will cause a database blackout. Plan your updates carefully.
If your changes are schema related, meaning adding columns and tables -- as most are -- you can use Alter commands in T-SQL. That requires slow and careful work, but it's a smoother process while the server is online. Additionally, be sure you or the database administrator has made a database backup before you proceed. Again, you might consider using Database Snapshot.
Most catastrophic breakdowns in database development are from a lack of guided and careful development practices. This article sheds a little light on how to use SQL Server features to handle errors and development tasks for database object development. To help bring you up to speed, take a look at Microsoft Visual Studio Team Addition for Database Professionals
Your mileage may vary….
ABOUT THE AUTHOR: Eric Brown is a senior consultant in the Business Intelligence national practice for Quilogy Inc. He works on emerging technologies and SQL Server 2005 and is the author of SQL Server 2005 Distilled (Microsoft Windows Server)." Brown's career began in 1996 when he started at Multiple Zones International as a product manager. While there he realized the next big wave would be e-commerce and he raced to get a job at a dot-com. After working for three such companies, his acumen for databases and passion for technology landed him on the SQL Server Product Team at Microsoft, during which he ran Yukon readiness. Eventually, Brown took a sabbatical and pursued other interests, like getting back to his e-commerce roots and writing a book about SQL Server 2005. He has presented at Microsoft TechEd and has written several papers as well as a column in SQL Server Magazine.
Copyright 2006 TechTarget
How to write and execute scripts for creating a database