restoring msdb from backup file

Question

restoring msdb from backup file

cidmi

SSC Journeyman

Points: 95
More actions
September 17, 2018 at 7:39 am

#408459

Hi there,
One client has a SQL Server 2012 Instance working on a production machine.
They had a problem in the server and the msdb database is in status "recovery pending".
They don't have a backup plan for the system files so we'll try to get msdb online through a backup from another machine.
The problem I am guessing we can have is that
SQL Server version on client site is 11.0.3128.
11.0.3128.0 2011.110.3128.0 2793634 Windows Installer starts repeatedly after you install SQL Server 2012 SP1 January 3, 2013
Is there a problem if we try to restore msdb from a backup of an earlier version, 11.0.2100 for example (SQL Server 2012 basis installation)?
I can't test it until I have access to their database.
Thanks in advance.
Kind Regards.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 1

So, was MSDB never backed up on this production Instance? I am not sure if SQL Server will allow to restore system database(s) with diff build level. Did you consider rebuilding using instmsdb.sql script?
Both the approaches will wipe out all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
Refer to this article for more info: https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2017#RebuildProcedure

cidmi SSC Journeyman Points: 95 More actions · Answer 2

Thanks Sreekanth B,
They assumed they lost the information stored in the msdb database.
We have to create again maintenance plans and jobs.

cidmi SSC Journeyman Points: 95 More actions · Answer 3

Confirmed the msdb versions should match.

Msg 3168, Level 16, State 1, Line 1
The backup of the system database on the device C:\msdb\11.0.2100.bak cannot be restored because it was created by a different version of the server (11.00.2100) than this server (11.00.3128).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.