sql server replication: If you are backing up your transaction log and if you noted the time of the
delete, you can restore the database backup to a new database and restore
all of the logs with the STOPAT option. That option lets you specify a date
and time after which it does not recover the log. At that point, you can
copy over the data.

Alternatively, Lumigent has excellent tools - www.lumigent.com.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


[quoted text, click to view]
Hi there,

One of our user had (and still has) the right to post DELETE commands to
one of our main tables in the Database. I usually hate when managers has
this kind of rights. Generate a lot of trouble.
The scene is as following: he deleted some records from a very important
table in production environment. He was very careful in deleting all the
dependent records before.
Now we need those records back. To generate them again we would have a
lot of work, envolving a lot of resources. Is it possible to rollback a
database to a certain point of its life... kinda... I need it to the way it
was today by 7:00 pm. Or anything like this. I need to rollback this
database to the closest point it was at the exact moment the fu*.. (sorry)
deleted the records.

Thanks in advance.

Be careful. You must first restore the full database backup - taken before
the delete occurred - WITH NORECOVERY. Next, you restore the logs leading
up to the delete WITH NORECOVERY - except for the last one. The last one,
you restore WITH RECOVERY, STOPAT = XXX.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


[quoted text, click to view]
Hi Tom,

Thanks for the tip..
I tryied the restore with stopat but something is not right, and I can't
figure out what it is.
I've created a back up today to restore using stopat.
So, now I have, in my C: root folder the MYDATABASE.BAK file
That's what I've done, and at the end you'll find the err msg.

-- **********************************************************
-- Create a database to restore backup over
CREATE DATABASE Temoprary
ON
( NAME = Temoprary_DATA,
FILENAME = 'C:\Temoprary_DATA.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Temoprary_LOG',
FILENAME = 'C:\Temoprary_LOG.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

GO

-- Trying to restore the DB over the just created DB
RESTORE LOG Temoprary
FROM DISK = 'C:\MYDATABASE.BAK'
WITH NORECOVERY
,MOVE 'MyDatabase_Data' TO 'c:\Temoprary.mdf'
,MOVE 'MyDatabase_Log' TO 'c:\Temoprary.ldf'
,STOPAT = 'Nov 26, 2003 2:00 PM'
-- **********************************************************
-- Received this error message
/*
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
*/
-- **********************************************************

Do you know what might be happening?? I tried "WITH RECOVERY", "WITH
NORECOVER" and "WITH STANDBY = 'c:\UndoFileName.sql'" options and it still
returns this same error.

Thanks again for your help =)

Daniel


"Tom Moreau" <tom@dont.spam.me.cips.ca> escreveu na mensagem
news:%23%23S2bOQtDHA.3196@TK2MSFTNGP11.phx.gbl...
If you are backing up your transaction log and if you noted the time of
the delete, you can restore the database backup to a new database and
restore all of the logs with the STOPAT option. That option lets you
specify a date and time after which it does not recover the log. At that
point, you can copy over the data.

Alternatively, Lumigent has excellent tools - www.lumigent.com.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


[quoted text, click to view]
Hi there,

One of our user had (and still has) the right to post DELETE commands
to
one of our main tables in the Database. I usually hate when managers has
this kind of rights. Generate a lot of trouble.
The scene is as following: he deleted some records from a very
important
table in production environment. He was very careful in deleting all the
dependent records before.
Now we need those records back. To generate them again we would have a
lot of work, envolving a lot of resources. Is it possible to rollback a
database to a certain point of its life... kinda... I need it to the way
it
was today by 7:00 pm. Or anything like this. I need to rollback this
database to the closest point it was at the exact moment the fu*.. (sorry)
deleted the records.

Thanks in advance.

Hi there,

One of our user had (and still has) the right to post DELETE commands to
one of our main tables in the Database. I usually hate when managers has
this kind of rights. Generate a lot of trouble.
The scene is as following: he deleted some records from a very important
table in production environment. He was very careful in deleting all the
dependent records before.
Now we need those records back. To generate them again we would have a
lot of work, envolving a lot of resources. Is it possible to rollback a
database to a certain point of its life... kinda... I need it to the way it
was today by 7:00 pm. Or anything like this. I need to rollback this
database to the closest point it was at the exact moment the fu*.. (sorry)
deleted the records.

Thanks in advance.

Daniel

Hi Tom,

Thanks for the tip..
I tryied the restore with stopat but something is not right, and I =
can't figure out what it is.
I've created a back up today to restore using stopat.=20
So, now I have, in my C: root folder the MYDATABASE.BAK file
That's what I've done, and at the end you'll find the err msg.

-- **********************************************************
-- Create a database to restore backup over
CREATE DATABASE Temoprary
ON=20
( NAME =3D Temoprary_DATA,
FILENAME =3D 'C:\Temoprary_DATA.mdf',
SIZE =3D 10,
MAXSIZE =3D 50,
FILEGROWTH =3D 5 )
LOG ON
( NAME =3D 'Temoprary_LOG',
FILENAME =3D 'C:\Temoprary_LOG.ldf',
SIZE =3D 5MB,
MAXSIZE =3D 25MB,
FILEGROWTH =3D 5MB )

GO

-- Trying to restore the DB over the just created DB
RESTORE LOG Temoprary
FROM DISK =3D 'C:\MYDATABASE.BAK'
WITH NORECOVERY
,MOVE 'MyDatabase_Data' TO 'c:\Temoprary.mdf'=20
,MOVE 'MyDatabase_Log' TO 'c:\Temoprary.ldf'
,STOPAT =3D 'Nov 26, 2003 2:00 PM'
-- **********************************************************
-- Received this error message=20
/*
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH =
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or =
WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
*/
-- **********************************************************

Do you know what might be happening?? I tried "WITH RECOVERY", "WITH =
NORECOVER" and "WITH STANDBY =3D 'c:\UndoFileName.sql'" options and it =
still returns this same error.

Thanks again for your help =3D)

Daniel=20


"Tom Moreau" <tom@dont.spam.me.cips.ca> escreveu na mensagem =
news:%23%23S2bOQtDHA.3196@TK2MSFTNGP11.phx.gbl...
If you are backing up your transaction log and if you noted the time =
of the delete, you can restore the database backup to a new database and =
restore all of the logs with the STOPAT option. That option lets you =
specify a date and time after which it does not recover the log. At =
that point, you can copy over the data.

Alternatively, Lumigent has excellent tools - www.lumigent.com.

--=20
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


[quoted text, click to view]
Hi there,

One of our user had (and still has) the right to post DELETE =
commands to
one of our main tables in the Database. I usually hate when managers =
has
this kind of rights. Generate a lot of trouble.
The scene is as following: he deleted some records from a very =
important
table in production environment. He was very careful in deleting all =
the
dependent records before.
Now we need those records back. To generate them again we would =
have a
lot of work, envolving a lot of resources. Is it possible to rollback =
a
database to a certain point of its life... kinda... I need it to the =
way it
was today by 7:00 pm. Or anything like this. I need to rollback this
database to the closest point it was at the exact moment the fu*.. =
(sorry)
deleted the records.

Thanks in advance.