Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Restore msdb and master

Posted on 2005-05-17
26
Medium Priority
?
1,518 Views
Last Modified: 2008-01-09
hi,

I restored use db which was fine, when I tried to restore master and msdb, I have the following error:

master:
RESTORE DATABASE must be used in single user mode when trying to resore the master db.



msdb:
The bk of the system db on device "my bk file name" cannot be resored because it was created by a different version of the server (134218546) than this server (134218488)


HELP!!
0
Comment
Question by:mcrmg
  • 13
  • 13
26 Comments
 

Author Comment

by:mcrmg
ID: 14021716
I need to put DTS package back, any way that I can restore them?  thx
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14021741
Yes, you need to start sqlservr.exe from a command prompt using -c and -m then use osql to run "restore database master....etc."

see also:
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

which has some useful info on system databases

Is this a new server, or a rebuild, or waht?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14021755
DTS packages are stored in msdb, assuming you are storiung them in SQL Server when you save (as opposed to structured storage file or VB script file)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mcrmg
ID: 14021786
sqlservr.exe -c -m

After that, can you show me the line to restore it?  thx  
0
 

Author Comment

by:mcrmg
ID: 14021801
do I type "restore database master...." under command line?
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 2000 total points
ID: 14021888
you need to open a second DOS Window and navigate to the directory that has osql.exe

type:
osql -E       (press enter)

If it worked, you should get a 1>

At the 1>:
Restore database master from disk="c:\master.bak" with recovery       (press enter)
At the 2>:
GO       (press enter)

OBVIOUSLY, you need to do some reading in Books Onlone in the subject areas of OSQL and RESTORE DATABASE commands.  You will also need to substitute in the correct path to your master database backup file, and you may need to use the "With Move" parameters.
0
 

Author Comment

by:mcrmg
ID: 14021946
>>OBVIOUSLY, you need to do some reading in Books Onlone in the subject areas of OSQL and RESTORE DATABASE commands.

lol


When I run those commands, I still get that "............must run in single user mode.............."

But I did ran sqlservr.exe -c -m already........

Thanks
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 2000 total points
ID: 14021981
Did you stop the currently running SQL Server service?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14021992
Glad I got a "lol" there....after re-reading, it looked like I was trying to be rude....certainly not the case :-)
0
 

Author Comment

by:mcrmg
ID: 14022060
I do need more knowledge on this..... (otherwise, I would be the one who is answering the question)   lol

anyway,


after I stop the service, it looks like it is under single user mode.  And inside osql, for both master and msdb, they all say that db were created in different version, can not be restored.....
0
 

Author Comment

by:mcrmg
ID: 14022097
okay, in single user mode, only master db is allowed to be restored.  But I still get the error msg saying " it was created by diffterent server..."


also, how can I restore msdb db?  thx
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14022157
worry about master first, then I believe you can get msdb restored via normal processes (but you may need to start sql with a trace flag to bypass recovery for msdb on startup)

Can you post the exact text of the "differernt version" error?  Or better yet, paste it into support.microsoft.com (which is the same thing that I would do with it).  My guess is that your old server is on a different service pack/hotfix  than the one you are trying to restore to.
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 2000 total points
ID: 14022182
0
 

Author Comment

by:mcrmg
ID: 14022207
Actually, it is the same server, I uninstalled it then reinstall SQL again on the same server.


The backup of the system database on device c:\myfilename.bak cannot be restored because it was created by a different version of the server (134218546) than this server (134218488)
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14022229
Your new install is not the same as the old one...see my link for how to determine the build number that the backup was made on
0
 

Author Comment

by:mcrmg
ID: 14022265
8.0.818  BK file

8.00.2039


*sigh*
0
 

Author Comment

by:mcrmg
ID: 14022303
A quick question, what is in master db?

would it be possible that I just resore msdb to get DTS package??   this question maybe so dumb)  lol

0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14022335
sysdatabases, syslogins...all sorts of fun things are in master.  I stronglyrecommend that you get the server to the same level as the old one, then restore master then model (if you have it) then msdb
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14022339
I know what .818 is...what is .2039?  Sp4?
0
 

Author Comment

by:mcrmg
ID: 14022353
it is strange that those bk files were backup last night.....still sp3

i updated it with sp4 this morning....then uninstalled sql then reinstalled it with sp3......but I did not install sp4
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 2000 total points
ID: 14022371
0
 

Author Comment

by:mcrmg
ID: 14022451
okay, I just reinstalled sql AGAIN.........  :(

I got 8.00.194

now what would be next step?  how can I find the hotfix?  thx
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 2000 total points
ID: 14022504
.194 is the Release To Market.

Install SP3, which will get you to .760

then click the link in my last post to get the patch that will get you to .818
0
 

Author Comment

by:mcrmg
ID: 14024131
okay, I restored master, msdb and user db back....Thank you very much.   lol

One more question, would it be okay to have sp3 on dev server (the one I have been working on) and sp4 on pdn server???

On dev server, I user dts, triggers........ after data gets imported, I just sync data with pdn server...

Thx
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 14024271
From the data standpoint, it shouldn't matter...datatypes and table structures don't change in a service pack.  If all you are doing is importing data to Dev them moving it to prod, you should be OK.  But the only way to know is to read through the list of what all was fixed/changed in SP4 and then see if you are using any of it.

My recommendation to my clients is to wait on SP4 and let others find the bugs first :-)  Especially in prod.
0
 

Author Comment

by:mcrmg
ID: 14024291
okay, Thank you for the help.........
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question