April 28, 2011 at 9:32 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 11:59 am
Is the new server the same build as the existing server? If it isn't you may have trouble, according to this.
If they are the same, shouldn't you be able to restore over the new server's master db with the "with replace" option?
April 28, 2011 at 4:56 pm
you could install SQL2005 on the new server and then upgrade it to get the same directory structure, that would simplify things.
However unless you have a complex environment with lots of features that contain information in the system databases restoring system databases across servers is not the best way to go, script out the server level objects such as logins instead.
with your current setup you can restore master with replace but then SQL still will not start because master holds information on where it expects the other system databases to be and those locations will not exist. All the issues are fixable but it is going to be messy.
Avoid using maintenance plans if you go the system database restore root, they are a pain when you restore msdb to a server of a different name.
---------------------------------------------------------------------
April 28, 2011 at 10:53 pm
Julie
Have you considered simply shutting down SQL Server and taking a full system level backup of the server and then restoring that to your new box? That would remove all your path issues as it would be in the same current locations.
However, regarding your comment about restoring in a DR event - surely you would have backups of the new server post-migration, even if you did do a clean install and the effort of RESTORE WITH MOVE etc. That means that any subsequent restore of those system databases to a new server in a DR would work OK with doing an install and then RESTORE as the new structure would be OK.
I think the initial pain of the migration and changing paths will make life easier in the long term.
April 29, 2011 at 2:51 am
providing the builds are in sync you can just restore the master database in single user mode. The master database paths are held in the registry as startup parameters for the instance. All other databases have their paths held in the master database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2011 at 3:37 am
BTW - duplicate post -
http://qa.sqlservercentral.com/Forums/Topic1100346-1550-1.aspx#bm1100667
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply