September 11, 2009 at 12:14 pm
Hi,
What is the best method to backup Resource Database? Does someone already has a script for it?
I just realize that this databases is not taken in backup.
Thanks,
September 11, 2009 at 12:24 pm
You don't really need to back this up. It's a standard database built for a particular patch level of the server. So every server that is RTM has the same Resource database. The same thing for SP1, SP2, etc.
That being said, you can back it up as a file copy if SQL Server is shut down. Here's the page at MSDN
September 11, 2009 at 1:57 pm
But you can run DBCC checkdb on master database and it perform tests on resource database too. I hope this will help you to keep your resource in a good condition.
September 12, 2009 at 12:09 am
Rem (9/11/2009)
Hi,What is the best method to backup Resource Database? Does someone already has a script for it?
I just realize that this databases is not taken in backup.
Thanks,
It doesn't have to be backed up as the data doesn't change in it. The only time when it changes is when you install service packs, it has information about system objects.
September 12, 2009 at 8:04 am
Krishna (9/12/2009)
Rem (9/11/2009)
Hi,What is the best method to backup Resource Database? Does someone already has a script for it?
I just realize that this databases is not taken in backup.
Thanks,
It doesn't have to be backed up as the data doesn't change in it. The only time when it changes is when you install service packs, it has information about system objects.
nice to have a copy of it though in case something happens to the original. Also saves having to re-apply service packsd if you have to run rebuild master.
---------------------------------------------------------------------
September 15, 2009 at 3:21 am
george sibbald (9/12/2009)
nice to have a copy of it though in case something happens to the original. Also saves having to re-apply service packsd if you have to run rebuild master.
i read the posted info like this: if you install sql server you have a resource db. if your server crashes so "hard" that you loose certain system db's you probably end up in a reinstall anyway. and with a reinstall the resource db is included again. and with service packes the resource db is also upgraded.
this is not comparable to the master db. you get one too but that one will be different after a (re)install.
September 15, 2009 at 3:43 am
Eduard (9/15/2009)
george sibbald (9/12/2009)
nice to have a copy of it though in case something happens to the original. Also saves having to re-apply service packs if you have to run rebuild master.i read the posted info like this: if you install sql server you have a resource db. if your server crashes so "hard" that you loose certain system db's you probably end up in a reinstall anyway. and with a reinstall the resource db is included again. and with service packs the resource db is also upgraded.
this is not comparable to the master db. you get one too but that one will be different after a (re)install.
Dangerous way of thinking. SQL needs the resource database to function, so have a copy of it. What do you prefer, sliding a copy of it back into place or doing a reinstall?
In fact if you have flat file copies of all your system database files you will never have to do a rebuild master again, just slide the files into place and restore your latest system database backups.
If you run the rebuild master options of setup it will install the base version and then tell you to reinstall your service packs. I have seen a number of posts where people have had problems when reapplying the service packs after a rebuild master. I have found that if you have your latest resource database in place the reinstall of the service packs is not required, saving some grief.
---------------------------------------------------------------------
September 15, 2009 at 5:09 am
Hi All,
I have gone through this post regarding resource database. I remember once I have tried to regress the service pack by applying old copy of resource database but it didn’t work?
Because as per my knowledge resource database keeps track for SPs and you can regress your SP levels by reapplying backup copy of resource database.
Does anyone know about it?
Thanks,
September 15, 2009 at 5:19 am
DKG, that does not work, info on SP levels are kept in other places e.g. the registry.
If you need to regress to a previous level in SQL2005 the only option is uninstall\reinstall I am afraid.
---------------------------------------------------------------------
September 15, 2009 at 7:48 am
The resource database tracks system objects at a particular SP level. The actual DLLs and SQLServer.exe are changed with the SPs, so you would need to uninstall/reinstall, as George mentioned.
September 15, 2009 at 8:02 am
george sibbald (9/15/2009)
Dangerous way of thinking. SQL needs the resource database to function, so have a copy of it. What do you prefer, sliding a copy of it back into place or doing a reinstall?
against what scenario is your flat copy of the resourcedb protecting you ? cause in a couple of scenarios i can think off you end up with a reinstall anyway (disk corruption or failure with upgrade). the resource db is only changed during upgrades. other then that it's a static file.
from the resource db info in bol sql 2005:
Backing Up and Restoring the Resource Database
SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
so in case you use a wrong version of the resource db you may even cause problems with your own copy.
September 15, 2009 at 8:17 am
Eduard, it protects you against loss of the resource db only. thats one scenario, and you only need one.
agreed, it only changes when upgrades are applied, so thats the only time you take a copy of it. Its like any other read only user database, you don't need constant backups but you do need A backup.
As for using the wrong version, you put proper procedures in place, copy the file to a directory that makes it clear which version it is. You can also tell from the timestamp on the files.
If you don't want to do copies of the resourcedb that is up to you, but I will do so and I advise anyone else to do so.
---------------------------------------------------------------------
September 15, 2009 at 8:23 am
i wondered about what i missed. i learned some time ago that you (as in me) can think you know a lot ... but later to learn you (me again) there is always more to learn
i'm also interested in the situation about the troubles you had with the master db. in case you want to share that. i made a seperate post about it to ask in general about it. our current server environment has the luxury problem of not having serious issues for 2+ years now (not talking about tuning).
September 15, 2009 at 8:24 am
I haven't seen anyone reporting issues with Resource being corrupt and needing to restore. Since you can just copy the same DB from any instance back to your server if they're the same patch level, I'm not sure a backup is needed, but it certainly can't hurt.
September 15, 2009 at 9:57 am
A hardware level corruption is as likely to hit the resource db as any other, and someone deleting it is not out of the question. You could copy it from another instance, but that presumes you have another instance at the same level, a small shop might not, so might as well have your own backup on the server you are working on.
If it saves you from one reinstall or one rebuild master its got to be worth it. Its certainly a lot quicker than rebuild master (and we want to recover from a disaster as quickly as possible) and a lot more reliable. I have had rebuild masters fail and its a real pain. Also it now requires you to re-apply service packs if the resource database is not present beforehand and I have seen reports of this failing, most likely because the executables are already at SPn even if the system databases are not.
So as steve says it cannot hurt, and it might help a lot.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply