SQL Data Directory

How can I move the data directory of SQL Server to a different drive?

I installed SQL Server 7 as part of the SBS Upgrade and did not tell the system to put the data directory on to the large data drive, so the system drive has the data for SQL and I am running out of space very quickly.

So can anyone help me soon.

Thanks in advance
SadManAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jboydCommented:
Look a SP_DETACH_DB and SP_ATTACH_DB in the online books.
0
SadManAuthor Commented:
I do not know how that will help, I want to move the data directory to a different drive, i.e.

move the C:\MSSQL\DATA directory to D:\SQLDB
0
SadManAuthor Commented:
see previous comment
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NaelCommented:
I don't know if this will help, but you can try it.
1. Backup all your DBs
2. Create new devices in the location you want.
3. Restore DBs to new location.
4. Delete old devices.

0
chigrikCommented:
You can do the following:

1. Make backup from your databases master, model and msdb.
2. Copy in new place all files that contain your other databases (except tempdb), i.e. *.mdf, *.ndf and *.ldf files.
3. Uninstall your SQL Server 7.0.
    For more information look at here: http://www.ntfaq.com/ntfaq/sql53.html#sql53
4. Install SQL Server 7.0 on new location.
5. Restore your databases master, model and msdb from previous backup.
6. Attach other databases that you had with stored proc sp_attach_db
For example:  
sp_attach_db @dbname = 'Test',
             @filename1 = 'D:\SQLDB\Test_Data.mdf',
             @filename2 = 'D:\SQLDB\Test_log.ldf'

It will work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SadManAuthor Commented:
Is there no way of doing that with out re-installing SQL Server, as it means re-installing the SBS Server from scratch, from what I've heard of how unstable SBS 4.5 can be if you do things like that.
0
chigrikCommented:
The way in general is, but I did not recommend it to use.

1. Shutdown Sql Server.
2. Run regedit.exe and modify value for SQLArg0, SQLArg2 on puth
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
    as you need.
3. Start Sql Server. Master database now on the new place.
4. Run sp_detach_db 'msdb', 'true'
5. Run     sp_attach_db @dbname = 'msdb',
                 @filename1 = 'D:\SQLDB\msdbdata.mdf',
                 @filename2 = 'D:\SQLDB\msdblog.ldf'
    Msdb database now on the new place.
6.Stop and start Sql Server.
7. Run

use master
go
     ALTER DATABASE tempdb
     MODIFY FILE
        (NAME = tempdev,
          FILENAME = 'D:\SQLDB\tempdb_data.mdf')
go
     ALTER DATABASE tempdb
     MODIFY FILE
        (NAME = templog,
        FILENAME = 'D:\SQLDB\templog.ldf')
go

8. Stop and start SQL Server. Tempdb database now on the new place.
9. I don't know why not work step 7 for model database, but I receive error 5037:MODIFY FILE failed. Do not specify physical name.
10. Copy to the new place all files that contain your other databases, drop this databases and attach them with sp_attach_db (from new place).
11. Now you will have only database model on an old place, which occupies only 2Mb (by default). If you want to transfer and it, try manually to modify system table sysdatabases from master database, but I don't recommend it to do.
0
chigrikCommented:
On your place, if re-before me stood such problem, I carry on the new place all databases except master, model, tempdb
and msdb. More so, that occupy a little place (at least once by default near 22Mb).
0
SadManAuthor Commented:
Thanks for all of your help, I had to re-install the server yesterday, so I had a chance to move the directory, but I did try everything you guys suggested, it worked, until someone wanted to send a fax and buggered Exchange.  Gits.

Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.