| Author |
Topic |
|
lkevinl
Starting Member
4 Posts |
 Posted - 2007-01-03 : 09:36:13
|
| OK, I made a big mistake. I'm using SQL Server 2000, latest SPs (not the mistake!).I currently back up my client's database with a backup job each night at 2am.In addtion, I use a DTS package to copy the database to my development machine at my office. The DTS is run as a job each night at 11pm, so it happens BEFORE the backup job. I recently got a new development machine and so, in Enterprise Manager, I registered my clients server, the server on my old development box and the new server installed on the new development box. I then tried to to create the DTS job on the new box to pull the database from my client's server to the new box.INSTEAD, in Enterprise Manager, I was creating the job on the clients box, not on the new dev box, so when I specified source, I used the IP address of the client box, and destination I used (local) and created the job ON THE CLIENT BOX! So, at 11pm, the job tried to import onto itself. The result: all tables empty. I lost 1 days worth of data since the job ran at 11pm before the backup job that runs at 2am.This was idiotic on my part. My client has to reconstruct his data. Is there anyway I could roll this back? |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2007-01-03 : 09:45:17
|
| If your database is in Full Recovery mode and you're backing up the transaction log, you can do a point in time restore. |
 |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2007-01-03 : 09:47:08
|
| In addition I'd think about backing up prior to copying the database or copy the backup file and restore that. |
|
|
|
lkevinl
Starting Member
4 Posts |
Posted - 2007-01-03 : 14:12:52
|
| It's not in full recovery mode unfortunately. I'm unfamiliar with how that works. Do I just enable Full Recovery mode and the transaction log gets backed up automatically or do I enable full recovery and then schedule regular transaction log backups?Then, let's say a problem like this does happen, what are the steps to recover? I assume you restore the database backup from the previous night but how do you apply the transactions via the transaction log? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-03 : 14:44:16
|
quote:
Originally posted by lkevinl
It's not in full recovery mode unfortunately. I'm unfamiliar with how that works. Do I just enable Full Recovery mode and the transaction log gets backed up automatically or do I enable full recovery and then schedule regular transaction log backups?Then, let's say a problem like this does happen, what are the steps to recover? I assume you restore the database backup from the previous night but how do you apply the transactions via the transaction log?
First, restore the database from the full backup with the NORECOVERY option.Then use the RESTORE LOG to restore each log backup in order from oldest to newest. Use the NORECOVERY option on each one, except for the last one.You're lucky you still have that client. You need to be extra careful when you don't know what you are doing. The rule to go by is: "First, do no harm" And always, always, always have good backups.CODO ERGO SUM |
|
|
|
lkevinl
Starting Member
4 Posts |
Posted - 2007-01-03 : 14:58:21
|
Lucky indeed! It's my brother-in-law's business, so likely wouldn't lose the client be Thanksgiving could become a bit tense!  So to do what you describe, what recovery mode would I need to use? If full recovery mode is required, how do I prevent the transaction log from getting too big?Are there any other steps required like setting up transaction log backup jobs? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-03 : 15:08:10
|
| Set the database to full recovery mode, schedule a full backup job to run once per day, and run that job. Then setup a transaction backup job to run at least every 15 minutes 24x7, more often if the log grows too large. Set the job to retain the transaction log backups at least 3 days. Retain the full backups for a few days if you have enough disk space. Make sure you also get at least a daily backup of the system databases; they should not be set to full recovery.That is just to get you started. You also should look at some kind of automatic tape backup so that you are covered when you don't discover that "oops" for a few days.And never do business with relatives or friends. CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
|
|
lkevinl
Starting Member
4 Posts |
Posted - 2007-01-03 : 16:25:21
|
| Hi Michael, thanks much! That was exactly what I'm looking for.I'll test this on my dev box. When I do a restore if the daily back-up at one stage do the transaction log back-ups get applied, during the restore or as an addition step(s)? For instance, to recover to a certain point in time, I'd first restore from the daily back-up and then what?The final production box for their app. will have RAID 1 and USB drive back-ups, neither of which would have helped with this situation, human error. I can out-smart most failsafe counter measures. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 16:33:58
|
| See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20exampleBasically:Restore a FULL backupRestore a DIFFERENTIAL backup (taken at some time after the Full backup, and based on it. This is optional) - a differential backup can reduce the number of TLog backups that then have to be restored - e.g. if a Full backup is taken once a week and TLog backups every 10 minutes a system failure 6 days and 23 hours later is a lot of Tlog backups to restore : (6 * 24 + 23) * 6 in fact!in such a situation taking a Diff backup daily means that only 1 diff + (23 * 6) tlog backups have to be additionally restored.Restore ALL Transaction Backups thereafter, in order, up to the last one that is required.For the last one optionally specify a Date/Time when the restore should stop - otherwise the whole of the last transaction backup will be restored.SQL Server will give you an error if you restore the TLog backups out-of-order, but the error is not fatal - you can locate the correct TLog backup and continue restoring with that and so on until you are done.Kristen |
|
|
|
|
Ouch... big mistake with DTS!