Nuno Silva

Change a Sql2005 Database…

…from place. As simple as 1,2,3.

1. Detach it

Make the following query in Management Studio:

use master
go
sp_detach_db 'DatabaseName'
go

2. Copy the files

Go to the SQL server directory where the databases are saved, usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and copy the .mdf and the .ldf file of the database that you want to change to a new location.

3. Attach the Database From the new location

Run the following query:

go
sp_attach_db 'DatabaseName','$LocationFileMdf$','$LocationFileLdf$'
go

where DatabaseName is the name of the database in the Sql Management Studio, $LocationFileMdf$ is the new full path of the mdf file and $LocationFileLdf$ the new full path of the ldf file.

Just to guaranty that the operation was successfull, run the query:

use $DatabaseName$
sp_helpfile

and see if the location of the .ldf and .mdf files is correct.

This operation is particularly useful when you have 2 disks and you need to free some space in the one where you have the database stored.

Random Posts

• Download Speed
• Google Search Appliance
• How do you feel today?
• Call center
• Fascinating!