Showing posts with label sql server. Show all posts
Showing posts with label sql server. Show all posts

Monday, February 11, 2008

Moving SQL Server Database Content

This old server was running low on disk space on the System volume. Needed to find a way to move the data files onto another disk volume without dealing with a database reinstallation and reconfiguring all the applications depending on it. I found out how to do it for MS SQL Server Desktop Engine (comes free with the free Windows Sharepoint Services) and MS SQL Enterprise

MS SSDE
From a command prompt, type

> Osql -S servername\instancename -E

where servername is your server's name and instancename is the database instance such as "sharepoint" (without the quotes).
Next, In order to 'detach' a database physical file, you have to disconnect the database from the database engine. Make sure the database is not in use. At the client prompt, type

1> EXEC sp_detach_db "databasename"
2> Go

Now, move the physical files to the new location. Move both the databasename.MDF and databasename_log.LDF files. Now to reattach the physical file to the database engine, and make the database available again, at the same client prompt, type

1> EXEC sp_attach_db 'databasename','C:\path\to\databasename.MDF','C:\path\to\databasename_log.LDF'
2> Go

That's it. Restart your database engine if necessary.

The sp_attach_db syntax is slightly different for the Enterprise Server version.