SQL Server Performance

users and database backup

Discussion in 'Performance Tuning for DBAs' started by itfbd, Sep 20, 2007.

  1. itfbd New Member

    Hello experts !!
    I'm taking two type of database backups as follows:
    1. BACKUP DATABASE Ldbase to Disk='F:DBFullLdbase.dmp' with INIT (This full database backup runs once at night in f drive.)
    2.BACKUP DATABASE Ldbase to Disk='G:DBDiffLldbase.dmp' with DIFFERENTIAL (This differential backup runs every 3 hours during the day between 6 AM to 9 PM)
    Now my question is, Whether these scripts and schedules are OK ?
    And how to restore in case of failure...? First full database then last differential or any other way ?
  2. satya Moderator

    I believe you haven't taken the point I was stressing that to use Transaction log backups every 1 hour. There is a bit confusion with differential backups and in any case you have to get back the data by restoring from last full database backup + differential backup or Transaction log backup.
    A point in time recovery is restoring a database to a specified date and time. When you have completed a point in time recovery, your database will be in the state it was at the specific date and time you identified when restoring your database. In order to perform a point in time recovery you will need to have an entire series of backups (complete, differential, and transaction log backups) up to and/or beyond the point in time in which you want to recover.
    Refer to http://www.databasejournal.com/features/mssql/article.php/3530616 for more information.
  3. itfbd New Member

    Dear Expert,
    To be very frank, i have a very little knowledge of SQL server. I was facing problem in backup as it was taking 1 hour and i wanted to reduce it so i entered in this forum. The server hardware part has been checked and the vendor has suggested to change some controller. That he'll take care.
    Now the situation is that i'm confused with the advice.
    What i have done is that set my database to Full recovery model. And Taking full database backup once at night and differential backup every 3 hours (as mentioned earlier). In this case differential database backup dump file has exceeded the actual size of full database backup. How come ?
    Secondly i dont have any idea on Transaction log backups.
    Can u please advice accordingly considering the fact that i'm a beginner.
  4. satya Moderator

    So the hardware is fixed you should be able to continue the backup with any performance glitch. I know you are beginner in the SQL world, thats why I have been stressing the important factor of point in time restore facility for your database.
    I believe you need a good read through the updated books online [http://www.microsoft.com/downloads/details.aspx?FamilyID=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=de] about Transaction log architecture, backup methods and recovery models you can adopt in case they are needed.
    Go through the following links for more information:
    http://www.functionx.com/sqlserver/index.htm
    http://www.sqlservertutorials.com/
    http://www.devx.com/getHelpOn/10MinuteSolution/16507
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
    http://www.dbazine.com/sql/sql-articles/larsen12
    http://www.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
    http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
  5. itfbd New Member

    Lots of Thanks or providing such valueable links ...
    One last question..why is the size of differential backup (16.2 GB), become larger than the full database backup(which is 12.4 GB) ?
    Thanks in advance.
  6. satya Moderator

    May be the number of transactions are higher when the diff. backup is performed and between that time to full database backup the number of transaction may be less, nothing to worry

Share This Page