Transfer sql server install to different drive

[f0rsaken4ngel]

Hypothetical:

rackmount machine with 2 drives. currently, they are just 2 drives, each with 1 ntfs partition. There is a SQL server install on the D: (2nd) drive and windows is installed on the C: (1st) drive. The database is on the 1st drive.

My final goal being to mirror the 1st drive onto the second drive, how can i move the SQL server install without losing any data? I know this is a messy situation - it was inherited from my predecessor.

 

[mrdenny]

While it's probably possible to hack the registry and simply move the install, it would be messy at best, and probably wouldn't work. Your best bet will be to uninstall and reinstall SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[f0rsaken4ngel]

Okay, i have no problem with that... But how do i move the database(s)? how do i locate the file in the first place...?

 

[mrdenny]

Before you uninstall the SQL Server log in with the GUI and run this command against each user database.
[/code]
select *
from sys.database_files
[/code]
This will tell you where all the files for each database are located. After SQL is uninstalled, simply move the files to the new location. After SQL is installed and patched you can simply right click in the GUI and select Attach database and reattach them.

Don't forget to write down all the usernames and passwords that will be needed for your applications to log back in.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com