installation of system databases to another location

Question

installation of system databases to another location

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
September 23, 2011 at 6:18 am

#390460

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Netic Old Hand Points: 362 More actions · Answer 1

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.

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 2

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

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" 😉

chris.mcgowan SSCarpal Tunnel Points: 4269 More actions · Answer 4

Microsoft instructions below;

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Thanks

Chris

------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 5

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 ?

chris.mcgowan SSCarpal Tunnel Points: 4269 More actions · Answer 6

Drop the Version drop box down at the top of the page and choose either SQL Server 2005 or SQL Server 2008.

Thanks

Chris

------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 7

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.