Detach and reattach

So you need to move a SQL database ‘eh?


First off the trick is to “get” to that database with a management tool


 http://msmvps.com/blogs/bradley/archive/2007/06/12/connecting-to-the-wsus-database-with-sql-2005-s-tools.aspx


If you don’t have the full blown SQL tools, download this one:


http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en


Then you can use a command line (yes, you heard me, command line), right mouse click on that name of the database and run query to detach and attach like so.. so what’s the database name for WSUS you ask?


Here’s what I found looking at mine… I had several… (this is the view from the SQL 2005 management tools but the concept is the same)


 Those sharepoint databases are there from a WSS 3.0 side by side install that I need to kick over to SQL 2005 one of these days.



And in doing a command on the wss_content that one got stuck on my C: drive 



See that “New Query” button?  click there. Once you’ve built the command argument you then click “Execute” to run the command.



So last night I got into a situation in the blog server where the database and the log files grew so much that we were nearly running out of room and thanks to the collective intelligence of Henry, Edwin and Brian I did a query command to move the databases to a new location… and for WSUS the same rules apply.


You detach.


You copy (or cut and paste the database files)


You reattach.


http://support.microsoft.com/kb/224071



1. Detach the database as follows:
use master
   go
   sp_detach_db ‘thenameofyourdatabase’
   go
2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3. Re-attach the database. Point to the files in the new location as follows:
use master
  go
  sp_attach_db ‘thenameofyourdatabase’,’E:\Sqldata\thenameofyourdatabase.mdf’,’E:\Sqldata\thenameofyourdatabase.ldf’
  go
Verify the change in file locations by using the sp_helpfile stored procedure:
use thenameofyourdatabase
   go
   sp_helpfile
   go

Comments are closed.

Post Navigation