SQL Server Performance

Db back ups

Discussion in 'General DBA Questions' started by PattyLand, Dec 10, 2003.

  1. PattyLand New Member

    Our NT administrator says that we shouldnt bother with routine back ups via SQL Server. That he backs up the all the files (Sql mdf adn ldf) on the drive server. I dont think this that is a good solution. Can he still perform point in time recovery? Can some one give me the pros and cons of using his stategy, so I can propose that we use standard back ups liek through Db maintenance plans or the Agent.

    Thanks
  2. Luis Martin Moderator

    Let see if I follow.
    1) NT administrator made backups of all files, include SQL files on drive server, same server than SQL Server are?.
    2) There is no incremental or differencial Backup, using log?.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. PattyLand New Member

    So is that the only difference is that he can not perform defferntial backups?

    Thanks
  4. Luis Martin Moderator

    Well, only and critical if you want to be online asap with mimimal information lost in case of crash.

    Plus having backups, that way or via Jobs, on same drive same server, is to risk.





    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. PattyLand New Member

    Can you please be kind enough to expalin why.

    Thanks
  6. ChrisFretwell New Member

    This is a good, basic article on recovery strategies, which is why you take backups and helps you determine how much data you can afford to lose.

    http://databases.about.com/library/weekly/aa031101a.htm

    And I found this tiny little tidbit in my SQL 2000 Admin training kit (for the exam)

    "Note: You must use SQL server 20000 or third party database backup programs to backup online database files. Windows 2000 and NT backups cannot back up files that are in use and for this reason cannot backup online SQL server 2000 database files."

    As for why you copy your backups or make your backups elsewhere is in the case when the server fails. If all copies of your database are on the server, then they are useless. Ditto for drive failures etc.

    Does that help?
    Chris
  7. PattyLand New Member

    Yes thanks.

    Thanks
  8. satya Moderator

    One of the most important tasks you will face as a DBA is performing backups. Although backups are certainly not the most interesting part of the job, they are probably the single most important. If something goes wrong it's the DBA's job to get the server back up and running as quickly as possible. Loss of productivity or, even worse, loss of data can be very expensive for a company.

    Don't care about any other alternatives/application on OS level, but make sure your ground is clear and firm by scheduling regular backups.

    Its a part of best practice by any DBA....

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. PattyLand New Member

    Let know if this plan will work.

    Db back up are every day every 6 hours from 9am - 9pm
    9am 3pm 9pm
    Transaction log back ups are every day every 3 hours from 10am - 10am
    10am 1pm 4pm 7pm and 10pm

    I back up to a different server and to tapes I have a new tape for each day. Every time I switch tapes in the morning the job rewrites all data on the tapes performs the back ups.

    Do you think this is a good plan. Db back ups take about 10min and so do transacion logs.


    Thanks
  10. Luis Martin Moderator

    Indeed, this is a good plan.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  11. PattyLand New Member

    I´ve been testing everything and it works find. Exept that everytime I run the job it fails in one of the steps (backups) I configured it so it will retry when it retries it succeeds, is this normal with tape backups? Here is the error

    Executed as user: dbAdministrator. Read on 'Tapebackups' failed, status = 1117. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3203) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)

    Thanks
  12. Luis Martin Moderator

    I find this in BOL

    Help with Backup and Restore
    The online Backup and Restore Troubleshooter is designed to help you resolve problems you may encounter when backing up or restoring databases and transaction logs in Microsoft® SQL Server™ 2000.



    Note For international users of SQL Server Books Online, the text of the problems as well as the references to Knowledge Base articles appear in English and can be found on nonlocalized Web sites.


    Common starting points for troubleshooting backup and restore include:

    Problems with backing up or restoring a database or transaction log that is password protected.


    Problems appending or initializing a backup to an existing backup device.


    Problems when backing up a transaction log or performing a differential file or file group backup while the database is set to the 'SIMPLE' recovery mode.


    Poor performance when performing a backup or restore.


    The database restore completes successfully, but the backup appears to have restored the database to a point in time earlier than expected.


    Problems performing a point-in-time restore of a transaction log.


    System database backups cannot be restored on a different build of SQL Server.


    Error 927 when attempting to use a database after a successful restore.


    Error 3201.


    Error 3203.


    Error 3024.


    Error 3038.


    Error 3101.


    Error 3108.


    Error 3168.


    Error 3141.


    Error 3159.


    Error 4330.


    Errors 1834 and 3156.


    Errors 4326 and 4305.
    To access troubleshooters online, see Viewing Online Troubleshooters and other PSS Web-Based Information.

    Hope it help.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  13. PattyLand New Member

    I had read that today earlier but didnt really look into it.

    Thanks

    Thanks
  14. PattyLand New Member

    The first step in the job runs this command. It writes over any existing data on the tape which would be last weeks back up because I use a different tape for each day of the week. All the other jobs after this append to the tape. This job is executed 3 times a a day. This is probably a stupid question but can the tape screw up do to exess formating?

    BACKUP DATABASE master
    TO TapeBackups
    WITH INIT ,
    NOUNLOAD ,
    NAME = N'masterBK',
    SKIP , STATS = 10,
    DESCRIPTION = N'System DataBase Backup ',
    FORMAT ,
    MEDIANAME = N'DB_SET'

    I seam to keep getting these errors. I just tested it and got it agian.

    Executed as user: DBAdministrator. Cannot open backup device 'TapeBackups'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE:
    The step was retried the requested number of times (2) without succeeding. The step failed.

    When I check the error log i notice that it reports something about dismounting.

    Tape 'Database_SET' (Family ID: 0xa6fa9833, sequence 1) dismounted from tape drive 'Tapebackups'.
    Why does it do this?

    I am using only one family to back up the Dbs and corresponding Transaction logs. should I create two families?

    Thanks
  15. satya Moderator

    Try to backup to a local file and then use OS application write into tape, backup directly to tape is often slow and error represents there is no device(file) associated as mentioned in the statement.

    Ensure SQL service account and your account has required privileges on tape drive.
    Its better to create different families for Database backups and TLogs backup.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. PattyLand New Member

    The backups all succeed. When I read the history table i notice that it has to arbitrarly retry on a one the steps. I'm running a job that backs up 9 databases, one step at a time. Sometimes it runs with out retries and sometimes does. It happens more on the transaction log job backups which by the way do not overlap when the Datbase backups are running. Is there a way I can put a WAITFOR clause maybe the tapes need to wait a second or so for the next step to run.



    Thanks
  17. Luis Martin Moderator

    Yes, you can use WAITFOR fow a few seconds, may be you are writhg and tape are not ready to work.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  18. PattyLand New Member

    I get a syntax error near the waitfor keyword with and without the comma.

    BACKUP LOG Corporativa
    TO [TapeBK_01]
    WAITFOR DELAY '00:00:05',
    WITH NOINIT ,
    NOUNLOAD ,
    NAME = N'CorpTLBK',
    SKIP ,
    STATS = 10,
    NOFORMAT


    Thanks
  19. Luis Martin Moderator

    I was thinking between BACKUPS.

    Backup..

    end backup.

    waitfor

    Backup...



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  20. satya Moderator

    I suggest you to bifurcate the backups job, for the bigger database keep it seperate and for smaller ones a seperate plan. This would help to dig out any issues for the troubled ones too.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  21. PattyLand New Member

    Will do Thanks a lot for your help. By the way is there a way I can run a job just to format the disk and create a new media set? If not no problem. The tapes are just temparary until we get are Stand by server which is on its way. By the way how often should I back up Master and Msdb?

    Thanks
  22. satya Moderator

    While using BACKUP statement you can specify FORMAT/INIT clauses.
    Where BOL specifies :

    FORMAT
    Specifies that the media header should be written on all volumes used for this backup operation. Any existing media header is overwritten. The FORMAT option invalidates the entire media contents, ignoring any existing content.

    Important Use FORMAT carefully. Formatting one backup device or medium renders the entire media set unusable. For example, if a single tape belonging to an existing striped media set is initialized, the entire media set is rendered useless.

    By specifying FORMAT, the backup operation implies SKIP and INIT; these do not need to be explicitly stated.

    INIT

    Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

    The backup media is not overwritten if any one of the following conditions is met:

    All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.


    The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.
    Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section.



    Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  23. satya Moderator

    It depends if there are frequent changes to jobs, system configuration and its reasonable to backup system databases on daily basis, no issues at my end and it never hog-up system resources.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  24. PattyLand New Member

    I added the waitfor statement TO each job step as follows.

    WAITFOR DELAY '00:00:10'
    BACKUP DATABASE....

    One thing I notice in the SQL Log was this

    Tape 'SERVER04_SET' (Family ID: 0xcc8c9057, sequence 1) mounted on tape drive 'TapeBackup'.
    Tape 'BackupDB_SET' (Family ID: 0xcc8c9057, sequence 1) dismounted from tape drive 'TapeBackup'
    Database backed up: Database: master, creation date(time): 2003/12/14(14:01:24), pages dumped: 1920, first LSN: 202:145:1, last LSN: 202:147:1, number of dump devices: 1, device information: (FILE=1, TYPE=TAPE, MEDIANAME='BackupDB_SET': {'TapeBackup'}).

    It follows this same sequence for all 9 databases. Are these Mounts and Dismounts part of the backup process?

    I am going to monitor the backups trough out the day and see what results I get using the WAITFOR statement.




    Thanks
  25. satya Moderator

    I think mount & dismount is processed from OS when SQL completes its backup procedure.
    Anyway you may find more information during you monitor the process.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  26. PattyLand New Member

    Since I included the WAITFOR DELAY '00:00:10' BACKUP DATABASE... statement all backups have succesfully completed without any retries. I dont know if this had any impact or not but I'm not complaining. Thanks!

    Thanks
  27. satya Moderator

    BTW, are directly performing backup to tape?
    Its always better on the basis of performance to do a local backup and use OS application backup the file to tape, this way you can gain performance on SQL process and while using backup process.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  28. Luis Martin Moderator

    True. But in no production hours is ok. Nigth I mean.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  29. PattyLand New Member

    So your saying to back up to local disk first, then back up from disk to Tape Drive? If so how often do I back up from local disk to tape drive? Why shouldnt I back up directly from server to Tape?

    Thanks
  30. Argyle New Member

  31. satya Moderator

    IN addition to Argyle's reference you can see lot of time difference in executing backup to local disk and a tape.

    Backups must be scheduled regularly where you cannot excuse for any reason, continue the existing process and monitor the process.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page