September 23, 2011 at 6:18 am
When you install SQL server 2005 where is the option to change the default installation of system databases to another location ? I sure it must be there but I am drawing a blank
September 23, 2011 at 6:37 am
MarvinTheAndriod (9/23/2011)
When you install SQL server 2005 where is the option to change the default installation of system databases to another location ? I sure it must be there but I am drawing a blank
Hi, i got this from MSSQLTIPS!
Moving all SQL Server 2005, SQL Server 2008 or SQL Server 2008R2 System Databases
Once you have reviewed the KB articles above, you can follow these steps to move all system databases at once.
Update the -d and -l startup parameters for SQL Server for the new location of the master data and log file
Issue ALTER DATABASE commands to change the file location for the model, msdb and tempdb database files
Stop SQL Server
Move the MDF and LDF files to the new locations specified in steps 1 and 2 for the master, model and msdb databases
Start SQL Server
Delete the old tempdb files
In addition to the master, model, msdb and tempdb databases SQL Server 2005 introduces the mssqlsystemresource database. Microsoft recommends not moving this database, but if you do want to move this database as well you will follow these steps. Note you cannot move the mssqlsystemresource database for SQL Server 2008 or SQL Server 2008R2.
Update the -d and -l registry startup parameters for SQL Server for the new location of the master data and log file
Issue ALTER DATABASE commands to change the file location for the model, msdb and tempdb database files
Stop SQL Server
Move the MDF and LDF files to the new locations specified in steps 1 and 2 for the master, model and msdb databases
Put SQL Server in minimal configuration mode by adding these two startup parameters -f and -T3608 and then start SQL Server
Issue ALTER DATABASE commands for the mssqlsystemresource MDF and LDF files using same path as the master database
Move the MDF and LDF files to the location specified in step 6 for the mssqlsystemresource database
Stop SQL Server
Remove the startup options added in step 5
Start SQL Server
Delete the old tempdb files
Regards,
Paulo Condeça.
September 23, 2011 at 6:38 am
MarvinTheAndriod (9/23/2011)
When you install SQL server 2005 where is the option to change the default installation of system databases to another location ? I sure it must be there but I am drawing a blank
I think there is no such option there sysdatabase remain in your Data folder of where the sql server installtion. but later on you can your sys databases location with the help of sql server configuration manager. there you need to right click on the sql server service and then go to advanced tab then go to start up paramertes and then copy paste your new desired location of sys databases and also copy sys databases at that location. donot foget to restart the sql server
September 23, 2011 at 6:55 am
MarvinTheAndriod (9/23/2011)
When you install SQL server 2005 where is the option to change the default installation of system databases to another location ? I sure it must be there but I am drawing a blank
during the SQL Server 2005 installation at the part where you specify the file paths, if you select the "Data Files" option you may change this path using the file browser button
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 23, 2011 at 8:04 am
Microsoft instructions below;
http://msdn.microsoft.com/en-us/library/ms345408.aspx
Thanks
Chris
September 23, 2011 at 8:10 am
SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted
Group: General Forum Members
Last Login: Today @ 2:56:32 PM
Points: 1,558, Visits: 216
Microsoft instructions below;
http://msdn.microsoft.com/en-us/library/ms345408.aspx
Thanks
Chris
Is this applicable to SQL 2005 also ?
September 23, 2011 at 8:17 am
Drop the Version drop box down at the top of the page and choose either SQL Server 2005 or SQL Server 2008.
Thanks
Chris
September 23, 2011 at 8:41 am
Set the default during setup or later in the server properties. Master comes from the startup parameter. Everything else comes from stored values in master.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply