SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DBAdmin toolbox - Part 2: Verify all databases in the enterprise


SQL DBAdmin toolbox - Part 2: Verify all databases in the enterprise

Author
Message
Infostar
Infostar
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 237
Comments posted to this topic are about the item SQL DBAdmin toolbox - Part 2: Verify all databases in the enterprise
William Soranno
William Soranno
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 519
The article is interesting. I have an issue with the code.
I copied it to my clipboard.
When I pasted it in to a new query it looks like this:



I even pasted the code into notepad and then copied and pasted with the same results.

Any suggestions?

Thanks

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Infostar
Infostar
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 237
Hi,

Thanks for response.

I had some problem with the code submit and repost it afterwards did no good. I could send you the script if you could tel me your email address.

Best regards

Zhong Yu
zhong.yu(at)b3it.se
William Soranno
William Soranno
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 519
Zhong,
I was able to do a find and replace for the lines that appeared in the code.

I am in the process of going thru the procedures to understand what they do.
I have been working on a process to do the automated restores to verify the backups.
It looks like your code will help move thing along.

Thank you for sharing your code.

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
William Soranno
William Soranno
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 519
Zhong,
I ran your process for one of my smaller sql servers and it worked great.

I have a question for you.
In the RestoreConfig table you have two columns, DatabaseName and DBExclude, that are not used. Are they for something in the future?

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Infostar
Infostar
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 237
Hi Bill,

I am glad that you found the script useful.

It is planned to have a third level, database level, that can be excluded from verification or have different restore path.

One thing worth to mention. Master databases restored as normal database always fail at dbcc check, so they are not checked. Master database från SQL2000 cannot be recovered in SQL2008R2 (at least) but this situation is not handeled. Successful verification of system databases as normal database is in no way an assurance that they can be restored as system database in the right SQL version.
SanDroid
SanDroid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 1046
It is a DBA responsibility to verify that database backups can be successfully restored but many DBAs (the majority ?) fail to do that.

This statement has not been true for many years now.

Every enterprise environment I know of has ALL backup creation, storage, retreival, and restores supported by employees outside of the Database or DBA team.
In Enterprise environments most DBA's wont have direct access to the application that creates the backup files or the place where they are stored. They also will not be able to restore the files.

Data theft or loss of personal and sensitive information through Backup files has become so out of hand that this has become the standard.

Its still a DBA's responsibility to make sure the database can be restored to a state where the data is valid and current.
This is validation is usually a checklist that is turned in after the DBA has validated the database schema and data after the restore is completed.
Usually these restores are only done during a refresh from the Production servers to the UAT servers, or during as DR exercise. I like the way this article uses that perspective.
I have never been in a DR exercise where the SQL server was restored and the msdb or master database was restored from a recent backup.
Cool

I would recomend and add to this that in Todays environment it is every DBA's responsibility to have a plan for restoring the system databases to a state where they can support the databases you are responsible for. In other words, you need to be able to get your databases working no matter where the Backup or Systems team restores them.
Infostar
Infostar
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 237
SanDroid

Thanks for your comments. However the way enterprises organize their IT as you described is definitively not my experience. It seems that those enterprises come to a conclusion that its own employees are bigger security risks than employees in other companies. I work in Europe there may be some cultural differences between continents. If disaster happens and it turns out that the backups are corrupted, who will lose his job? The DBA who never had a chance to verify backups or the company who don't know or don't care how his client's system work?
SanDroid
SanDroid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 1046
Infostar (6/21/2011)
SanDroid
Thanks for your comments. However the way enterprises organize their IT as you described is definitively not my experience. It seems that those enterprises come to a conclusion that its own employees are bigger security risks than employees in other companies. I work in Europe there may be some cultural differences between continents.

I would say these are more Governmental than cultural differances.
There are many regulations that compnaies here have to follow for data security.
If that company is pulicly owned and thier stock is traded, then these regulations apply to them. If the data stored is Health Industry data, it is regulated even more.
The company I work for in America is Japanese owned with global offices. They have to comply to JSOX audits for all business that is done in the US, or that could effect the earnings they report to the SEC.
No matter where you live unauthorized access to sensitive data by employees is still listed in the top ten reasons for corporate litigation. This was true when data was stored on paper. As IT has made data access easier it has become even more true.

If disaster happens and it turns out that the backups are corrupted, who will lose his job? The DBA who never had a chance to verify backups or the company who don't know or don't care how his client's system work?

This all depends on what the Auditors say. In most ITIL and SOX/JSOX shops restoring a server to the state of the last backup cycly would be the systems group. I have never seen an audit failure that was one persons responsibility. Usualy there are several involved in what caused the failure.
PS: You may have heard about ENRON and Author Anderson...
They had a problem with corporate culture and US laws and regulations also.
Most of the current laws and regulations that affect IT came from the lessons learned from that multi billion dollar disaster. As far as I know, everyone at both those companies lost their jobs. For some reason your question made me think of that.
Infostar
Infostar
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 237

This all depends on what the Auditors say. In most ITIL and SOX/JSOX shops restoring a server to the state of the last backup cycly would be the systems group. I have never seen an audit failure that was one persons responsibility. Usualy there are several involved in what caused the failure.
PS: You may have heard about ENRON and Author Anderson...
They had a problem with corporate culture and US laws and regulations also.
Most of the current laws and regulations that affect IT came from the lessons learned from that multi billion dollar disaster. As far as I know, everyone at both those companies lost their jobs. For some reason your question made me think of that.


I do not know details of how Enron and Author Anderson crashed, but I would be very supprised if it was because some illoyal actions of some employees in IT department. That kind of disaster is usually caused by poor management or data manipulation by the highest corporate level.

In all the organisations I worked and am working, DBAs are the sole formal authorized persons to the SQL database data as sysadmin. Although developers often get access because of convienence, exceptions must then be granted for them. OS System engineers should no have access to data either. That's why Microsoft removed builtin administrators from sysadmin role in SQL server since SQL2005 SP1.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search