April 28, 2011 at 9:34 am
I am migrating to a new SQL server. My existing sql server is currently running SQL 2008 sp1. However, it was upgraded from SQL 2005. On this server my system databases reside at F:\MSSQL\Data. When I am building the new server I can specifiy the location of the root data directory during the installation of SQL 2008. I am choosing F:\. However, then by default the system database directory is now F:\MSSQL10.MSSQLSERVER\MSSQL\Data. This means that I cannot simply restore my system databases on the new server.
I know that I can restore my user databases and script logins and jobs. However, what would I do in a disaster scenario? How would I build a new server with only backups of my system and user databases if the location on the new server is different? Everything I read about DR plans says to backup those system databases. I guess I just don't understand how to use them in this scenario. Thanks.
April 28, 2011 at 9:41 am
It is very easy to restore databases and to move the files in one step.
The restore statment includes the MOVE syntax which takes the logical file name and the new physical path. If you do this through SSMS and press the Script button it will generate this syntax for you.
April 28, 2011 at 9:47 am
Everything I'm reading says it is not that simple with the system databases. How would I restore them, especially master, to the new location?
April 28, 2011 at 10:06 am
To move the master database, follow these steps.
1.From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3.In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
4.Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
Copy
-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATAmaster.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLLOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLDATA\mastlog.ldf
If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
Copy
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
5.Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
6.Move the master.mdf and mastlog.ldf files to the new location.
7.Restart the instance of SQL Server.
8.Verify the file change for the master database by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO
For further information visit on this mentioned link below
http://msdn.microsoft.com/en-us/library/ms345408.aspx
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 28, 2011 at 10:17 am
The restore syntax is the same. Not a big deal there. However the startup parameters for master might need to be changed in a DR situation. In the services applet, check out the startup parameters for the database engine service. you can specify the location of master there.
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
April 29, 2011 at 7:12 am
The master databases from 2005 and 2008 are difference, i am not sure you want to restore this datbases master for 2005, all other databases other than system are fine. If you need procedures from master i would export them out.
If this is a new install and you didn't set master to your correct location I would uninstall and reinstall SQL again.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply