January 7, 2009 at 11:12 pm
Hi there
This is first time i am installing service pack for DB server. after taking DB role in my company
pls help me with following questions
1) Do i need to inform downtime while installing SP3 on DB server?
2) Do i need to restart the DB server?
3) Should i send email to all people to install SP3 on their local SQL-Server?
4) Is it a normal click through installation or i need to any specific things? e.g. (take backup etc)
Cheers
January 8, 2009 at 12:47 pm
This is just me, but
1) Do i need to inform downtime while installing SP3 on DB server?
YES
2) Do i need to restart the DB server?
YES
3) Should i send email to all people to install SP3 on their local SQL-Server?
NO. Test SP3 on test servers before mass-upgrade
4) Is it a normal click through installation or i need to any specific things? e.g. (take backup etc)
YES, but you should backup everything (system DB, user DB, logins, etc....) just in case
January 10, 2009 at 11:59 am
I second everything that Jerry said. Anytime you are making changes to your SQL Server you should backup everything. Don't forget the resource database as well. See this article: http://www.mssqltips.com/tip.asp?tip=1544
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 10, 2009 at 1:16 pm
Jack Corbett (1/10/2009)
I second everything that Jerry said. Anytime you are making changes to your SQL Server you should backup everything. Don't forget the resource database as well. See this article: http://www.mssqltips.com/tip.asp?tip=1544
Nice info Jack, but it is interesting if you create the maintenance plane for the Backup DBs in SQL Server 2005 and when you select to backup the system DBs, the Resource Database is not in list, till it is in the System DBs familly!
January 10, 2009 at 1:26 pm
Dugi (1/10/2009)
Jack Corbett (1/10/2009)
I second everything that Jerry said. Anytime you are making changes to your SQL Server you should backup everything. Don't forget the resource database as well. See this article: http://www.mssqltips.com/tip.asp?tip=1544Nice info Jack, but it is interesting if you create the maintenance plane for the Backup DBs in SQL Server 2005 and when you select to backup the system DBs, the Resource Database is not in list, till it is in the System DDs familly!
thats because MS don't want you touching it. You have shut SQL down and just do a file copy. Top tip, whenever you do an upgrade, whilst you've got exclusive use of the instance, stop SQL one more time after the upgrade\install has finished, and copy all the system db files (except tempdb) off to another directory. Lot easier to just copy those files back then do a master rebuild should system dbs be lost\corrupted. Belt and braces and all that.
---------------------------------------------------------------------
January 10, 2009 at 1:41 pm
george sibbald
thats because MS don't want you touching it
Till we are discussing for the System DBs, also the Master DB is very important and I'm sure that it is much important then Resource Database so when you select the DB backup only for sys DBs is an automatically bck for all DBs why not also for Resource DB same as others (Master, msdb, model). During this proces we are not tauching the DB such as copy-paste the mdf,ldf files!
January 10, 2009 at 1:56 pm
Master is not more important than the resource database, as the instance will not start without the resource database. The fact that it is hidden should tip you off to it's importance.
You cannot back it up because it is not visible so the BACKUP command does not recognize it as a valid database name.
Also because it is read-only and only changes when you do a service pack or update, copying it immediately after an install or upgrade is all you need, while master, model, and msdb need regular backups because they DO change.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 10, 2009 at 1:56 pm
Dugi, see this:
http://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx
the resource database could be said to be more important than master as it contains all the system objects. SQL won't work without either of them anyway.
Why can't we back it up,? I guess its all part of this database being central to the operating of the instance and there is no need for anyone to do anything with it. If you can back it up someone might be tempted to restore it.
Its also read only and never changes. Even when upgrading it is overwritten rather than being updated.
---------------------------------------------------------------------
January 10, 2009 at 2:11 pm
I didn't read in deep for the Resource DB, I just was wondering why the other Sys DBs are available for BCK and RDB is not ! Anyway thnx my friends for much info and for me is clear everything now, I appreciate that!
😎
January 20, 2009 at 6:37 am
Dugi (1/10/2009)
I didn't read in deep for the Resource DB, I just was wondering why the other Sys DBs are available for BCK and RDB is not ! Anyway thnx my friends for much info and for me is clear everything now, I appreciate that!😎
Hopping in late ....
The KB ref provided earlier contains the info.
Basically the RDb is a read only db containing all definitions for "system" stuff.
A simple file copy is enough to copy it for backup resource. Keep in mind it is best to shut down the instance before this copy.
A copy should be taken with each SP/CU/HF you apply.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2009 at 3:42 pm
Hi All,
Has anyone actually tried rolling back to the SP2 after installing SP3.
I tried it on my local PC and it did not work.
According the re article quoted earlier: http://www.mssqltips.com/tip.asp?tip=1544
1) you should be able to apply SP3 to the second named instance after you upgrade one instance by simply copying the RDB files.
2) You should be able to roll back to previous version SP2 by copying old (prior to upgrade files) the mdf and ldf files on top of the new updated files and you are done...
I tried both and it did not work.
For item #2) I stopped sql server, copied mdf and ldf files of all system dbs including mssqlsystemresource.mdf and.ldf. I then applied the SP3. I then tried to roll back by copying the "old" pre_update mdf and ldf files on top of the post_update files, restarted PC and no luck. It still shows version 4035 when i do select @@version.
Did i do something wrong?
Thank you
O
January 21, 2009 at 3:59 pm
Hi,
One more question:
If I have to roll back to the SP2 according to Microsoft i nave to re-install SQL server 2005 and then restore all system dbs, user db...is there anything else that i need to do?
What else should be backed up prior to the upgrade?
Server settings? logins? Is there an easy way to back this all up?
thank you very much:)
Golova
January 22, 2009 at 12:44 am
Golova (1/21/2009)
Hi,One more question:
If I have to roll back to the SP2 according to Microsoft i nave to re-install SQL server 2005 and then restore all system dbs, user db...is there anything else that i need to do?
What else should be backed up prior to the upgrade?
Server settings? logins? Is there an easy way to back this all up?
thank you very much:)
Golova
I think there is more to a service pack than only the RDB, so I wouldn't be confident in just copying back a previous version of it.
(why would a sp application to a second instance take more than 10 minutes if it only needed to copy the RDB)
As with any sp/cu/hf I advise to make a full backup of all your databases before applying the update(s).
My fallback scenario is a fresh install up to the software level I started from (I keep a weekly inventory using SMO) and then restore all databases. The RDB will then also be in its correct state of content.
When planning for an upgrade, don't just plan for success, but plan for DRP ! If the actual update process (backup before, upgrade, backup after, db maintenance,.. would need 30 minutes, at least request the x flod, just in case ....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2009 at 3:33 pm
just to share with you all. I checked on test servres and found that The SQL 2005 sp3 backup is getting restored on SQL Server 2005 sp2.
Not Sure if this one is intened behaviour.
September 11, 2009 at 12:02 am
Within the same major version of sqlserver a restore should never be an issue for user databases.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply