Solved

The restore of SQL server 2008 R2 database

Posted on 2014-03-12
30
272 Views
Last Modified: 2014-04-03
Dear all,

Right now restored the MASTER DB of MS SQL server, but once it is restored and I start the instance, it said some signature is not correct.

and if I start that single user mode to restore the master DB again I see it keep try to mount all user database and later on it can't find all, then the single use mode failes.

is the correct order to restore all DBs is:
1) user database.
2) msdb
3) model
4) master DB?
0
Comment
Question by:marrowyung
  • 14
  • 7
  • 4
  • +3
30 Comments
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 250 total points
ID: 39925595
It really depends on whether you use simple or full recovery.

Simple Recovery Restore

Full Recovery Model - Complete Data Recovery Restore

CAUTION:  Follow the steps carefully here for the Master database - http://technet.microsoft.com/en-us/library/ms190679%28v=sql.105%29.aspx

HTH
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39925623
"CAUTION:  Follow the steps carefully here for the Master database - http://technet.microsoft.com/en-us/library/ms190679%28v=sql.105%29.aspx"

this what I followed.

"It really depends on whether you use simple or full recovery."

the master DB basically always simple mode, then what say you ?
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39925639
I think your issue is this..  Anytime you do a restore of User data, especially if the users are on your domain, for example, you do need to do an ALTER USER - http://msdn.microsoft.com/en-us/library/ms176060%28v=sql.105%29.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39925695
NONONO

we are doing restore test for a new SQL instance built, we need to restore all DB, what is the correct steps to restore DB?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39925748
it seems that model dB don't need to be restore ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930721
I would restore the user databases and then provided the versions  are identical you can restore the master database.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39932266
Dear all,

I found something good for SQL server 2008 R2 and I follow it (I didn't know last time when I do it I don't need that !! ) it works but I didn't restore the resource DB yet.

http://www.mssqltips.com/sqlservertip/2425/rebuilding-sql-server-on-different-hardware-after-a-failure/

under what situation MUST I restore the resource database?

The reason I said I didn't do this read that link is I don't do it In that way when I upgrade the DB to a new hardware 6 months ago, but this time!! What also surprise me is that I tried OTHER vendor's MS SQL encryption and compression software, but finally when I do this restore test, we (both me and the Vendor's support) finally we found out that only MASTER DB can't be restored by their script/software, but the rest of system and user database.

funny ! right ? the most funny thing is that their support guy ALSO Finally found out that problem !!! and their product exist in their market for a long long time.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39932270
"I would restore the user databases and then provided the versions  are identical you can restore the master database. "

This will create problem too when you are doing this as the new server's drive letter is not the same as the old server.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39966516
I wouldn't bother restoring the system databases.

I restore the user databases. I then use the sp_help_revlogin script to transfer logins from one instance then new one.

I have all my maintenance jobs set up via a pretty generic script that I then just run.

The only time you need to restore the model DB is if you added a bunch of custom procedures/functions that you want in every DB you create on that instance.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39966564
but resource database must be useful, what is it ?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39966660
The resource DB is a "hidden" db that is front-ended by the rest of the system databases. It's built up from the modification of those databases.

The master is mostly used to hold the system and user logins and the configuration of the other DBs. The model is the template DB when creating a new DB.

The msdb is the SQL Server Agent DB to list the jobs, and other stuff the Agent needs to do.

TempDB is workspace for the SQL Server.

Back in SQL 2000 and below you could actually edit the system tables in the master DB and sometimes that was the method to fix things. Starting with SQL 2005 they changed it so that the system tables you see in master are actually in the resource DB and they are now read only.
0
 
LVL 8

Expert Comment

by:chcw
ID: 39966732
If your normal restore process encountered problems, sometimes that is due to the corruption of one or two MDF database files. If that is the case(though rarely happens), then the final resort is to use some data recovery tools to recover that MDF database for you.

In the past, I use DataNumen SQL Recovery to recover some badly damaged MDF database, which works perfectly.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39968892
Jim P.,

"The resource DB is a "hidden" db that is front-ended by the rest of the system databases. It's built up from the modification of those databases.
"
I don't really understand this, please explain further.

" Starting with SQL 2005 they changed it so that the system tables you see in master are actually in the resource DB and they are now read only."

so this mean master DB and resource DB has to restore together ?

chcw,

"If your normal restore process encountered problems, sometimes that is due to the corruption of one or two MDF database files. If that is the case(though rarely happens), then the final resort is to use some data recovery tools to recover that MDF database for you."

this is not related to the resource DB we are talking about here.
this is not related to the resource DB, right?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39969400
You can't do a select on it. You don't see it in the Enterprise Manager. The various stored procedures and system databases access and do any changes to it via the SQL Server engine.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:marrowyung
ID: 39969612
"You can't do a select on it. You don't see it in the Enterprise Manager."

yes, that's why I tried to ask for that use of it.

"The various stored procedures and system databases access and do any changes to it via the SQL Server engine. "

so this mean we don't care about the resource database ?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39969739
It is basically the sqlservr.exe DB. It has to be there for the SQL Server service to run. But nothing is done to it by the DBA or end-user directly. Other than that, no one cares about it other than just being there.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971130
ok, on the new machine, we don't copy/migrate that ! right?
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 39971152
The way I would do it is just do a base install and regular configuration of SQL Server.

Then do a restore of the databases that you want to migrate.

Some applications, such as MS Dynamics GP creates passwords based off the instance information. But that is rare. The worst case scenario is the help desk will have to reset their passwords.

Then use the  sp_help_revlogin results to migrate the users. That will bring the same users and groups from the old server to the new one.

Then after the migration recreate backup jobs and such. I have a script that sets up the back up jobs. That is a separate Q though.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971215
"Then use the  sp_help_revlogin results to migrate the users. That will bring the same users and groups from the old server to the new one."

yeah, some script help to migration password.

"Then after the migration recreate backup jobs and such. I have a script that sets up the back up jobs. That is a separate Q though. ":

the migration of MSDB already do this, why recreate it ?

so in your steps, still don;t worry about the resource DB and simply ignore it, right?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39971224
yeah, some script help to migration password.

It does migrate the password under normal circumstances.  I know having done MS Dynamics GP disaster recovery tests that it doesn't work. But on all the rest it was just fine.

the migration of MSDB already do this, why recreate it ?

The name change from Server1 to Server2 will force you to touch every single job to maybe get them to work again. And that isn't guaranteed.

so in your steps, still don;t worry about the resource DB and simply ignore it, right?

The resource DB is for use of the sqlservr.exe only. You never touch it directly. So it is not for anyone to touch at all.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971240
"It does migrate the password under normal circumstances.  I know having done MS Dynamics GP disaster recovery tests that it doesn't work. But on all the rest it was just fine."

I done it before, it works.

"The name change from Server1 to Server2 will force you to touch every single job to maybe get them to work again. "

there are script to change it too.

"The resource DB is for use of the sqlservr.exe only. You never touch it directly. So it is not for anyone to touch at all. "

got it .
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39971244
there are script to change it too.

The script should grab the server or an instance name as a variable, and as part of the backup scripts use it in the job creation.

Same with the rest of the maintenance jobs you want to do such as integrity checks and reindexing.

The script is therefore generic.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39971394
Seems to be a bit of confusion...

You say you installed a NEW sql instance.

That will build a new Master / MSDB / Model / Resources databases.

Resource : You do not touch Resource DB (it cannot be backed up or restored using SQL)

Model : Model database is just that - a "model" used for creating new (user) DB's and would not worry about it. Easier to change the properties if / when needed (like default recovery model when doing a create database).

MSDB : Is a bit more interesting. It is used by SQL Server Agent for scheduling jobs, alerts, and Database Mail. More significantly, it can also be "home" to SSIS packages. Suggest you read Valentino's blog : http://blog.hoegaerden.be/2010/01/10/list-all-ssis-packages-deployed-on-your-integration-server/ and if needed, export : http://technet.microsoft.com/en-us/library/ms137916.aspx

Master : Well, this is the big one. Main challenge here is really permissions, logins and users. And I know we have discussed before, and you say you have scripts.

TempDB : is created automatically every time SQL starts. You will want to configure as appropriate.

Finally, there is the user databases and they can be moved or restored (and do that before fixing users et al).

Now, in terms of "New Instance" is it the exact same version, or, are you "moving" to a new location ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39971401
*laughing* should have refreshed first - I see you have accepted while I was typing.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971463
Mark Wills

ar ah... WOWOW you here today! nice to meet you.

you talk about resource DB too but I can'd find out that post again.

I post this one because this time I find a bit different approach than last time I do an execise.

"*laughing* should have refreshed first - I see you have accepted while I was typing. "

I accepted BEFORE you were typing!! I am forced to use 'before' !!!!!

"Now, in terms of "New Instance" is it the exact same version, or, are you "moving" to a new location ? "

yes, it is a MUST of system dB can't be restore!!

I just have a new DB to test the restore and as I said, I can't see why the restore steps I toke before doesn't works, as the restore path is diff now.

