January 3, 2007 at 7:26 am
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?
January 3, 2007 at 8:25 am
Hi Larry,
What Recovery model are you using on the Database? If you are using the "Full" recovery model then you have a few options using the Transaction Log.
Kind Regards,
Nigel.
January 3, 2007 at 8:32 am
I agree with Nigel. Old backups, or the T-log.
Also, do you drop the tables before doing the transfer? If so, you might be stuck.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 3, 2007 at 12:08 pm
Nigel, I'm using a Simple recovery model. What option would be better and what would I have been able to do?
Steve, used the DTS Import/Export Wizard to create the job and I DID use the Drop Objects option.
I need to change my back-up/recovery scheme. I'm going to be setting them up with a RAID 1 system and I'll be doing back-ups to disk using a maintenance plan. I'm unfamiliar with the recovery models. I do know that on another project, the Transaction log grew way too large using the Full Recovery model. Are back-ups mid-day a bad idea? What about running a DTS job (properly created) to pull the data a couple of times a day during business hours?
P.S. This problem seemed far to easy to create. I was simply creating an Import/Export using the wizard but under the wrong server. I was on the source server instead of the destination and I did source by IP address and destination by (local). On the destination, that would have worked fine!
January 3, 2007 at 12:35 pm
If you are using SIMPLE recovery model you are out of luck...
Read the BOL topic "Recovery Models" to get more info about recovery models...
Running the backup is depends on your company requirements... I run some backups during the day and some during night depends on server...
Yes, you can copy the data multiple times if you have the date column in your tables and as long old data is not modified...
For future care... check the server name before you truncate so that you don't truncate accidentally from production...
If @@servername <> 'production servername'
begin TRUNCATE TABLE....
end
MohammedU
Microsoft SQL Server MVP
January 3, 2007 at 2:10 pm
Good advice Mohammed... Thanks! I need to learn a lot more about the Recovery Models.
January 3, 2007 at 3:04 pm
This is why I always use server names when using the wizard, never (local).
Greg
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply