March 23, 2010 at 6:23 am
Currently we have a server which is on very shaky grounds. I am ensuring taking full backups daily along with transactional backups hourly. My biggest concern is the system DB's. I have scripted out the jobs, users, and linked servers. Is there anything else I should do to be proactive in case this server soils it's linens??
March 23, 2010 at 6:30 am
What do you mean by 'shaky ground'? What's wrong with the server?
Are you backing up the system databases (master, model, msdb)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2010 at 6:35 am
Yes. daily backup of system db's. Ever since we cut over from Hitachi SAN to EMC, our cluster blue screened and needed to be restored from a previous build. Since then there is more blood in the event log then imaginable and server is very unstable. It's an OS problem, not SQL but I wanted to ensure I am covered. Is there anything in the master database that is not easily restored?
March 23, 2010 at 6:40 am
How about attending to the root problem instead of just preparing for when it fails?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2010 at 6:42 am
Server team is dragging their feet regarding the issue. I am ensuring CYA.
March 23, 2010 at 8:05 am
when you say users I presume you mean logins.
do you have user defined error messages, user stored procedures held in master? Details of user default databases, default language, server roles granted?
Ensure you are able to quickly rebuild the server, so have the software available, make sure you know the exact release version, service account passwords and drive layout. I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource.
If a fix does not look to be forthcoming soon any chance of setting up a DR server via logshipping or mirroring?
---------------------------------------------------------------------
March 23, 2010 at 11:43 am
George: "I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource."
kwilt: Sorry to step in on someone else's post requesting help, but could you explain how to create flat file copies of system databases? I've not heard of this approach. Is this something that should be routinely done?
March 23, 2010 at 1:29 pm
I'm not sure how you saved out your users, but you might want to take a look at sp_help_revlogin. It scripts them out keeping the same SID (avoiding orphaned users), and the passwords are hashed, so it's secure.
Have you scripted out the user roles?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 1:35 pm
Yep Wayne. That is what I always use when moving users, especially local ones! Thanks!
March 23, 2010 at 1:38 pm
kwilt (3/23/2010)
George: "I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource."kwilt: Sorry to step in on someone else's post requesting help, but could you explain how to create flat file copies of system databases? I've not heard of this approach. Is this something that should be routinely done?
simply stop SQL and copy the system database files off to another location (not tempdb). Then if the master database in particular is ever corrupted you can slide the copy back into place to get SQL up again without having to do a master rebuild. You should do this every time you patch the server, no need to do more often that that.
For the mssqlsystemresource database this is the only way you can get a 'backup' of it.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply