Post reply

database maintenance - best practice

  • November 24, 2011 at 6:25 am

    #263390

    Hi

    We have sql 2008 server with 2 instances. One for production and one for testing. Both instances has multiple databases. We have a maintenance job running everyday to do a full backup of database. We don't have a true db admin here. Part of my job is to deal with these databases.

    Lately, I am trying to find if there is any best practice articles to handle the server and databases. Please let me know if you come across or have any suggestions...

    My goal is not to surprise ourselves in case of any crashes... 🙁

    thx, Ted.

  • November 24, 2011 at 6:36 am

    #1412678

    Ted

    In the event of a crash, what is the largest amount of data (in days, hours and minutes) that you can afford to lose? Do you need to be able to do a point-in-time recovery, or will going back to the last full backup be sufficient? Your answers to those questions will dictate your backup strategy. You may also wish to read about DBCC CHECKDB, and you'll want to consider testing that you can actually restore a sample of your databases.

    John

  • November 24, 2011 at 7:02 am

    #1412691

    article that is what you need to know...about SQL Server Maintenance

    http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx

  • November 24, 2011 at 7:23 am

    #1412700

    hi,

    Thank you for your reply. It is not a must to be 24/7 type of operation here. We prefer to be able to retrieve data up to the minutes but it is not a must. Is there a step to step guide for this. Our sql server is setup since last summer, we are able to restore full backup.

    Also, thank you for the link, i will go through it.

    thx, Ted.

  • November 24, 2011 at 7:59 am

    #1412710

    If you're new to SQL Server then use SQL Server Management Studio (SSMS) GUI to point-and-click your way through the configuration. Open SSMS, connect to the database instance (by default same as server name). In Object Explorer, expand Management and then Maintenance Plans, right-click and New Maintenance Plan. Use the Toolbox window on the left and drag/drop your desired backup jobs into the subplan - create new subplans if necessary. Double click on the object to edit the settings. Once done, and scheduled, find the Job in the SQL Server Agent tree on the left and Start Job At Step... to test. It sounds complex but easy when you see it.

    The previous poster was right in saying it depends on the application needs when considering your backup strategy. The London Stock Exchange's backup strategy is going to be far more demanding than the database back-end for Norma's Cake Shop. Just consider that if you have to restore the database, having a full set of backups & transaction logs is going to give you much more choice than if you didn't.

    Graze TechNet / MSDN and the articles/stairways on this site for more help.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • December 1, 2011 at 3:55 am

    #1415123

    Make sure DB Backups are in place...Which might help in case of crash.

    If Possible use some high avilability approach like clustering or mirroring.


    - Pradyothana     http://www.msqlserver.com

Viewing 6 posts - 1 through 5 (of 5 total)

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