Reverse data processed...

Hi All,
I have a very big database (110GB+). It takes a long time to restore or
even just copy...
Now, I have a process that will alter a lot of data in this DB over a lot of
tables. And this process has a bug in it... In order to debug this
process, I would need to have to keep restoring this DB into its initial
state. The process takes about 45 min to 1 hour.
Would someone be kind and tell me:
1) Is there a way to reverse the changes this "Process" made quickly? i.e.
Quick restore. Obviously, I have a full backup.
2) A good old restore takes about 4 hours from a local drive
3) A good old file copy takes about 2 hours.
4) What I am really looking for, is a way to undo the DB to certain point
in time. Like a restore point...
Anyway idea?
Thanks,
AlexHello Alex,
Depending on your process, you may be able to successfully accomplish what
you need by building some error handling into your process that will
ROLLBACK TRANSACTION when an error is encountered. Note that you can not
ROLLBACK once a transaction has been committed, so this would have to
happen before a COMMIT TRAN statement. It may also be a good idea to
create a log entry in the NT Event Logs to provide more information about
the details of the error. You can use RAISERROR to call messages stored in
the sysmessages table of your database, which can be configured using
sp_addmessage. For example:
BEGIN TRAN T1
..statements...
IF @.@.ERROR <> 0
BEGIN
RAISERROR ('Error Message Text', 16, 1) WITH LOG
ROLLBACK TRANSACTION
END
..statements...
COMMIT TRAN
For more information about using RAISERROR to provide specific information
about the error that you're receiving, please refer to Books Online and
search for RAISERROR. This will fully describe the parameters that are
available with RAISERROR.
Another way to manage your issue, depending on your testing requirements,
might be to debug your process against a smaller data set. By pulling a
subset of data from the VLDB you should be able to test you process, break
your data, and restore your data much faster. You can use DTS to transfer
the data over to a new database and then run your process against that.
If working against a smaller dataset is not an option for you, another
method would be to move the affected tables to a different filegroup. Once
you have run your process you could restore just that one filegroup, which
should be smaller (and faster) than a full restore.
Restore points and checkpoints are used by MS SQL Server only during the
restore process, so these will not be helpful for you as a "rollback to X
point" option. There may be third party solutions available on the market
that provide this level of functionality.
Please let me know if you have any other concerns, or need anything else.
Hope this helps!
Sincerely,
Dana Brash
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>Subject: Reverse data processed...
>Date: Tue, 25 Jan 2005 16:40:20 -0800
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#PSre#zAFHA.4004@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: 63.171.237.69
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499552
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Hi All,
>I have a very big database (110GB+). It takes a long time to restore or
>even just copy...
>Now, I have a process that will alter a lot of data in this DB over a lot
of
>tables. And this process has a bug in it... In order to debug this
>process, I would need to have to keep restoring this DB into its initial
>state. The process takes about 45 min to 1 hour.
>Would someone be kind and tell me:
>1) Is there a way to reverse the changes this "Process" made quickly?
i.e.
>Quick restore. Obviously, I have a full backup.
>2) A good old restore takes about 4 hours from a local drive
>3) A good old file copy takes about 2 hours.
>4) What I am really looking for, is a way to undo the DB to certain point
>in time. Like a restore point...
>Anyway idea?
>Thanks,
>Alex
>
>|||If you have the disk space, detach the database using sp_detach_db, make
copies of the files, then just keep making a fresh copy of the file every
time and then use sp_attach_db to reattach it. Should be much faster.
You might also just consider wrapping the whole thing in a transaction and
step through the process a few statements at a time and rollback when you
find a bug. If it is a single statement taking a bulk of the time, post it
if you can and someone might be able to help you find your problem.
This might sound preachy, but you might want to execute the code "manually"
by walking through and tracing through all of the joins mentally and on a
white board. It can really make the difference if you can wrap your head
around it, you may even find errors that don't crop up in simple testing.
Happy hunting!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smartikat" <Smartikat@.online.nospam> wrote in message
news:%23PSre%23zAFHA.4004@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a very big database (110GB+). It takes a long time to restore or
> even just copy...
> Now, I have a process that will alter a lot of data in this DB over a lot
> of tables. And this process has a bug in it... In order to debug this
> process, I would need to have to keep restoring this DB into its initial
> state. The process takes about 45 min to 1 hour.
> Would someone be kind and tell me:
> 1) Is there a way to reverse the changes this "Process" made quickly?
> i.e. Quick restore. Obviously, I have a full backup.
> 2) A good old restore takes about 4 hours from a local drive
> 3) A good old file copy takes about 2 hours.
> 4) What I am really looking for, is a way to undo the DB to certain point
> in time. Like a restore point...
> Anyway idea?
> Thanks,
> Alex
>