Post reply

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

  • June 20, 2011 at 11:19 pm

    #239629

    Comments posted to this topic are about the item SQL DBAdmin toolbox - Part 2: Verify all databases in the enterprise

  • June 21, 2011 at 6:18 am

    #1341782

    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

  • June 21, 2011 at 7:11 am

    #1341819

    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

  • June 21, 2011 at 7:17 am

    #1341823

    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

  • June 21, 2011 at 9:17 am

    #1341911

    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

  • June 21, 2011 at 10:00 am

    #1341927

    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.

  • June 21, 2011 at 11:57 am

    #1342011

    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.

    😎

    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.

  • June 21, 2011 at 12:36 pm

    #1342047

    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?

  • June 21, 2011 at 1:11 pm

    #1342073

    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.

  • June 22, 2011 at 8:03 am

    #1342401

    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.

  • June 30, 2011 at 3:52 pm

    #1346798

    From my experience, at bigger organizations such as hospitals, it is the system administrators that are responsible for backups. Just like they are responsibile for all backups...Outlook, file shares. To me its hard to justify making an exception for SQL files to be the responsibility of the DBA just because their title is DBA. DBAs shouldn't have access to files like system admins do. Why would a DBA be given access to a backup tool like Backup Exec when the system administrators already use that tool for all other backups?

    At a smaller shop the rules change quite a bit as normally a DBA is a jack of all trades that performs heroic effects from advanced .NET programming to DBA work to project management and BI(and then the sales people reap the commision benefits of course).

  • July 14, 2011 at 7:28 am

    #1352818

    Try doing a DBA job within the financially constrained defence sector.

    Oh for the luxury :hehe: of being able to have "a dedicated SQL server with highest SQL version in the enterprise", "enough disk space to restore the largest database" and "all the backups in a central network share" :laugh:

    The impossible can be done at once, miracles may take a little longer 🙂

  • July 20, 2011 at 6:58 am

    #1355551

    "the financially constrained defence sector."

    I did not know such exists. Which country are you from? This must be a state secret. 🙂

    I really don't recognize the situiation that a company would have problems to have a dedicated SQL server for backup verification with large enough disks or have a centralized backup storage. I am interested to know how is the backup configured with you.

  • August 10, 2011 at 2:40 am

    #1366426

    I am really disappointed that the discussion has been focused whether database backup and the integrity of the backup files are the responsibility of DBA, instead of how the script can be improved.

    I am also a little shocked that it seems many of DBAs have the opionion that the backup is somebody else's business, while I have never met one in my career as DBA. There must be different worlds of DBAs,

    Personaly I do not think the critics hold. The security argument that DBA should not have access to data, the tools argument that backup is done using tools that DBA have no control and knowledge are all invalid to me. The only reason that backup is not done in many organization is the cost / benefit argument. It is too costly to manually verify integrity of backups and you will almost always get confirmed that the backups are ok. Yet people always maintain that "you don't have a backup tills you verified you can restore from it".

    My scripts make it possible to reduce the costs and security risk of doing verification. It is designed to run automaticaly in a secured box. You can get informed of the results by email. The scripts currently only verify native SQL backup to the disk. But it is possible to develop to verify other type of devices.

    Since the script is also getting very low rating, I decide to discontinue publish of the toolbox scripts. It also saves me from my dilemma since many scripts especially the performance tuning part are from other DBAs, and I cannot present them here according to the rules. Anyone interested in exchange of administrative dba scripts and ideas can email me.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply