need a reliable backup plan fro SQL 2000

[yankeeclipper]

I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.

As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:\xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.

I created #1, and #3 in query analyzer and saved the script.

I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?

This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.

Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.

I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.

many thanks

[MAK]

You can do one of the following
1. Log Shipping
2. Differential backup on a different server.
3. Full backup, tranlog backup every 15 min and move the tranlog backup to another server
4. checkpoint database, detach db, copy db to new location in same server, attach db, zip only .mdf file and copy the zip file to another server, unzip it, attach the db.
5. do transactional replication to another server

It all depends on how critical your data is.

[timc]

I second all above solutions. I have seen some problems with some apps when detaching and attaching DB's. Log shipping would be simplest approach

[sdives]

I've done, and am doing a variation of the things you've listed except the detatch and attach scenario. I'm moving toward log shipping but my production server is running with Standard edition, so I have to deal with that first.

In the scenario you described, you could execute .bat files for steps #2, #4, and #5 (or combine #4 & #5 into one step).

What I currently do is run two backup jobs. The output of one goes to the NTFS of the production server. The second job writes directly to another server. The login account for SQL is an administrator on both servers. I created a backup device that used UNC to point to a shared drive on the second server. I then use BackupExec to backup the files on the second server. It's not hotswappable but I can recover in a resonable amount of time if I have to.

Prior to this method, I modified the maintenance job with an additonal step that would execute a .bat file. The .bat file performed an XCopy from the production server to the second server. The biggest issues I had using this method was that I received a warning from SQL about modifiying a maintenance job and I didn't have a way to automatically clean up the second server. I kept running low on disk space.

Questions? Email me or reply to the post.

Sidney Ives
Database Administrator
Sentara Healthcare

[yankeeclipper]

thanks,
I was reading up about log shipping this weekend, but since we have the Standard Edition I don't think that is an option. From what I was reading the "warm backup server" seems like the closest approach we are attempting. The backups would be placed and restored on the warm backup and the server would be minutes away from going "live" once the trasaction logs are added.

Does anyone have a disater Recovery point sheet that lists the actions involved to switching over to a "warm backup server" so that the switchover is SMOOTH? It would be appreciated.

The differential backup strategy seems interesting as well, but being able to restore to an exact minute is a powerful reason to stick to the transaction log scenario.

[timc]

You might check the SQL 2000 Resource Kit, I believe there is a version of log shipping for standard edition in the kit. There are also several variations of log shipping scripts out on the web

[yankeeclipper]

#1 Would the detach of the DB give me a DB that is fully current including all transactions or would it be a snapshot with transactions not yet added into it? One of the reasons we are considering the detach/attach scenario is because it would not need any transaction logs to be added to it. If we had to do a disaster recovery.

#2 Also, when we perform a detach what would happen to people that were accessing that DB. In our scenario there are end users that could/would be adding records any hour of the day even at 2 or 3AM.

[sdives]

I'm not sure I can adequately address #1 but as far as #2 is concerned, you can't detach the database while it's being used. Additionally, the users aren't going to be able to use the database until it's re-attached.

[timc]

As far as #1, I would backup trans log before doing this. Still vote for log shipping. No downtime, least intervention, and less chance for disaster

[rmiao]

1. After detaching db, you can backup db data and log files. But it's not same as backup database.

2. Users are unable to access the db after detaching it, and you can't detach db if it has any open connection.

3. Don't understand why detach db for backup. If don't like to do log restore, you can use differential backup.

[yankeeclipper]

We were thinking the detach and copy would be a good DB to use on a warm backup server. To be current would it need the DB updated with the latter trans logs?

Please Rate three disaster recovery strategies. We have 2 appl DB's the master DB and msdb.

uld we be better off with full backups of [2] both appl DBs, Master, and msdb?

FIRST - full backup
Would the recovery process on backup warm server then be to
#1 copy DB and logs to warm server
#2 restore the [4] full backups and
#3 update with [4]trans logs to bring the SQL server current within the [4]DBs?

SECOND - detach scenario
Using instead detach command could we
#1 copy DB and logs to warm server /
#2 attach on warm server and update DB with trans logs to be current. Is this an accurate restore?


THIRD - DIFFERENTIAL
With differential backup could we
#1 copy latest .BAK to warm server and #2 restore latest .BAK and then update DB with the last differential backup.

Would these three scenarios all produce equally valid DBs on the backup server?

I'm thinking the detach might not be accurate and I don't want any holes or missing work in the DB . Also, we can have end users in the DB at even 3AM so the detach would not work.

[rmiao]

Yes, all three scenarios will produce equally valid DBs on the backup server. But I'll use log shipping in your case, and don't have to restore master nor msdb on backup server after initial setup.