Moving SQL Server 2005 databases

ITdude

Hi,

I recently installed SQL Server 2005 on a box but for some reason it would not allow me to located the databases on the drive. I have basically installed the main database and reporting services if I recall.

What I would like to know given this scenario, what is the easiest way to move the main database and reporting services database to my drive. Or for that matter what shoud I move from the C: dive to the D I assume it is a detach/attach scenario but which files? I assume databases and transaction logs but need to be sure.

By the way the C: drive is a RAID 1+0 Array and the D:drive is a RAID 5 consisting of 3 72GB drives plus a hot spare.

I have heard different things about redundancy versus performance etc etc.

Mainly, though which files should be moved friom the C: to the to accomplish my goal. Please mention the file name and the extension as well. I am trying to get through a SQL Server learning curve here.....

Thanks


P.S. There is no data stored yet. I have only done the initial install and upgraded to service pack2

blargoe

It would be the database file (mdf) and the log file (ldf) that you would move. You can detach the database, move the files to the D drive, and attach. When you do the attach, you have to browse to the location of both the database file and log file new locations.

If by main database you mean the master database, I'm not sure off the top of my head how to do that.

BeaverC32

For a detailed run down of the necessary steps, refer to Books Online (enter "Moving database" as your search term).

Here is a run down of the necessary steps:

The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.

To move the master and Resource databases, follow these steps.

From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

In the SQL Server 2005 Services node, right-click the instance of (for example, SQL Server (MSSQLSERVER)) and choose Properties.

In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf


Stop the instance of by right-clicking the instance name and choosing Stop.

Move the master.mdf and mastlog.ldf files to the new location.

Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

For the default (MSSQLSERVER) instance, run the following command:
NET START MSSQLSERVER /f /T3608

For a named instance, run the following command:
NET START MSSQL$instancename /f /T3608

ITdude

Well actually would it be necessary to move the main database? There are alot of files in there with similar extensions. I want to be sure I am moving the right components.

ITdude

Thanks guys. I am still trying to wade through this stuff to decide what and where I should move. Basically the database will be gathering data about a few other networks and relies upon reporting services to accomplish this.

What I need to figure out is what needs to be moved from the C: drive to drive where there is more space and redundancy...And I thought Exchange was fun.

BeaverC32

I guess I'm unclear as to what exactly you're trying to do.

Is this database that you want on the D drive already installed/attached? If not, the easiest way to put this on the D drive would be to create another instance of SQL Server on the D drive, and then place the mdf/ldf files in the data folder (or attach the DB).

ITdude

Let me approach thisd from a different way. Lets assume I was setting up a new SQL Server 2005 on the above configuration. I chose the main database and reporting services along with the other defaults listed except for some during the install.

Where would be the best location for the databases for these to be installed? If it is on the drive which is larger and RAID5 which files would I want to move now from the C: drive to the including filename extensions and how is the easiest way to accomplish this without creating a new instance?

While there is currently no data stored yet, I would prefer not to create a new instance. Does this help, I hope?

blargoe

Best practice is to have all database files (anything *.mdf and *.ldf) off of the system drive. So those would be the ones you would move to D

ITdude

ok great. Now would the best way to accomplish that by detaching an attaching all of the .mdf and .ldf files or can it be done somehow uding the GUI?

Thanks again

JDMurray

FYI: SQL Server 2005 will not read database files placed on compressed partitions. If you do so, a very cryptic error message will result, but it will not clearly indicate what the problem is. (My personal experience on this one.)

Apparently, it is quite common to put SQL Server versions 6, 6.5, 7, and 8 (2000) databases on compressed partitions to reduce their size, and then call Microsoft complaining about how slow your database access is . Microsoft finally got tired of this and decided that SQL Server version 9 (2005) would not work with compress partitions.

ITdude

Thanks for the heads up on that. The partition is not compressed or encrypted etc etc....I just need to get that stuff over there.

TechJunky

blargoe wrote:

Best practice is to have all database files (anything *.mdf and *.ldf) off of the system drive. So those would be the ones you would move to D

True.

And if you really want to go for speed then I would suggest moving them onto a seperate physical drive as well.

BeaverC32

In SSMS:

Right-click the database you want to move, select "Tasks", and then choose "Detach".

Click "OK".

Now, in explorer, navigate to the folder where the mdf, ndf (might not be any), and ldf files were located, and move them to the new drive/folder you want them to reside in.

Back in SSMS, right-click the Databases folder and select "Attach". Click the "Add" button, and select the mdf file from the new location. Click OK, and you have the database in the new location.

To verify that this worked, right-click the new database, select properties, select "Files" on the left nav pane, and then verify that the "Path" column lists the correct location of the database files.

ITdude

cool thanks. I know this can be done via command line but it would be less work with a GUI. So basically I go through the list of all the database entries that are currently on the C: drive and then move anything that has a mdf extension or a .ldf or possibly a ndf. and move them via explorer to the location on the drive and then reattach and add.

Doesn't seem all that painful. I just wanted to be sure that I got them all. Is it okay to move these all to the root of the drive or better to create a databases folder and place them inside there?

thanks

BeaverC32

You could just place them in the root of the D drive, but it would probably be wiser to place them in a folder (D:\ Databases, or something similar) so that the files are grouped together and are easier to find, especially if there are a lot of files already residing on the root of the drive.