January 13, 2016 at 8:50 am
Can System db's such as MSDB, distribution and master can be restored to SQL 2014 from backup files of SQL 2008R2 ?
I'm getting following error when restoring MSDB database.
Msg 3154, Level 16, State 4, Line 3
The backup set holds a backup of a database other than the existing 'msdb' database.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
January 13, 2016 at 8:52 am
No. Master can't be restored from any other version, and to be honest you shouldn't be moving most system DBs from one server to another. Script their contents, recreate. In the case of distribution, consider re-doing the replication.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2016 at 9:05 am
GilaMonster (1/13/2016)
No. Master can't be restored from any other version, and to be honest you shouldn't be moving most system DBs from one server to another. Script their contents, recreate. In the case of distribution, consider re-doing the replication.
FYI: I'm just running some test, the reason I'm trying to restore system DB's are to keep intact all replication, upgrading from SQL 2008R2 to SQL 2014
January 13, 2016 at 9:08 am
Script out replication, always, and keep it handy. There are times you'll need to reset it up.
Use that to move replication to a new, SQL 2014, instance.
January 13, 2016 at 1:50 pm
Don't restore system databases from one instance to another, doing so is asking for subtle problems.
Script the logins, linked servers, config settings, roles, jobs, replication out and use those scripts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2016 at 5:06 pm
An underlying point in all of this is that you should already have had most if not all of this stuff scripted and in a version control repository so that rebuilding an instance in the case of a catastrophe, provisioning a new server or migrating/upgrading your server is low stress since you're mostly prepared to bring it online.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2016 at 11:07 am
GilaMonster (1/13/2016)
Don't restore system databases from one instance to another, doing so is asking for subtle problems.Script the logins, linked servers, config settings, roles, jobs, replication out and use those scripts.
I didn't restore system Db's but I did restore distribution db, scripted SQL Agent jobs (Log Reader, Rep Snap jobs etc), at first everything worked, replication are all intact, jobs are running but when creating new subscription to existing publication and run View Snapshot agent. It errors out
Could not find the regular snapshot job for specified publication
Supply either @job_id or @job_name.
When creating replication snapshot job on SQL 2014, it has new job_id. How can I update meta data table use new job_id or change Job agent to have old job_id ?
January 14, 2016 at 11:08 am
As I said,
GilaMonster (1/13/2016)
Don't restore system databases from one instance to another, doing so is asking for subtle problems.
Distribution *is* a system database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply