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.

Undocumented extended stored procedure - xp_fileexist

This post was originally posted ni this blog entry

It's quite shocking to hear the word "undocumented" in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won't find anything. But there's always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I'm limited to what I just have) when I chanced upon this extended stored procedure - xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure - xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:

xp_fileexist 'C:\autoexec.bat'

This will return three columns indicating if the file exists, if the file is a directory and if the file's parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below

DECLARE @result INT
EXEC xp_fileexist 'c:\autoexec.bat', @result OUTPUT
IF (@result = 1) --@result will return 1 if the specified file exists, if it doesn't, it will return 0
PRINT 'file exists'
ELSE
PRINT 'file does NOT exist'

This is also helpful when you want to check for the existence of a database backup file as it has been recorded in the msdb database. One of the challenges I have is checking if a database transaction log backup still exists if a log shipping job starts failing.

Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn't have any after all) so make sure to test your scripts after applying service packs or upgrading.

Changing VMWare image settings

And so I found out.

I was deploying an image to a server and wanted to try out VMWare Player instead. I am using version 2.0.3. Since the server has 4 processors, I was thinking of configuring the image to use as much processors as are available. Now there are two ways to do this. First, you can use VMWare Workstation or Server to change the configuration of the VMX file. You can use the graphical user interface for this. In my case, since my laptop does not have multi-processors, I did encounter an error stating that the image will not start if I use more than one CPU. But it doesn't matter since I am not going to start the image on my laptop anyway. I just need to change the settings so I can use those settings on a different machine.

Second, similar to how you do it in Microsoft Virtual PC where you change the VMC file, you can change the VMX file by searching for the numvcpus field and changing the value of the number, let's say from 1 to 2.

Oh, and there's one more thing: VMWare Player does not support more than 2 processors. And so I found out

Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files

I've compiled a couple of my scripts - both TSQL and VBScript - to create a tip for MSSQLTips.com on automating backups and deletion of SQL Server 2005 Express databases to mimic that of creating a database maintenance plan. You can check out the article for more details. Feel free to post comments on the MSSQLTips.com forum site and hopefully, you'll find the tip very useful. I'll write a few more tips for them in the long run just for the fun of it.