Undo database changes

Question

Undo database changes

paulholl

SSC Enthusiast

Points: 154
More actions
January 10, 2008 at 2:48 am

#202212

Hello, I have a problem with one of my clients to do with a SQL Server 2005 database but I am a relatively inexpirienced with SQL Server.
A query has been run that has overwritten the data in one column with the data from another column and there are no backups to restore. How can I undo the changes to restore the data in that column?
The database is on a remote server owned by Fasthosts and I have installed Microsoft SQL Server Management Studio Express on my local machine to view the database. I have tried using Redgate SQL Log Rescue which seems like it should do as I require. However I get an error when I try to install the Extended Stored Procedures.
I would greatly appreciate any ideas anyone has for undoing the changes to that column.
Kind Regards
Paul

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

If it's not your server then chances are you're not an administrator and won't have the rights to install the necessary bits to make it work 🙁 Sorry...

Are you sure that you don't have a full backup, even if it's quite old (and another hasn't been taken since)? If so, then you could use that full backup in conjunction with a transaction log backup that you could take now. You'll restore the full backup and then the log backup with the STOP AT command indicating a point in time to which you want the database restored. This will only work if your database is in full recovery mode though...

Finally, perhaps you could get your database taken offline and then have the server operator send you the actual mdf & ldf files. The server's database could then be put back online and you can do the fiddling with logs on your own server/desktop using the mdf & ldf files.

paulholl SSC Enthusiast Points: 154 More actions · Answer 2

Thanks, that is a great idea about moving the database onto my local server and making the changes. Is there a way I could copy the remote copy to my local copy?

Thanks

Karthik-214069 SSChasing Mays Points: 631 More actions · Answer 3

Ian's idea is good. But before anything can you confirm whether the database's recovery model is full by running the below query?

select databasepropertyex('DBNAME', 'recovery')

paulholl SSC Enthusiast Points: 154 More actions · Answer 4

paulholl

SSC Enthusiast

Points: 154

January 11, 2008 at 2:00 am

#766994

I have checked and the Recovery Mode is FULL

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

paulholl (1/10/2008)
Hello, I have a problem with one of my clients to do with a SQL Server 2005 database but I am a relatively inexpirienced with SQL Server.
A query has been run that has overwritten the data in one column with the data from another column and there are no backups to restore. How can I undo the changes to restore the data in that column?
The database is on a remote server owned by Fasthosts and I have installed Microsoft SQL Server Management Studio Express on my local machine to view the database. I have tried using Redgate SQL Log Rescue which seems like it should do as I require. However I get an error when I try to install the Extended Stored Procedures.
I would greatly appreciate any ideas anyone has for undoing the changes to that column.
Kind Regards
Paul

Hi Paul,

SQL Log Rescue does not support SQL Server 2005. It is only released for SQL Server 2000. There are other vendors that do have 2005 support, so you may want to look at those.

Kind Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 6

paulholl (1/11/2008)
I have checked and the Recovery Mode is FULL

You mentioned that there are no backups to restore. If the database has never been backed up then the recovery mode does not matter. It will still run in SIMPLE recovery mode, and the log will be recycled. In order to "activate" full recovery mode, a full database backup must have been taken. Has this been done? If so, there are tools to compare the data in the backup directly with the database, or you could restore it to a new database, and compare the data. This would allow at least partial recovery. Alternatively you could use third party log reading tools. If there has never been a full database backup, then it is very likely that the log has been recycled or soon will be (do stop the server, and make copies of the ldf and mdf to avoid further damage to the existing log data).

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

paulholl SSC Enthusiast Points: 154 More actions · Answer 7

Thanks.

I have since taken a Full Backup. How would I be able to move that backup onto my local server?

Karthik-214069 SSChasing Mays Points: 631 More actions · Answer 8

Moving the backup to your local machine should be easy if you have FTP access to any of the directory on the server.

Take a full backup of the db at the ftp location and download the backup using any ftp client tool or just IE