none
Database Maintenance and Backup

    General discussion

  • I am interested in setting up a reliable maintenance and backup routine for our SQL databases and don't know where to begin.  In most of the links I've followed on the web I've seen that using the Maintnenace wizard is suggested.  However I have read elsewhere that this can be unreliable.  I have spoken casually to folks who wrote their own backup but I haven't been privy to the contents.

     

    So I have a few questions:

     

    Do Master, MSDB,Model,Pubs and tempdb need to be backed up?  If so, is a full backup daily good enough? 

         Can they be part of the same backup plan?

     

    As for our main database:

     

    There is one (inactive) backup routine that says:

     

    DUMP TRANSACTION <databasename> WITH TRUNCATE_ONLY
    DUMP DATABASE <databasename> TO DBBackup WITH  NOUNLOAD ,  STATS = 10,  INIT ,  NOSKIP

     

    What is STATS=10?

     

    There is another (inactive) backup routine that is part of the maintenance plan that says:

     

    XECUTE master.dbo.xp_sqlmaint N'-PlanID EB57C193-F384-4A31-A094-E58713B975C8 -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB "D:\Microsoft SQL Server Data\MSSQL\BACKUP" -DelBkUps 4WEEKS -BkExt "BAK"'

     

    What is the difference between the two?

     

    We would like it if we didn't lose a single transaction should something ever happen to the server. 

    We would also like our .ldf (this is the "logs" right?) file to stop getting so BIG.  It grows to over 30GB but when shrunk is only 5GB

    We would like the database to be healthy and fast.

     

    With other programs I know the wizards don't provide quite the functionality that you can get for yourself if you know your way around the program.  So I wonder is the Maintenance wizard going to keep us in top shape?  If so, what settings do I make to accomplish our goals?

     

    Thanks for the help. 

     

    mwlaz

    • Changed type Shanky_621Moderator Friday, November 08, 2013 10:13 AM Seems user want to discussion about how to begin
    Monday, July 14, 2008 5:23 PM

