7

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?

Improve this question
  • 1
    Have you tried to do a backup of the MSDB database with the NO_LOG option? – Mark Kremers Aug 4 '12 at 7:51
  • Sorry it's taken a while to respond. It's a good suggestion and might have helped, but as it happened I re-ran the restore command and it worked fine, even after it had failed several times previously. Unfortunately I have no idea why it suddenly started working as I changed nothing - it was just one of those "work, damn you!" moments where you try the same thing again out of sheer frustration. Ah well! – paulH Aug 14 '12 at 14:34
  • For anyone else wanting to do the same thing by the way, I discovered that moving all the system databases betweeen servers in this way did not produce an especially robust system. I eventually scrapped the whole idea and simply copied SQL Agent Jobs with an SSIS job, copied database logins with sp_help_revlogin, and manually recreated everything else. – paulH Aug 14 '12 at 14:37
  • @paulH : I think you should add your comments as an answer and accept it. If you can provide more information, it would be splendid since this is a useful use case to know . – Atilla Ozgur Aug 19 '12 at 12:52
  • Check Event Log and ERROR.log in the binary folder. They will give you more information. – swasheck Oct 26 '12 at 4:38
4

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.

Improve this answer
  • Unfortunately for me the root folder was different. So the advice about 'taking another path' applies, and that is ultimately what I did. It just wasn't worth the hassle and the risk that I'd end up with a system with unidentified issues. The tip about dropping the MSDB database in single-user mode is a good one though, and would have helped me out if i'd known about it at the time. – paulH Oct 26 '12 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.