SQL 2000 Point in time return

[rkf_ou]

If I want to return to a point in time for all my databases in SQL 2000 can I just copy all the files in the data directory to a safe place. And then when I want to go back to that point in time, just copy them all back?

[skhanal]

That will not work as the files may be open by SQL Server service. Safe bet is to stop all SQL Server and related service and take a backup, you should be able to restore this to go back in time.

If you are worried about data before a big change, backing up all databases from SQL interface provides equally good safety net.

[rkf_ou]

Thanks. I took the step of shutting down the SQL service before copying the files. I then copied in all the files into the data directory from another copy and no problems appeared and the expected data is intact.

The purpose is to be able to store many customers data that I collect from a single laptop software installation. I either copy out the files from Customer B and copy in the files from Customer A from a previous engagement, or copy in the files from a clean database.

It may not be elegant but the MS tools assume a single installation. This method is fast and effective. At least I haven't seen a problem so far.

Cheers,

R

[rmiao]

Why don't do db backup? That guarantees data integrity.

[rkf_ou]

Well the db backup process seems to be very complicated in that you have to backup each database individually,e.g. master msdb, etc.

Plus the restore process is the same but in reverse and the databases aren't related because they are for different customer and I am using a common tool and laptop.

Maybe I could take the time to write a script but the copy all is a lot less complicated and faster.