Microsoft technologies and what I do for fun: January, 2007

Edwin Daniel's profileMicrosoft technologies a...BlogLists

<< First < Previous Next > Last >>

January 24

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 newly installed 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.

Notice that I filed this under the category SQL Server General and not Windows Administration. This is a generic approach which can be used for any database running on MSDE or SQL Server, whether it's WSUS or not.

January 23

WSUS database instance - only works on the default setting

Setting up WSUS 2.0 on a Windows 2000 Advanced Server in preparation for my WSUS 3.0 Beta 2 testing. I happened to be very comfortable with SQL Server/MSDE so I really don't use any default settings. I installed MSDE with WSUS20 (the default is WSUS) as it's instance name, hoping that I can use a non-default instance name for WSUS. The command-line setup can be used to choose non-default settings like a non-default MSDE instance name. Using the /d switch to use specify a parameter for a SQL Server instance and /v switch to pass parameters in the setup process. My command-line option for setup looked like this:

WSUSSetup.exe /d /v "INSTANCE_NAME=WSUS20"

To my surprise, it prompts me for a "database server not found" error. I tried for a couple of times to repeat the process with no luck. Just to make things simple, I just reinstalled MSDE with a default setting for the named instance used (instead of WSUS20) and reinstalled WSUS. Installation went smoothly as expected.

12:28 PM | Add a comment | Permalink | Blog it | Windows administration

January 22

Joining Windows Vista to an Windows SBS 2003-domain?

If you are planning to join a Windows Vista machine to a Windows SBS 2003 domain, the usual client deployment tools in Windows SBS will not work. Normally, you just go to the http://<servername>/ConnectComputer site to join a client machine to the domain. You should see an error when you try to do this

The Small Business Server Networking Wizard was not installed. You may not be a member of the local Administrators security group on this computer or your Local Intranet security settings may be set to High. Click Connect to the network now to try again and click Yes when prompted. If the wizard fails to install, contact the person responsible for your network

Microsoft released a KB article on configuring a Windows Vista machine to join a Windows SBS 2003 domain. Until a patch is released, it is recommended to manually join the Windows Vista machine based on the procedures outlined in this article.

January 18

SQL Server Compact Edition - the rebranding

A few months ago, I was working on SQL Server 2005 Mobile Edition to create Windows Mobile Line-of-business applications. Since this was originally SQL Server 2005 Everywhere Edition, I know for a fact that the name will change in the long run. At the Professional Association for SQL Server (PASS) Community Summit held last November 2006, a formal announcement was made regarding SQL Server Compact Edition. A description of SQL Server 2005 Compact edition is available from the Microsoft website

5:37 PM | Add a comment | Permalink | Blog it | SQL Server 2005