All replies

  • For newbie’s - database maintenance plans are a great way to start backing up system and user databases.  It is very reliable!!!

    I have included comments below:

    Thanks

    Ross

     

     mwlaz wrote:

    I am interested in setting up a reliable maintenance and backup routine for our SQL databases and don't know where to begin.  In most of the links I've followed on the web I've seen that using the Maintnenace wizard is suggested.  However I have read elsewhere that this can be unreliable.  I have spoken casually to folks who wrote their own backup but I haven't been privy to the contents.

     

    So I have a few questions:

     

    Do Master, MSDB,Model,Pubs and tempdb need to be backed up?  If so, is a full backup daily good enough? 

         Can they be part of the same backup plan?

     

    They can be part of the same backup plan, however, I prefer to keep them seperate.  A full backup on a daily basis is sufficient.  If not back them up when they change.  No need to backup tempDB or sample databases such as PUBS.

     

    As for our main database:

     

    There is one (inactive) backup routine that says:

     

    DUMP TRANSACTION <databasename> WITH TRUNCATE_ONLY
    DUMP DATABASE <databasename> TO DBBackup WITH  NOUNLOAD ,  STATS = 10,  INIT ,  NOSKIP

     

    What is STATS=10?

     

    Here is a listing of all the arguments associated with backups - http://msdn.microsoft.com/en-us/library/ms186865.aspx

     

    STATS [ = percentage ]

    Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

    The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

     

    We would like it if we didn't lose a single transaction should something ever happen to the server. 

    We would also like our .ldf (this is the "logs" right?) file to stop getting so BIG.  It grows to over 30GB but when shrunk is only 5GB

    We would like the database to be healthy and fast.

     

    Ensure the recovery model is set to full - place database and transaction logs on seperate volumes and conduct a Full and Transaction Logs Backups

     

    Monday, July 14, 2008 6:43 PM
  •  

    The Master database contains your server settings (things like logins, server wide permissions, user database info...), so, if you want to save yourself some headache while recovering from a disaster (or migrating to another box), make sure you have a good backup. Put in mind that the master database doesn't usually change as frequently as the user database. TempDB is deleted and recreated everytime you restart SQL server, so it is a waste to back it. Also, TempDB is mostly used for internal DB operations by SQL Server instance but some programmers also use is for their #Temps tables though this appoach is considered bad programming practice. All other System DBs need to be backedup as well.

     

    the DUMP statement you refered to, was probably used in your OLD SQL server and was deactivated when migrated to a newer version of SQL server. DUMP is included for backward compatiblity only and is not suppored in SQL 2008. Use BACKUP statement instead.

    http://msdn.microsoft.com/en-us/library/ms187315.aspx

     

     

    xp_sqlmaint is an extended stored procedure that execute your maintenance plan. both commands backup your DB, the second way is part of the maintenance plan. I too use maintenance plans to backup one of my DBs. they work fine.

     

    well, 30GB is nothing these days, just move your transaction log to a different disk array (preferably RAID 1 because they provide better write performance as well as fault tollerance), and trunc it at night with your backup job. also, you can do the following to ensure minimum data loss:

    1- backup your log regularly

    2- use replication or log shipping

    3- all non-committed transactions will be lost in case of a server restart (to ensure integrity). However, if your hardware is not setup correclty, you might lose some committed transactions in case of a power outage. Make sure your RAID controller got the samll backup battery installed.

    4- to make sure the database is healthy and fast is the hope of every DBA. there are many factors a DBA cannot control, like database design, stored procedure designs, datalayer data access approach. what you can do is to monitor performance and report bad performing queries to the programmers. Tuning query and optemizing a database is an ART as well as science.

     

    Monday, July 14, 2008 7:31 PM
  • Guys, thanks very much for the answers.  I understood a lot of what you said and am very happy for the detailed information but still have a few questions. 

     

    We don't have a separate RAID 1 array to move the logs to yet.  I've got tons of room on my RAID 5 array, but the OS is on the only other array.  Next server (hopefully coming soon) will have 2 controllers and 3 arrays (RAID 1, RAID 1, RAID 5, maybe RAID 10 - can't find a definitive answer about which is better). 

     

    How do I "Ensure the recovery model is set to full - place database and transaction logs on seperate volumes and conduct a Full and Transaction Logs Backups" I'm not sure where to look for the recovery model setting. 

     

    Where are the database logs stored?  I think the Transaction logs are the .ldf files, right?  Those are supposed to go on the different array.  What are the database logs and should I be backing them up?

     

    What is a 'Full and Transaction Logs Backup"?  Is it a Full database backup and a full transaction logs backup?

     

    Where in the Database Maintenance Plan Wizard am I given the option to truncate the transaction logs?

    How often should I be backing up the log?  I thought every 10 minutes was pretty good.  Is that OK?

    What is log shipping?

    What do you mean by "Make sure your RAID controller has the small (?) backup battery installed?  I have the server plugged into a hefty UPS, isn't that enough?

     

    Couple more questions specific to the Database Maintenance Plan Wizard:

     

    How do I decide what the free space per page percentage should be?

    How do I know how big the database should get before it needs to be shrunk?

    How much free space should I leave after the shrink?

     

    Do I want to attempt to repair minor problems when checking database integrity?

    Should the Integrity check be done before backing up he database or transaction log?

     

    I don't really understand how the database will be restored should it ever need to be.  I get that it will be restored from the backup but I'm learning that the Transaction Logs play a roll too.  The backup is done daily, right (since there are no incremental or differential backups it seems) - so I stand to lose a day's work if I restore from the backup.  The logs must be used to fill in the gaps between the last backup and the time of failure, right?  How does one restore the logs - or do I want to know??

     

    Thanks for your help so far.  I know I have a lot of questions AND I know I need a SQL "guy" but . . . well the "but" is a long story so let's just say I appreciate your help.

     

    mwlaz

    Tuesday, July 15, 2008 10:08 PM
  • To view or change the recovery model of a database

    1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

    2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

    3. Right-click the database, and then click Properties, which opens the Database Properties dialog box.

    4. In the Select a Page pane, click Options.

    5. The current recovery model is displayed in the Recovery model list box.

    6. Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.

     

     Read this article to understand the different recovery models.  Basically Full allows you to restore to the point of failure as it take transation logs into concideration where Simple, lets you restore to the last known successful backup

     

    Choosing the Recovery Model for a database

    http://msdn.microsoft.com/en-us/library/ms175987.aspx

     

    Full Backups

     

    http://msdn.microsoft.com/en-us/library/ms186289.aspx?n=0

     

    Transaction Log Backups

     

    http://msdn.microsoft.com/en-us/library/ms190440.aspx?n=0

    Tuesday, July 15, 2008 10:18 PM
  • How to: Back Up a Database (SQL Server Management Studio)

     

    http://msdn.microsoft.com/en-us/library/ms187510.aspx

     

    Tuesday, July 15, 2008 10:23 PM
  • in summary:

    SQL provides 3 built-in Database backup types: FULL, Differencial, and Transaction Log backup. Full will backup everything, Diff will backup the changes since the last full backup, finally Trans Log will backup the transaction log. If you chose Trans Log backup, you can also choose to Trunc the log after the backup succeeds (removing the inactive part of the log). In management Studio, Right click your DB-->All tasks-->Backup under type choose Trans Log then go to options and under Trans Log you will find the option to tranc the log. You can also right T-SQL to automate the job (the maintenance plan will create one for you). You can also use "WITH TRUNCATE_ONLY" option to trunc the log without actual backup. refere to documentation for more info.

     

    Disk Size: in general you hit an alerting point when your Disk is 80% full. some claims the performance is degraded after 50% full.

     

    RAID controller Battery: is not your Server UPS. the way RAID controllers work, is that they cach committed data to their built-in RAM first before they write it to disk. this increase performance, however, in rare occasions it may cause data loss in case of power failure. This is a problem because to SQL server the data was committed but the RAID controller couldn't physically write it to disk. the work around, is to install an optional battery on the RAID controller itself. I believe all RAIDs come with this battery these days but it won't hurt to double check.

     

    Restore has many options too. you can do a full restore from one backup (restore with recovery). more common, you do Full restore by using mulitiple backups: First you apply your full back then you apply your transaction log backups in order (restore with norecovery) until you get to the point where you apply your last transaction log backup, you chose restore with recovery.

    You can also recover to a point of time.

     

    The best backup practice is a full backup at night and a transaction log backup every...lets say 30 min (depends on transactions)

     

     

    Tuesday, July 15, 2008 11:07 PM