Encrypting your database backups in SQL Server 2008

I was asked a question about how you make sure that your SQL Server database backups are secured and I ended up saying, "go get a third-party tool like LiteSpeed for SQL Server from Quest Software." While it is true that third-party tools can provide additional layer of security on your database backups, budget is always a constraint. Good thing SQL Server 2008 has included a feature called transparent data encryption which you can use to secure your database backups. I have a written an article on MSSQLTips.com and created a video at BlogCastRepository.com about this feature which is worth having a look at.

Moving the WSUS database to a new location

I woke up from a call from our support engineers telling me that a drive has less than 10% free space and needs to be maintained. This drive happens to be hosting my WSUS server together with the database used in the backend. There's only one thing for me to do: move the database to a new location. This approach works for any SQL Server/MSDE database you want to move to a different location.

Step 1: Stop any service that is accessing the database

Since this is a WSUS database, we need to stop several services like the Update Service and the WWW service. You can do so using the Services applet or by using the NET STOP command

Step 2: Detach the database using the sp_detach_db command

Since we do not have Enterprise Manager by default in using MSDE, we will stick to our command-line tool, osql. The sp_detach_db command detaches the specified database from SQL Server/MSDE. If you didn't stop any service or application accessing this database, this command will fail. Below is the syntax for the sp_detach_db command(see MSDN as well)

sp_detach_db [ @dbname= ] 'database_name'
[ , [ @skipchecks= ] 'skipchecks' ]

To use the sp_detach_db in osql for the SUSDB database, execute this in the command line

osql -E -S %computername%\wsus -Q "exec sp_detach_db 'SUSDB'"

I am more comfortable with executing TSQL scripts while logged in that's why I make it a point to secure a logged in connection first before I execute them.

Step 3: Move the database files to a new location

Now that you have disconnected the database from the server, you can now treat it as any other file in your file system. Move the SUSDB.mdf and SUSDB_Log.LDF files (for any database, be sure you know which files correspond to which database by executing the sp_helpdb command)

Step 4: Re-attach the database using the sp_attach_db command

Execute the sp_attach_db in osql to re-attach the database files you have moved to a new location.

osql -E -S %computername%\wsus -Q "exec sp_attach_db @dbname=N'SUSDB', @filename1=N'E:\WSUS\MSSQL$SUS\Data\SUSDB.mdf', @filename2=N'E:\WSUS\MSSQL$SUS\Data\SUSDB_log.ldf'"

Step 5: Start the services which you stopped in Step 1

Resume the services or applications you stopped. Check whether the application still works fine and that it can still access the database.

This is a generic approach which can be used for any database running on MSDE or SQL Server, whether it's WSUS or not. Note that if you are dealing with WSUS 3.0 which, by default, uses the Windows Internal Database (SQL Server 2005 Embedded Edition), check out this blog post to connect to this instance.