I found this one: http://www.mssqltips.com/sqlservertip/2425/rebuilding-sql-server-on-different-hardware-after-a-failure/ and it is good looking.

as I heard about resource DB( basicaly I still remember you talk about this before as you said be sure to restore the resource DB too), so I come and ask if anything stilll missed, resource DB is the last bit I can recall.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39971554
Yep, still here :)

That link looks pretty good - a rebuild after a failure on different hardware.

Can only restore resource DB via a binary file xcopy (move) or physical backup (as if it were a binary image with other DB's as in a recover disk from external backup device). Normally, don't touch resource DB under any circumstances.

Sounds like you are on top of it.

Cheers,
Mark
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971581
"That link looks pretty good - a rebuild after a failure on different hardware."

yeah, I follow that and except the alter path of the system DB, everything is easy to follow.

"Can only restore resource DB via a binary file xcopy (move) or physical backup (as if it were a binary image with other DB's as in a recover disk from external backup device). Normally, don't touch resource DB under any circumstances."

actually don't understand it very well.  if that one is not useful during server rebuilf or server migration, we don't even need to xcopy that, right? just ignore it, right ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39972502
Not quite. It must match your MASTER and basically lives side by side (well, in the binn folder).

If it is broken or Master has been restored, then you should restore from the same file backup source / version as your master. Or (arguably) try the Repair option in the SQL setup.

But the only "backup" possible is one of those external file backups just like any physical file / disk backup in case your disk / computer dies and you need to restore from backup media  (such as BackupExec).

That does not mean the SQL Backup / Restore - it simply cannot do the resource DB. Think of it as a read only disk file more so than a database (which is why it can be copied at any time).

So, if you are restoring from a physical disk backup you end up copying the Resource DB that goes hand in hand with the MASTER db into the same instance location as master (except master goes to data subfolder and resource goes to binn subfolder). And then, use the repair, and/or reapply all the upgrades / patches / hotfixes to bring it up to date and in sync with master.

It is always a very manual task of copies or patching and often ignored.

But, if you are installing SQL, then the Master and the Resource are perfectly matched because of the "new" install. And then apply any upgrades.

Resource DB contains objects that are available to all other DB's and any updates that happen to Resource are then available to the other databases. Check out the System Views by way of example. They are pretty much physically persisted in the Resource DB and logically available via the sys schema in the other DB's.

That's why it is very important to consider Resource as an extension to Master and make sure you take a security copy after every upgrade or patch or hotfix.

So, in terms of "a rebuild after a failure on different hardware" then don't ignore it.

SQL Server simply wont start without it (or it is incorrectly named, or, not in the expected place for that instance).

Either copy the matching version from wherever you are retrieving the DB's, or use the Repair, or bring it up to date by applying all the patches...

Have a read of : http://blogs.msdn.com/b/vsanil/archive/2012/11/02/resource-database-common-questions.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39977126
"It must match your MASTER and basically lives side by side (well, in the binn folder). "

yeah, I saw that and I am not sure copy it or not !

"If it is broken or Master has been restored, then you should restore from the same file backup source / version as your master. Or (arguably) try the Repair option in the SQL setup. "

and it will restore the resource DB by using master DB?

"But the only "backup" possible is one of those external file backups just like any physical file / disk backup in case your disk / computer dies and you need to restore from backup media  (such as BackupExec)."

yeah, just copy and paste!

"That does not mean the SQL Backup / Restore - it simply cannot do the resource DB. Think of it as a read only disk file more so than a database (which is why it can be copied at any time)."

I am thinking about if it is so important, why MS do not offer the backup option ?

"But, if you are installing SQL, then the Master and the Resource are perfectly matched because of the "new" install. And then apply any upgrades."

no,.... I will restore the master DB, right ? then file copy the resource DB file and run the setup.exe to repair the DB?  then all SP and patches, finally model and MSDB restore followed by all user databases?

"That's why it is very important to consider Resource as an extension to Master and make sure you take a security copy after every upgrade or patch or hotfix."

ok.

"SQL Server simply wont start without it (or it is incorrectly named, or, not in the expected place for that instance).

no, I don't restore the resource DB from the production and it still startable.

"Either copy the matching version from wherever you are retrieving the DB's, or use the Repair, or bring it up to date by applying all the patches..."

I think there is a step problems and I think it should be in this order:

1) install a new SQL server.
2) apply all SP and CU udpate up to the same version as the production server.
3) restore the master DB. (it is alredy the same version as the production one)
4) copy and paste the resouce DB to the respective folder (same as production, e.g.)
5) apply the alter script from the link I post.
6) restore MSDB and MODEL DB.
7) restore all user databases.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now