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.(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|||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.|||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