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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chigrikConnect With a Mentor Commented:
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
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
SadManAuthor Commented:
see previous comment
0
 
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
 
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
All Courses

From novice to tech pro — start learning today.