Curious as to how most of you do MS SQL database backups, as I am now working on a SQL Server backup and recovery plan…

My problem lies in that I will be co-locating and will not have direct access to tape drives, but only one large hard drive to make all database backups on. Thus I am thinking of doing a full backup of the necessary databases to this backup disk once a day with a few differential backups throughout the day by using some sort of automated process. I am also contemplating using a VPN or FTP to get the latest backup once a week in case the co-located server becomes compromised and all data is erased.

What I am wondering is where you keep your database backups and how many old database backups you keep on average? What sort of process do you use to automate such a task? (Simply copy the .MDF and .LDF files, use Enterprise Manager, use T-SQL, etc...) And finally, is it really safe to be doing differential backups on the same last full backup file? I worry that something might go wrong with the backup and corrupt the latest full backup file.

For your information the database I am using is MS SQL Server 2000 and I plan on using T-SQL to automate the process.

Hi Rebies,

There was a tool I knew of which could connect to MS SQL databases and you could issue mysql requests etc. I've since lost the link.

However, http://sqlrelay.sourceforge.net/ may be of help. Basically, I'd recommend logging into the MS SQL server (via an M$ machine OR via a Linux machine using sqlrelay) as the admin user and dumping every database into seperate dump files.

That way, you could import the lot in the chance of a disaster instead of messing with the raw DB files on the backend.

Just my 2c,

Stuart

SQL Server 2000 has a wizard that will automate the entire backup process using jobs or a maintenance plan that can be scheduled any time you want.

Best plan to backup critical data to disk nightly with a full backup and every hour dump the log file. The wizard should be able to automate that too.

If you cannot get to the wizard at the colo site, just create the jobs, script it out and then ask the colo to add the jobs to SQL Server for you.

I would not detach and copy raw data files (mdf and ldf). You will have a nightmare to recover.

The freequency of full backups depends on the size of your database. If you can do it everyday, that's ok.

This should be your plan:
- A full backup everynight
- differential backups every few hours
- trasnaction log backups every so minutes

You can schedule these tasks very easy.

When a database crashes, backup the transaction log with no recovery option.

Restore the last full backup
Restore the last different backup.
- restore trasnaction logs starting from the last differential backup till the one you did after the crash.

You can ftp the backup files to your location. You can also set up a replication scheme to transfer data continously to an offsite location. You can also do log shipping for a standby. The whole topic is too big to discuss here.