Recovery from a file system backup? 2

[mingtmak]

My knowledge of SQL Server is limited, so please bear with me.

I haven't been able to find this out.
Can you recover from a file system backup (i.e. the .mdf,.ndf and .ldf files located in the 'Data\all\databases' folder)? Is this practice frowned upon?

ex. doing a tape backup of all the files located in the SQL folders and the folders where the databases and log files themselves are located.
Should I be doing a backup through the Enterprise Manager of the DBs to another folder and then doing a filesystem backup? My issue is that Enterprise manager backups do take a bit of time and also take up extra room. The daily backup does a backup of the whole server. Changing tapes is not practical either.

 

[ptheriault]

your backup stategy depends on a few things.
1st Do you need to be able to recover to a point in time? If so then just backing up the files in the data folder won't do.
(This is asuming you have the correct backup software) mdf, ldf and ndf files are locked by SQL server so unless you have software that can backup those files while in use don't even bother trying to restore them.

2nd What is the current recovery model of your databases. Right click on the database and select properties. if your databases are in FULL recovery mode you better be backing up the Transaction log. If you don't your log will never be cleared of old transactions and you will run the server out of disk space. If you have to recover your data to a point in time then you must have your db in FULL recovery mode. To recover to a point in time you will have to backup your Transaction log thoughout the day. If you don't need to recover to a point in time set your db to simple recovery and you won't have to worry about your transaction log filling up your disk.

I have found the most reliable backup of a database is to use the SQL server tools. There are some other 3rd party tools like LightSpeed by quest software which is excellent. It compresses the database backup around 90% and is much faster than the SQL server backup.
I have also found that most of the time I have to restore a database it is because of user error. So I'm restoring from the previous night's backup. It would take forever if I had to go to tape. I always keep the most recent backup on disk and I let my file system backup write my .bak files to tape.

Which ever options you choose you should run a test restore of your backup senerio.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

No. It won't work. Well, okay there is ONE exception. The database has to be offline when the files are backed up. That means either detach the database and backup the .mdf/.ldf files or stop the SQL Server services and backup the files.

I suggest using SQL Server backup commands to backup the database to disk and then copy the backup files to tape.

-SQLBill

Posting advice: FAQ481-4875

 

[mingtmak]

Thanks guys for your feedback!

Currently the SQL Server is only used for development and is shutdown during the server backup. But your guys' strategy is a better solution.

Thanks again!

Jon

 

[ptheriault]

Here is some very good information on SQL Server backup and restore.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.