July 21, 2008 at 10:30 am
Hello,
I need to move all of the System databases that got installed on the C: drive to another drive (D:).
We are running low on disk space on the C: drive. All the other databases already recide on the D: drive. Please let me know what's the best way to accomplish this goal.
Thank you.
July 21, 2008 at 10:38 am
These are the steps I use.
July 21, 2008 at 12:59 pm
follow these steps
move the MASTER DB first. Stop the sql instance and agent and copy the database files
(master, model, tempdb, msdb, mssqlsystemresource and dist DB files) to their new locations.
MSSQLSYSTEMRESOURCE and DIST need to be in same folder as MASTER database
edit the SQL server service and change the paths for the MASTER data and log files to the new location.
Add in the "-m;" parameter to start the instance in single user mode, then start the instance
set the MSSQLSYSTEMRESOURCE DB paths using the following T-SQL via a SQLCMD shell
syntax is SQLCMD -Sserver\instancename
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data ,
FILENAME = 'D:\Program Files\whateverpath\mssqlsystemresource.mdf' )
ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log ,
FILENAME = 'D:\Program Files\whateverpath\mssqlsystemresource.ldf' )
restart SQL instance in multi user mode removing the "-m;" parameter
now set the path of each file in the remaining system databases
ALTER DATABASE model MODIFY FILE ( NAME = modeldev ,
FILENAME = 'D:\where ever\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog ,
FILENAME = 'F:\where ever\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata ,
FILENAME = 'D:\Program Files\where ever\msdbdata.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog ,
FILENAME = 'F:\Program Files\where ever\msdblog.ldf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev ,
FILENAME = 'D:\Program Files\where ever\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog ,
FILENAME = 'F:\Program Files\where ever\templog.ldf' )
after these paths have been set restart the SQL instance and its agent.
clean up by removing the now unused database files for each system database from the original locations.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 12:06 am
[font="Verdana"]Checkout the links below
Moving system databases -- SQL Server 2000
Moving System Databases - SQL Server 2005[/font]
Regards..Vidhya Sagar
SQL-Articles
July 22, 2008 at 7:30 am
Vidya,
I made the changes for temp database and things were fine.
I tried the msdb and model and my SQL system completely broke!!!
PLEASE HELP!
July 22, 2008 at 12:12 pm
what steps did you take to move the databases and what commands did you use?
what errors are you getting?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 6:24 am
Why bother copying the files for TempDB? It practically gets "recreated" every time the service gets stopped. Just run the Alter Database command on the files, stop the service and restart. TempDB is now in the directory you told it to be in. No need for copying the files for this one.
As far as the others, yes, the files have to be physically moved.
NetEng81 - Please list the exact steps you used to move the model & MSDB and what (if any) errors you're getting. This way we can see if you missed anything.
Also, did you move Master yet?
July 23, 2008 at 6:28 am
I'm all set now. I made a typo with the physical name and it created wrong association. I had to restore the master in recovery mode and could go back and make the changes.
thanks all.
July 24, 2008 at 7:45 am
You may also want to look at default location of where SQL does it's log dumps. That can take space from your c drive also.
On the server
Open SQL Server Configuration Manager -> SQL Server Configuration Manager (Local) -> SQL Server 2005 Services on the right you will see all the services running for your environment.
Right click on SQL Server (MSSQLSERVER) and open properties, go to the Advanced Tab and find the Dump Directory. Change that from C to a different location on different drive. The service will have to be restarted to save changes.
review all services and do the same. That will also help on space crunched C drive.
What Standards does everyone else recommend on a new SQL2005 build for a C drive? I have started off with 20GB minimum but now wonder if I underestimated.....
July 24, 2008 at 7:51 am
20GB should be fine so long as you don't put any SQL Server databases (including system DBs) on your C: drive.
Usually I reserve C: drive for the OS and for any program information that will not allow an install on any other drive. The image we have for SQL installs the base files on C: but allows us to move the databases & logs. (I don't know if this is a corporate image or a Microsoft image).
August 5, 2008 at 8:29 am
If you move master and/or mssqlssytemersource from their default location, do not be surprised if the next service pack install fails and leaves your SQL instance unuseable. You have been warned...
The SP2 install puts a new copy of mssqlsystemresource in the default location for that database, then updates master to tell it what version of mssqlsystemresource to expect. It then restarts SQL Server, quickly followed by a SQL Server crash. With a lot of effort you can get SQL Server working again, but because I could not be certain the SP2 apply had worked 100% correctly I chose to uninstall and reinstall.
It may be that SP3 can cope with master and mssqlsystemresource being in non-default locations, but IMHO it is far better to leave these 2 databases alone.
In SQL Server 2008, the mssqlsystemresource database is located in the \binn folder, and I advise you leave it there.
If you run Windows 2003 with SQL Server 2005, my advise is to have a system disk size of 15 GB. You can just about get away with 10 GB, but if you keep reasonably up to date with Windows and SQL fixes then 10 GB quickly seems small.
If you plan to use Windows 2008 then the minimum size for a Windows 2008 install is 21 GB. The full implications of running Windows 2008 and SQL Server 2008 for 3 years are not yet known, but I advise you plan for a system disk of 35 to 50 GB.
BTW, I remember when people were horrified about giving NT4 a system disk of 1 GB, and Windows 3.1 needed single figure MBs. You tell the kids of today you could get a Microsoft OS installed in less than half a gig and they won't believe you!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 5, 2008 at 9:17 am
just ensure that MSSQLSYTEMRESOURCE and DIST are in the same location as your MASTER database and all should be well
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply