Sql-server – Restore MSDB data to a new server

sql-server-2008-r2

I'm trying to move everything from a SQL Server 2008 R2 (enterprise) instance to a new instance on a different server. To do this, I thought (after some research) that I'd be able to move the system databases – master, msdb and model.

To accomplish this, I've got backups of all my system and user database, and I'm trying to restore them to the new instance. Note that the new instance is the same version of SQL, but the IP address, instance name and port are all different.

I've restored the master database successfully. But when I try to restore the msdb database it just doesn't seem to work.

The command I'm using is:

restore database msdb from disk='\\webapp\SQLBackups$\msdb.bak' with replace,recovery

and the response I'm receiving is:

Processed 2912 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1. Msg
3283, Level 16, State 1, Server SQLServerName\Instance_Name, Line 1
The file "MSDBLog" failed to initialize correctly. Examine the error
logs for more details. Msg 3013, Level 16, State 1, Server
SQLServerName\Instance_Name, Line 1 RESTORE DATABASE is terminating
abnormally.

If I try to execute:

restore database msdb with recovery

I receive:

The database cannot be recovered because the log was not restored.

I didn't have a log backup, because the msdb database was in 'simple' recovery mode. So I went back to the original server, switched recovery mode to 'full', created a new full backup and a log backup, then tried to restore this new backup, but still got the same result. If I try and access anything within SSMS I just get a message saying the msdb database cannot be opened as it is in the middle of a restore.

So I seem to be stuck. I cannot use the server as it thinks it's mid restore. I cannot drop the msdb database as it's a system database. I cannot complete the restore because whatever options I try, with whatever backup files, I still get the message "The file "MSDBLog" failed to initialize correctly".

Any ideas?

Best Answer

I did that kind of migration and I never want to do it again. But if you really want to do it, it is feasable. Here's my recommandations:

  1. Make sure you have the exact same version on both instance.

    select SERVERPROPERTY('ProductVersion')

  2. Migration will be way easier if the root folder is in the same path. If you can't have that, it's much more complicated and I strongly suggest taking another path. BTW I did that with SQL 2000. Now I think you can copy over the ROOT folder in one shot. Some files like FullText will be failing if the catalogs are not migrated and replication snapshot folder and etc... Take note of all the datafiles path with:

    select * from master..sysaltfiles

  3. If you can't have the user databases at the same location, no big deal. You'll attach them later.

  4. Start the service and pray.

Now back to restoring MSDB and MODEL. In your particular problem. Your last restore was done without recovery. The MSDB is inconsistant and can't be recovered. I suggest you restart SQL Server in Single User Mode (using -m startup parameter) then DROP MSDB and restore it again.

Related Question