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
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".