Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: Backup\Restore Best Method?

    Hi,

    I currently have a SQL 2000 server with around five small databases. I wish to have a full backup of each database every night. Currently I use the Database Maintenance Plan to backup the databases and logs to the C: drive of the server from where they can be picked up by backup software. Previously I used the backup wizard, but found it wasn't backing up the logs, would that cause a problem?

    My two questions are ( I am new to SQL)
    1 - How come the database only backsup 6.5 MB, when in explorer the database file is 46MB? Is it working correctly?
    2- Using the above method, if I only backup the database each night, would this be enough to roll back. Say a user deletes a table and I want to restore back to yesterdays database, can I simply restore over the current mdf?

    Thanks in advance.

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    (1) Backups are compressed files and they only backup the actual data within your databases. Meaning if you have a 50mb database and only 30 mb is used, it will only backup the 30mb of data resulting in a backup file smaller than 30mb

    (2) If your company does transactions all day long and someone deletes a table (like you said) at 5:00 PM. Would it be acceptable to restore to yesterdays backup losing all transactions for an entire day? If it is, you better make sure management is OK with this before it happens

    The alternative is to run transaction log backups throughout the day so you could restore up to a point in time.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rhigdon
    (1) Backups are compressed files and they only backup the actual data within your databases. Meaning if you have a 50mb database and only 30 mb is used, it will only backup the 30mb of data resulting in a backup file smaller than 30mb

    (2) If your company does transactions all day long and someone deletes a table (like you said) at 5:00 PM. Would it be acceptable to restore to yesterdays backup losing all transactions for an entire day? If it is, you better make sure management is OK with this before it happens

    The alternative is to run transaction log backups throughout the day so you could restore up to a point in time.

    HTH

    What's your recovery model set to?

    And no. You can not recover a single table from a dump or log.

    You'd have to restore the entire database and then recover.

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    The standard way to restore a table is to restore the database to a test database and then transfer over the deleted table. Or, you can use third party tools such as Lumigent's Log Explorer that allows you to reverse DDL commands.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •