ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00
Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00

ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00

ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx
.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00sql

Point-In-Time Restoration Problem

I'm learning how to restore a database to a point-in-time. I set up a
database called DR_TEST and a table called tblTest with a single
varchar column. I back up the database using the following command:
use master
go
BACKUP DATABASE DR_TEST
TO DISK = 'c:\backup\dr_test\dr.bak'
go
Then I run a TSQL script to continuously insert records into the
tblTest table. Using Enterprise Manager I have a scheduled transaction
log backup running every three minutes to c:\backup\dr_test\drLog.bak.
I drop the tblTest table and backup the transaction log using the
following command:
BACKUP LOG DR_TEST
TO DISK = 'c:\backup\dr_test\drLog.bak'
I then run the following commands to restore the database to it's state
at 2:25 PM:
use master
go
RESTORE DATABASE DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
WITH NORECOVERY
go
RESTORE LOG DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
WITH RECOVERY,
STOPAT = N'11/16/2006 2:25 PM'
go
When I run the log restore I get the following message:
This log file contains records logged before the designated
point-in-time. The database is being left in load state so you can
apply another log file.
I started inserting the data at 2:22 PM and I dropped the table at 2:27
PM. Why would I get this message?
JerrySeems you did several log backups but only restored the very first one...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1163710146.977620.47300@.e3g2000cwe.googlegroups.com...
> I'm learning how to restore a database to a point-in-time. I set up a
> database called DR_TEST and a table called tblTest with a single
> varchar column. I back up the database using the following command:
> use master
> go
> BACKUP DATABASE DR_TEST
> TO DISK = 'c:\backup\dr_test\dr.bak'
> go
> Then I run a TSQL script to continuously insert records into the
> tblTest table. Using Enterprise Manager I have a scheduled transaction
> log backup running every three minutes to c:\backup\dr_test\drLog.bak.
> I drop the tblTest table and backup the transaction log using the
> following command:
> BACKUP LOG DR_TEST
> TO DISK = 'c:\backup\dr_test\drLog.bak'
> I then run the following commands to restore the database to it's state
> at 2:25 PM:
> use master
> go
> RESTORE DATABASE DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
> WITH NORECOVERY
> go
> RESTORE LOG DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
> WITH RECOVERY,
> STOPAT = N'11/16/2006 2:25 PM'
> go
> When I run the log restore I get the following message:
> This log file contains records logged before the designated
> point-in-time. The database is being left in load state so you can
> apply another log file.
> I started inserting the data at 2:22 PM and I dropped the table at 2:27
> PM. Why would I get this message?
>
> --
> Jerry
>

Point-In-Time Restoration Problem

I'm learning how to restore a database to a point-in-time. I set up a
database called DR_TEST and a table called tblTest with a single
varchar column. I back up the database using the following command:
use master
go
BACKUP DATABASE DR_TEST
TO DISK = 'c:\backup\dr_test\dr.bak'
go
Then I run a TSQL script to continuously insert records into the
tblTest table. Using Enterprise Manager I have a scheduled transaction
log backup running every three minutes to c:\backup\dr_test\drLog.bak.
I drop the tblTest table and backup the transaction log using the
following command:
BACKUP LOG DR_TEST
TO DISK = 'c:\backup\dr_test\drLog.bak'
I then run the following commands to restore the database to it's state
at 2:25 PM:
use master
go
RESTORE DATABASE DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
WITH NORECOVERY
go
RESTORE LOG DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
WITH RECOVERY,
STOPAT = N'11/16/2006 2:25 PM'
go
When I run the log restore I get the following message:
This log file contains records logged before the designated
point-in-time. The database is being left in load state so you can
apply another log file.
I started inserting the data at 2:22 PM and I dropped the table at 2:27
PM. Why would I get this message?
--
JerrySeems you did several log backups but only restored the very first one...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1163710146.977620.47300@.e3g2000cwe.googlegroups.com...
> I'm learning how to restore a database to a point-in-time. I set up a
> database called DR_TEST and a table called tblTest with a single
> varchar column. I back up the database using the following command:
> use master
> go
> BACKUP DATABASE DR_TEST
> TO DISK = 'c:\backup\dr_test\dr.bak'
> go
> Then I run a TSQL script to continuously insert records into the
> tblTest table. Using Enterprise Manager I have a scheduled transaction
> log backup running every three minutes to c:\backup\dr_test\drLog.bak.
> I drop the tblTest table and backup the transaction log using the
> following command:
> BACKUP LOG DR_TEST
> TO DISK = 'c:\backup\dr_test\drLog.bak'
> I then run the following commands to restore the database to it's state
> at 2:25 PM:
> use master
> go
> RESTORE DATABASE DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
> WITH NORECOVERY
> go
> RESTORE LOG DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
> WITH RECOVERY,
> STOPAT = N'11/16/2006 2:25 PM'
> go
> When I run the log restore I get the following message:
> This log file contains records logged before the designated
> point-in-time. The database is being left in load state so you can
> apply another log file.
> I started inserting the data at 2:22 PM and I dropped the table at 2:27
> PM. Why would I get this message?
>
> --
> Jerry
>

Point In Time Restore Question

This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C393F7.79D233D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am restoring a production database onto a test server to a point in = time.
I restore the database from script with no issue, but when I try to = restore the first of 6 logs, I receive the following message;
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 237426000000004300001, which is = too late to apply to the database. An earlier log backup that includes = LSN 237424000000007700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
What am I doing wrong? What does this mean and how do I get my log = restore script to work to get my database restored to a point in time?
Thanx!
--=_NextPart_000_0038_01C393F7.79D233D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am restoring a production database onto a test server to a point = in time.

I restore the database from script with no issue, but when I try to = restore the first of 6 logs, I receive the following message;

Server: Msg 4305, Level 16, State 1, Line 1The log in this = backup set begins at LSN 237426000000004300001, which is too late to apply to the = database. An earlier log backup that includes LSN 237424000000007700001 can be restored.Server: Msg 3013, Level 16, State 1, Line 1RESTORE LOG = is terminating abnormally.

What am I doing wrong? What does this mean and how do I get = my log restore script to work to get my database restored to a point in = time?

Thanx!
--=_NextPart_000_0038_01C393F7.79D233D0--JLS
This is telling you that you have a missing log between the database you
restored and the log that you are trying to restore. The message is telling
you that you need the log backup containing:
LSN 237424000000007700001
The log you are attempting to restore starts with:
LSN 237426000000004300001
As you can see, your log start point is greater. If you have no log backups
between the database backup and the 7700001 log backup, then that may mean
that the log was truncated during that period. When the chain of LSNs is
incomplete, you cannot restore past the break.
(In the MSDN, search for "SQL LSN" and you will get some background on
this.)
Russell Fields
"JLS" <judy.shoop@.sartomer.com> wrote in message
news:%23RvzWiBlDHA.2364@.TK2MSFTNGP11.phx.gbl...
I am restoring a production database onto a test server to a point in time.
I restore the database from script with no issue, but when I try to restore
the first of 6 logs, I receive the following message;
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 237426000000004300001, which is too
late to apply to the database. An earlier log backup that includes LSN
237424000000007700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
What am I doing wrong? What does this mean and how do I get my log restore
script to work to get my database restored to a point in time?
Thanx!|||I do a full database backup, then an hour later I start my hourly log
backups.
I think I may need to do a log backup immediately after the database backup
if the log is somehow being truncated after the full backup, and before my
hourly log backup job starts. I need to have a full backup and an hourly
log backup.
The full backup is scheduled for 7:45, then the hourly log backups kick off
at 8:45. The first log backup I am trying to restore is the one right after
the full database backup, therefore, I guess the log is being truncated
somehow in that hour window.
Thanx for the tip on looking this up in MSDN.
--
JUDE
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23S2DROClDHA.2772@.TK2MSFTNGP12.phx.gbl...
> JLS
> This is telling you that you have a missing log between the database you
> restored and the log that you are trying to restore. The message is
telling
> you that you need the log backup containing:
> LSN 237424000000007700001
> The log you are attempting to restore starts with:
> LSN 237426000000004300001
> As you can see, your log start point is greater. If you have no log
backups
> between the database backup and the 7700001 log backup, then that may mean
> that the log was truncated during that period. When the chain of LSNs is
> incomplete, you cannot restore past the break.
> (In the MSDN, search for "SQL LSN" and you will get some background on
> this.)
> Russell Fields
>
> "JLS" <judy.shoop@.sartomer.com> wrote in message
> news:%23RvzWiBlDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I am restoring a production database onto a test server to a point in
time.
> I restore the database from script with no issue, but when I try to
restore
> the first of 6 logs, I receive the following message;
> Server: Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 237426000000004300001, which is
too
> late to apply to the database. An earlier log backup that includes LSN
> 237424000000007700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> What am I doing wrong? What does this mean and how do I get my log
restore
> script to work to get my database restored to a point in time?
> Thanx!
>|||Definitely, you don't have to do another log backup
straight after full backup.
When you backup transaction log to the same log backup
file, make sure you use WITH NOINIT option which do not
overwrite the previous backup. Or backup to the different
file name.
When you restore transaction log, you specify WITH FILE=[file no] to specify file number on the file.|||In addition, look in msdb, the backup history tables. These might assist in finding out where the
missing log backups went.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> Definitely, you don't have to do another log backup
> straight after full backup.
> When you backup transaction log to the same log backup
> file, make sure you use WITH NOINIT option which do not
> overwrite the previous backup. Or backup to the different
> file name.
> When you restore transaction log, you specify WITH FILE=> [file no] to specify file number on the file.|||Thanx Johnny, but I am doing everything that you have suggested. I backup
the log to the same backup file as the database, it is the first log file
backup after the full database backup, and when I restore I use WITH FILE
[file no]. The backup most certainly contains WITH NOINIT, until the full
database backup occurs the next night, this is when I INIT.
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> Definitely, you don't have to do another log backup
> straight after full backup.
> When you backup transaction log to the same log backup
> file, make sure you use WITH NOINIT option which do not
> overwrite the previous backup. Or backup to the different
> file name.
> When you restore transaction log, you specify WITH FILE=> [file no] to specify file number on the file.|||Ok, great! I will, because I would actually like to know what has happened.
Thanx!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OFpE3dIlDHA.1764@.tk2msftngp13.phx.gbl...
> In addition, look in msdb, the backup history tables. These might assist
in finding out where the
> missing log backups went.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Johnny" <anonymous@.discussions.microsoft.com> wrote in message
> news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> > Definitely, you don't have to do another log backup
> > straight after full backup.
> >
> > When you backup transaction log to the same log backup
> > file, make sure you use WITH NOINIT option which do not
> > overwrite the previous backup. Or backup to the different
> > file name.
> >
> > When you restore transaction log, you specify WITH FILE=> > [file no] to specify file number on the file.
>

Point in time restore problems

I have some problems using point in time restore. I use SQL 2000 SP3a on a W2000 server. I use the full recovery option. I start the week with a full backup and every 3 hours I take a log backup.
If I use the ''Point in time restore'' option, at the end of the restore, my database is ALWAYS in ''Loading'' state even if I checked the ''Leave database operational''. I know that I can use the ''RESTORE DATABASE XXX with recovery'' to bring it in normal mode but if I use this command every logs are restored and the date and the hour that I put in the ''Point in time restore'' is useless. The other thing that bug me is even if you put something in ''Point in time restore'' option, SQL doesent seem to take care of and it restore simply every backup and logs that are checked in the list in the bottom of the ''Restore database'' screen.
I use ''Point in time restore'' a lot in the SQL 7.0 but it's the first time in SQL 2000. Something change ?Could it be that the 'point in time' that you were trying to restore upto,
is later than the time period covered by the last log you restored?
I don't use Enterprise Manager for restoring or backing up databases, so I
cannot comment on their usability aspects.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Cris" <anonymous@.discussions.microsoft.com> wrote in message
news:F563BD78-B7C7-4BAA-8613-D1771338D88B@.microsoft.com...
I have some problems using point in time restore. I use SQL 2000 SP3a on a
W2000 server. I use the full recovery option. I start the week with a full
backup and every 3 hours I take a log backup.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. I know that I can use the ''RESTORE DATABASE XXX
with recovery'' to bring it in normal mode but if I use this command every
logs are restored and the date and the hour that I put in the ''Point in
time restore'' is useless. The other thing that bug me is even if you put
something in ''Point in time restore'' option, SQL doesent seem to take care
of and it restore simply every backup and logs that are checked in the list
in the bottom of the ''Restore database'' screen.
I use ''Point in time restore'' a lot in the SQL 7.0 but it's the first time
in SQL 2000. Something change ?

Point in Time Restore Part II

In the hereunder written message I talk about point in time restore.
It is now based upon the fact that there are no hardware problems or what so
ever.
I just would like to roll back to a situation of some time (minutes, hours
or what ever) ago.

Used to the ingres database a point in time restore can take place UP to
any, any, any time since the last FULL backup. (any time up to now !!!)

I can't understand why a point in time restore can only be done based upon
transaction log backups. The current transaction log is also available in my
opinion. (Turn off the power, turn on the power and you will notice that the
automatic recovery is based upon this transaction log file; so in that case
this file is used)

That's what my question is about. Is it correct that a point in time restore
in a SQL server environment can only be done up to the last transaction log
backup.

Bye

Arno de Jong,
The Netherlands.A.M. de Jong (arnojo@.wxs.nl) writes:
> Used to the ingres database a point in time restore can take place UP to
> any, any, any time since the last FULL backup. (any time up to now !!!)
> I can't understand why a point in time restore can only be done based
> upon transaction log backups. The current transaction log is also
> available in my opinion. (Turn off the power, turn on the power and you
> will notice that the automatic recovery is based upon this transaction
> log file; so in that case this file is used)
> That's what my question is about. Is it correct that a point in time
> restore in a SQL server environment can only be done up to the last
> transaction log backup.

Just because Ingress works in one way, there is no requirement for MS
SQL Server to work in that way too.

In SQL Server you need a transaction-log backup to do a point-in-time
restore, but since you can backup the transaction log at any point,
this is not any serious restriction.

I should add here that you must be running full or bulk_logged recovery
mode to have availability to point-in-time restore. With simple recovery
mode, this feature is not available.

In the case you want to undo a fatal SQL statement like an UPDATE without
a WHERE clause, you may be interested in exploring the third-party tool
Log Explorer from Lumigent, check out www.lumigent.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

point in time restore error

When i use point in time restoration, it show error STOPAT , restore og
terminates why? and i can restore it without use point in time restoration!
Please post the full text of the error.
Nik Marshall-Blank MCSD/MCDBA
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!
|||Hi,
STOPAT can be used in transaction log backup and only if your source
database is in FULL RECOVERY MODEL.
See the recovery model of your source database first.
The actual syntax is:-
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
-- Restore the log backup with stopat option
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
Thanks
Hari
SQL Server MVP
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!

point in time restore error

When i use point in time restoration, it show error STOPAT , restore og
terminates why? and i can restore it without use point in time restoration!Please post the full text of the error.
--
Nik Marshall-Blank MCSD/MCDBA
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!|||Hi,
STOPAT can be used in transaction log backup and only if your source
database is in FULL RECOVERY MODEL.
See the recovery model of your source database first.
The actual syntax is:-
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
-- Restore the log backup with stopat option
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
Thanks
Hari
SQL Server MVP
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!sql

point in time restore error

When i use point in time restoration, it show error STOPAT , restore og
terminates why? and i can restore it without use point in time restoration!Please post the full text of the error.
Nik Marshall-Blank MCSD/MCDBA
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!|||Hi,
STOPAT can be used in transaction log backup and only if your source
database is in FULL RECOVERY MODEL.
See the recovery model of your source database first.
The actual syntax is:-
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
-- Restore the log backup with stopat option
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
Thanks
Hari
SQL Server MVP
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9683A6B5-4609-450F-BC21-AD123E4DE8F3@.microsoft.com...
> When i use point in time restoration, it show error STOPAT , restore og
> terminates why? and i can restore it without use point in time
> restoration!

Point in time restore - cannot change date

I am trying to roll back a SQL Server 2000 database to a previous point
in time, but I am not allowed to select the date I want in the 'point
in time' dialog - it always reverts back to the date of the transaction
log backup.
So far, I have done the following, in the following order:
1. Backed up the database.
2. Backed up the transaction log.
3. Restored the database from a previous backup.
4. Do a 'point in time restore' from the transaction log backup
completed in step 2.
At step 4, if I follow through with the restore, choosing the date and
time of the log backup as my 'point in time', I sucessfully get the
data I started with.
Can anyone tell me why I cannot specify a 'point in time' of, say, five
days ago - and, more importantly, *how* to specify another point in
time? If the transaction log has the data to restore my database to the
point at which the log backup was taken, surely it has the data to
restore to a point a few days earlier.
Thanks,
Joejoe
Do you perfom it from EM, right?
There is very good topic ( with examples) about it in the BOL
"joe" <joe.hodsdon@.gmail.com> wrote in message
news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
>I am trying to roll back a SQL Server 2000 database to a previous point
> in time, but I am not allowed to select the date I want in the 'point
> in time' dialog - it always reverts back to the date of the transaction
> log backup.
> So far, I have done the following, in the following order:
> 1. Backed up the database.
> 2. Backed up the transaction log.
> 3. Restored the database from a previous backup.
> 4. Do a 'point in time restore' from the transaction log backup
> completed in step 2.
> At step 4, if I follow through with the restore, choosing the date and
> time of the log backup as my 'point in time', I sucessfully get the
> data I started with.
> Can anyone tell me why I cannot specify a 'point in time' of, say, five
> days ago - and, more importantly, *how* to specify another point in
> time? If the transaction log has the data to restore my database to the
> point at which the log backup was taken, surely it has the data to
> restore to a point a few days earlier.
> Thanks,
> Joe
>|||Hi Uri,
Thanks for the reply. Yes, I'm working in EM. I reviewed the Books
Online before doing anything - that's where I got much of my
instruction. According to the "How to restore a point in time" article
in BOL, I should be able to specify a date and time to which I want to
restore. But EM won't let me change the date.
Thanks again,
Joe
Uri Dimant wrote:[vbcol=seagreen]
> joe
> Do you perfom it from EM, right?
> There is very good topic ( with examples) about it in the BOL
> "joe" <joe.hodsdon@.gmail.com> wrote in message
> news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...|||Joe,
I remember an issue in SQL2000 with point in time restore from EM.
Actually the very first time you try to restore a database from EM
point in time restore doesn't work. You have to do it from QA using the
SQL statements. I thought it has been fixed in one of the service packs
but don't remember which one.
Markus|||Thank you, I'll give that a try.
Joe
MarkusB wrote:
> Joe,
> I remember an issue in SQL2000 with point in time restore from EM.
> Actually the very first time you try to restore a database from EM
> point in time restore doesn't work. You have to do it from QA using the
> SQL statements. I thought it has been fixed in one of the service packs
> but don't remember which one.
> Markus

Point in time restore - cannot change date

I am trying to roll back a SQL Server 2000 database to a previous point
in time, but I am not allowed to select the date I want in the 'point
in time' dialog - it always reverts back to the date of the transaction
log backup.
So far, I have done the following, in the following order:
1. Backed up the database.
2. Backed up the transaction log.
3. Restored the database from a previous backup.
4. Do a 'point in time restore' from the transaction log backup
completed in step 2.
At step 4, if I follow through with the restore, choosing the date and
time of the log backup as my 'point in time', I sucessfully get the
data I started with.
Can anyone tell me why I cannot specify a 'point in time' of, say, five
days ago - and, more importantly, *how* to specify another point in
time? If the transaction log has the data to restore my database to the
point at which the log backup was taken, surely it has the data to
restore to a point a few days earlier.
Thanks,
Joejoe
Do you perfom it from EM, right?
There is very good topic ( with examples) about it in the BOL
"joe" <joe.hodsdon@.gmail.com> wrote in message
news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
>I am trying to roll back a SQL Server 2000 database to a previous point
> in time, but I am not allowed to select the date I want in the 'point
> in time' dialog - it always reverts back to the date of the transaction
> log backup.
> So far, I have done the following, in the following order:
> 1. Backed up the database.
> 2. Backed up the transaction log.
> 3. Restored the database from a previous backup.
> 4. Do a 'point in time restore' from the transaction log backup
> completed in step 2.
> At step 4, if I follow through with the restore, choosing the date and
> time of the log backup as my 'point in time', I sucessfully get the
> data I started with.
> Can anyone tell me why I cannot specify a 'point in time' of, say, five
> days ago - and, more importantly, *how* to specify another point in
> time? If the transaction log has the data to restore my database to the
> point at which the log backup was taken, surely it has the data to
> restore to a point a few days earlier.
> Thanks,
> Joe
>|||Hi Uri,
Thanks for the reply. Yes, I'm working in EM. I reviewed the Books
Online before doing anything - that's where I got much of my
instruction. According to the "How to restore a point in time" article
in BOL, I should be able to specify a date and time to which I want to
restore. But EM won't let me change the date.
Thanks again,
Joe
Uri Dimant wrote:
> joe
> Do you perfom it from EM, right?
> There is very good topic ( with examples) about it in the BOL
> "joe" <joe.hodsdon@.gmail.com> wrote in message
> news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
> >I am trying to roll back a SQL Server 2000 database to a previous point
> > in time, but I am not allowed to select the date I want in the 'point
> > in time' dialog - it always reverts back to the date of the transaction
> > log backup.
> >
> > So far, I have done the following, in the following order:
> >
> > 1. Backed up the database.
> > 2. Backed up the transaction log.
> > 3. Restored the database from a previous backup.
> > 4. Do a 'point in time restore' from the transaction log backup
> > completed in step 2.
> >
> > At step 4, if I follow through with the restore, choosing the date and
> > time of the log backup as my 'point in time', I sucessfully get the
> > data I started with.
> >
> > Can anyone tell me why I cannot specify a 'point in time' of, say, five
> > days ago - and, more importantly, *how* to specify another point in
> > time? If the transaction log has the data to restore my database to the
> > point at which the log backup was taken, surely it has the data to
> > restore to a point a few days earlier.
> >
> > Thanks,
> > Joe
> >|||Joe,
I remember an issue in SQL2000 with point in time restore from EM.
Actually the very first time you try to restore a database from EM
point in time restore doesn't work. You have to do it from QA using the
SQL statements. I thought it has been fixed in one of the service packs
but don't remember which one.
Markus|||Thank you, I'll give that a try.
Joe
MarkusB wrote:
> Joe,
> I remember an issue in SQL2000 with point in time restore from EM.
> Actually the very first time you try to restore a database from EM
> point in time restore doesn't work. You have to do it from QA using the
> SQL statements. I thought it has been fixed in one of the service packs
> but don't remember which one.
> Markus

Point in time restore

Is it possible?

I have no Backups at all.
And I dropped a important table completely.
Is there anyway to roll back this command.

I am guessing that the transaction log keeps this inforation and its still there. I think with other 3rd party tools you can recover to a point in time without backups so there must be a way we can do it without 3rd party tools , cant it?

Your 'best' option at this point is to immediately download an eval copy of one of the Third party log tools. Many are fully functioning for 14-30 days.

But since you indicated you have no backups, if by some bad karma your database is in 'Simple' mode and you may be SOL.

|||Is there anyother way without 3rd party tools.
its a Full Recovery Model.
what is meant by > you may be SOL. <
|||

Download Lumnigents' Log Exlorer, or ApexSQL's SQLLog -or one of the several other good log tools.

It is just not worth any time and effort to try this without their help.

*SOL = "Sorry, Out of Luck"

|||

what is u r backup policy ... if u have full backup and subsequent transaction log backup it is possible to restore to the time just before u deleted the table... Read about STOP AT option in Restore Database...

suppose

(a) 06 Mar 07 you deleted the table at 1000 AM...

(b) U have full backup of 05 Mar 07 20:00 hrs (8 PM)

(c) your database is in Full recovery model

(d) you have no differential backup

(e) you have no TL backup

06 Mar 07 15:00 hrs you found that the data has been delete by mistake and u need to get the databack

(a) first and foremost thing is to take the TL backup

(b) restore the full backup of 05 Mar 07 20:00 hrs as a new database with No REcovery option

(c) Restore the TL with STOPAT 09:55 hrs with Recovery option

this is just a scenario which u can relate and try to get the data back

Madhu

|||I am aware of the stop at option and the scenario you mentioned.
But i am thinking of the worst-worst case scenario. What if you have no backups at all.
you deleted a table.
now u need to recover that table. How do you go about doing that without any backups.

If the history is kept in the transactional logs then it seems correct to assume that you can recover a table dropped 5 minutes before from the transactional logs.

Can we achive such a thing without using third party tools. worst case scenario.

Thanks everyone..|||

In simple terms, as I've tried to communicate to you a couple of times -NO!

If you need to do this, get a third party tool.

|||

Just out of interest, I've noticed that SQL 2005's RESTORE DATABASE command now has a STOPAT option, however BOL doesn't give much information away as to how/under what circumstances this option can be used.

I gave it quick a test earlier on today but couldn't get it to work. Does anyone have any experience of using it? I was wondering if it could possibly be of use to the original poster of this thread (i.e. by taking a full backup now, and if the log hasn't been backed-up, then is it possible to restore to a point in time earlier than the full backup? - my guess is 'probably not', although in theory surely this should be possible by examining the log and rolling back / undoing all uncommitted and comitted transactions to the point in time specified?).

Thanks
Chris

|||

It seems to me that if you make a 'Full Backup' at this juncture, ALL committed activities would be assumed to be in the database, and the transaction log would be truncated. Then not only would the table to permanently 'gone', but any record of it would no longer be in the transaction log.

The missing element is the previous 'Backup' -it does not exist, therefore, the only vestiges of the missing table (if other than page logging) 'might' be in the existing transaction log. Lunmigent's Log explorer would help find out IF there is any chance of recovering the data.

|||

Just one thing, though, taking a full database backup does not truncate the transaction log - this is one of the reasons why I was wondering whether the STOPAT option in the RESTORE DATABASE command would be of use. Agreed that a log-reader tool would be a good tool to invest in / trial if not.

Chris

point in time restore

My database is in full recovery mode. When I have created some full backups of the database, I would like try to point in time restore. Unfortunately, this option is greyed out on the restore screen. What have I done wrong?From the holy book once again

Recovering to a Point In Time

You can recover to a point in time by recovering only the transactions that occurred before a specific point in time within a transaction log backup, rather than the entire backup. By viewing the header information of each transaction log backup or the information in the backupset table in msdb, you can quickly identify which backup contains the time to which you want to restore the database. You then need only apply transaction log backups up to that point.

You cannot skip specific transactions. This would compromise the integrity of the data in the database. Any transactions that occur after the transaction you want to undo might depend on the data modified by the undone transaction.

If you do not want to restore any modifications made to the database after a specific point in time:

Restore the last database backup without recovering the database.
Apply each transaction log backup in the same sequence in which they were created.
Recover the database at the desired point in time within a transaction log backup.
This process also can be used to restore a database and transaction logs if some transaction log backups created after a point in time are missing or damaged.

Point-in-time recovery is not supported with the Bulk-Logged Recovery model. Bulk-Logged recovery only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.sql

point in time restore

hello everybody
i just tried to do the exercise of point in time restore with northwind
database.
First i took a complete database backup of northwind
then after 10 min i took a transaction log backup of northwind
now when i try to restore the northwind database
while restoring i get this erros as
"invalid value specified for stopat parameter, restore log is terminating
abnormally
also now my northwind database is showing as loading & nothing inside this
database is showing
can anyone please suggest where i'm wrong & what needs to be taken care
about while doing point in time restore.
Hi
Post the T-SQL code you used to do the restore.
Regards
Mike
"vishal" wrote:

> hello everybody
> i just tried to do the exercise of point in time restore with northwind
> database.
> First i took a complete database backup of northwind
> then after 10 min i took a transaction log backup of northwind
> now when i try to restore the northwind database
> while restoring i get this erros as
> "invalid value specified for stopat parameter, restore log is terminating
> abnormally
> also now my northwind database is showing as loading & nothing inside this
> database is showing
> can anyone please suggest where i'm wrong & what needs to be taken care
> about while doing point in time restore.
>
>

Point in Time RESTORE

I have SQL Server Standard edition (that was installed
after un-installing the Enterprise evaluation copy using
add/remove programs). On Retore screen, the option "Point
in Time' is grayed out totally. How can I make this option
usable?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0375_01C3B80E.61607470
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Your database must be using the Full recovery model - and you must backup
the database as well as the log. Use the STOPAT option when restoring logs.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Neeru" <anonymous@.discussions.microsoft.com> wrote in message
news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
I have SQL Server Standard edition (that was installed
after un-installing the Enterprise evaluation copy using
add/remove programs). On Retore screen, the option "Point
in Time' is grayed out totally. How can I make this option
usable?
Thanks.
--=_NextPart_000_0375_01C3B80E.61607470
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Your database must be using the Full =recovery model - and you must backup the database as well as the log. Use =the STOPAT option when restoring logs.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Neeru" wrote in message news:019f01c3b837$a9=8a48b0$a301280a@.phx.gbl...I have SQL Server Standard edition (that was installed after =un-installing the Enterprise evaluation copy using add/remove programs). On Retore =screen, the option "Point in Time' is grayed out totally. How can I make this =option usable? Thanks.

---

--=_NextPart_000_0375_01C3B80E.61607470--|||Thanks, Tom. I used the Maintenace Wizard to create backup
jobs. Is there an option not to use Full Recovery model?
Neeru
>--Original Message--
>Your database must be using the Full recovery model - and
you must backup
>the database as well as the log. Use the STOPAT option
when restoring logs.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Neeru" <anonymous@.discussions.microsoft.com> wrote in
message
>news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
>I have SQL Server Standard edition (that was installed
>after un-installing the Enterprise evaluation copy using
>add/remove programs). On Retore screen, the option "Point
>in Time' is grayed out totally. How can I make this option
>usable?
>Thanks.
>|||Neeru:
If you want "Point in Time" be enabled then the option "Recovery Model" of
your database must be set to "Full Recovery". To set this option using
Enterprise Manager--> (1) Right Click your database and then click
"Properties" (2) Then click "Options" set and choose "Full" as Recovery
Model.
By this......">> >Your database must be using the Full recovery model -
and you must backup the database as well as the log. >>" Tom meant to
say the same thing but I think you undertood that you are not seeing
"Point-In-Time" because your database is set to "Full Recovery"
"Neeru" <anonymous@.discussions.microsoft.com> wrote in message
news:a85a01c3b83a$629742c0$a601280a@.phx.gbl...
> Thanks, Tom. I used the Maintenace Wizard to create backup
> jobs. Is there an option not to use Full Recovery model?
> Neeru
> >--Original Message--
> >Your database must be using the Full recovery model - and
> you must backup
> >the database as well as the log. Use the STOPAT option
> when restoring logs.
> >
> >--
> >Tom
> >
> >----
> --
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >
> >
> >"Neeru" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
> >I have SQL Server Standard edition (that was installed
> >after un-installing the Enterprise evaluation copy using
> >add/remove programs). On Retore screen, the option "Point
> >in Time' is grayed out totally. How can I make this option
> >usable?
> >Thanks.
> >|||THANKS, DON and Tom.
It worked. Have a nice week.
Neeru
>--Original Message--
>Neeru:
>If you want "Point in Time" be enabled then the
option "Recovery Model" of
>your database must be set to "Full Recovery". To set this
option using
>Enterprise Manager--> (1) Right Click your database and
then click
>"Properties" (2) Then click "Options" set and
choose "Full" as Recovery
>Model.
>By this......">> >Your database must be using the Full
recovery model -
>and you must backup the database as well as the log.
>>" Tom meant to
>say the same thing but I think you undertood that you are
not seeing
>"Point-In-Time" because your database is set to "Full
Recovery"
>
>"Neeru" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a85a01c3b83a$629742c0$a601280a@.phx.gbl...
>> Thanks, Tom. I used the Maintenace Wizard to create
backup
>> jobs. Is there an option not to use Full Recovery model?
>> Neeru
>> >--Original Message--
>> >Your database must be using the Full recovery model -
and
>> you must backup
>> >the database as well as the log. Use the STOPAT option
>> when restoring logs.
>> >
>> >--
>> >Tom
>> >
>> >----
--
>> --
>> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >SQL Server MVP
>> >Columnist, SQL Server Professional
>> >Toronto, ON Canada
>> >www.pinnaclepublishing.com/sql
>> >
>> >
>> >"Neeru" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
>> >I have SQL Server Standard edition (that was installed
>> >after un-installing the Enterprise evaluation copy
using
>> >add/remove programs). On Retore screen, the
option "Point
>> >in Time' is grayed out totally. How can I make this
option
>> >usable?
>> >Thanks.
>> >
>
>.
>

Point in time restore

Hi All
I have database that has Recovery Model property set to Full
since creation.
I have never performed backup of this database.
Is it possible to do "Point in time" restore for this database?No. All restores are based on that you have done a backup in the first place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Denis" <trud81@.hotmail.com> wrote in message news:027701c3c486$e373e170$a001280a@.phx.gbl...
> Hi All
> I have database that has Recovery Model property set to Full
> since creation.
> I have never performed backup of this database.
> Is it possible to do "Point in time" restore for this database?
>

point in time restore

hello everybody
i just tried to do the exercise of point in time restore with northwind
database.
First i took a complete database backup of northwind
then after 10 min i took a transaction log backup of northwind
now when i try to restore the northwind database
while restoring i get this erros as
"invalid value specified for stopat parameter, restore log is terminating
abnormally
also now my northwind database is showing as loading & nothing inside this
database is showing
can anyone please suggest where i'm wrong & what needs to be taken care
about while doing point in time restore.Hi
Post the T-SQL code you used to do the restore.
Regards
Mike
"vishal" wrote:
> hello everybody
> i just tried to do the exercise of point in time restore with northwind
> database.
> First i took a complete database backup of northwind
> then after 10 min i took a transaction log backup of northwind
> now when i try to restore the northwind database
> while restoring i get this erros as
> "invalid value specified for stopat parameter, restore log is terminating
> abnormally
> also now my northwind database is showing as loading & nothing inside this
> database is showing
> can anyone please suggest where i'm wrong & what needs to be taken care
> about while doing point in time restore.
>
>

POINT IN TIME RESTORE

When I try to restore a database with query analyser and point in time resto
re, I always obtain this error message : The log in this backup set terminat
es at LSN 69508000000440000001, which is too early to apply to the database.
A more recent log backup t
hat includes LSN 69508000000440300001 can be restored.
My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
RESTORE LOG TEST1 FROM BBA
WITH recovery, STOPAT = '2004-03-07 8:30:00'
I have the same problem with all my databases and if I look in my device BBA
, I can see that I start with a full backup on 2004-03-07 and I continue wit
h logs backup until 2004-03-13.
What's wrong ?I forgot to mention that my full backup were at 5:00 AM on 2004-03-07|||Is there only 1 tlog created upto "8:30" and after?
If there are multiple tlogs you'll have to issue something like...
restore database dbname.bak from bba with norecovery
restore log logname1.tlog from bba with norecovery
restore log logname2.tlog from bba with recovery, stopat = '2004-03-07 8:30:
00'
Regards,
Craig.
Cris wrote:

> When I try to restore a database with query analyser and point in time restore, I
always obtain this error message : The log in this backup set terminates at LSN 6950
8000000440000001, which is too early to apply to the database. A more recent log bac
kup
that includes LSN 69508000000440300001 can be restored.
> My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
> RESTORE LOG TEST1 FROM BBA
> WITH recovery, STOPAT = '2004-03-07 8:30:00'
> I have the same problem with all my databases and if I look in my device B
BA, I can see that I start with a full backup on 2004-03-07 and I continue w
ith logs backup until 2004-03-13.
> What's wrong ?|||Hi,
To add on to craigs posting,
The POINT-IN-TIME recovery will work only if the database's Recovery moel is
set to "FULL" and the model is not
changed after theFULL database backup.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
MCDBA
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:#T$bgUICEHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is there only 1 tlog created upto "8:30" and after?
> If there are multiple tlogs you'll have to issue something like...
> restore database dbname.bak from bba with norecovery
> restore log logname1.tlog from bba with norecovery
> restore log logname2.tlog from bba with recovery, stopat = '2004-03-07
8:30:00'
> Regards,
> Craig.
> Cris wrote:
>
restore, I always obtain this error message : The log in this backup set
terminates at LSN 69508000000440000001, which is too early to apply to the
database. A more recent log backup that includes LSN 69508000000440300001
can be restored.
BBA, I can see that I start with a full backup on 2004-03-07 and I continue
with logs backup until 2004-03-13.|||Sorry to jump in, I have read the thread and it is close to what I
would like to accomplish but not quite.
I have a perfefctly good database with a .mdf and a .ldf file. The
recovery model is FULL. It was a new database a few weeks ago for
developing a new product.
We entered all kinds of info into it before we realized we were not
backing it up. Today we erased all the data...oops..
So I have a good .mdf and a full .ldf, I do a backup of both (wrong
thing)? And I try to restore using the cdoe I have seen here before..
RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
NORECOVERY;
RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
STOPAT = 'Mar 18, 2004 10:00 AM';
This runs without error but restores the database to its CURRENT state
which is empty of data!
If I run the same from Enterprise Manager and I check the "Point in
time" it will not allow me to select anything but today.
It seems that I should be able to do this since I have a full .ldf
file but the method is beyond my technical skills.
Any ideas?
Thanks
Tim|||You haven't provided us with exact information of what you have, and at
which point in time those backups (loosely speaking) were taken.

> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
OK, so what we need to know is, compared to this point in time (when you
erased all data), what file backups (ldf, mdf) and database backups (BACKUP
DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
on a time line compared to the erase of the data.

> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)?
You generally don't do backup of the underlying database files in SQL
Server. Use BACKUP DATABASE and BACKUP LOG instead.
Then you continue to say that you use the RESTORE command, but you haven't
given us the information about when you did the BACKUP DATABASE and BACKUP
LOG.
You are probably toast, though. My guess is that you have some old file
level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
LOG. You then put back those old database files, but want to come closer to
the point of disaster. So you do the RESTORE DATABASE. And we don't need to
go no further, because that put the database in the same state as it was
when the backup command was taken (which was after the point of disaster).
You really need to read the sections about backup and restore in Books
Online to understand how these things work in SQL Server. If you want more
help with this particular situation, please provide more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403221607.6e054dc9@.posting.google.com...
> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Tim|||Tibor,
Thanks for trying to help a dummy
You are right on many fronts
a) I need more knowledge in BOL
b) I didn't decribe clearly, due to "a"
Here is the timeline
1- create database
2- enter data
3- erase data
4 - enterprise manager backup database, backup transaction log
5- restore database with norecovery
6- restore log with recovery, stopat (date time before data erased)
I use osql to run the job, the job runs but all the data is still gone
If I run the same restore from enterprise manager I am unable to
select a date prior to the time of the 1st transaction log backup.
I guess that is the point, you have to have a database backup from
BEFORE the point in time you want to restore to...
I am toast eh?
Tim
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#
6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
> You haven't provided us with exact information of what you have, and at
> which point in time those backups (loosely speaking) were taken.
>
> OK, so what we need to know is, compared to this point in time (when you
> erased all data), what file backups (ldf, mdf) and database backups (BACKU
P
> DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
> on a time line compared to the erase of the data.
>
> You generally don't do backup of the underlying database files in SQL
> Server. Use BACKUP DATABASE and BACKUP LOG instead.
> Then you continue to say that you use the RESTORE command, but you haven't
> given us the information about when you did the BACKUP DATABASE and BACKUP
> LOG.
> You are probably toast, though. My guess is that you have some old file
> level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
> LOG. You then put back those old database files, but want to come closer t
o
> the point of disaster. So you do the RESTORE DATABASE. And we don't need t
o
> go no further, because that put the database in the same state as it was
> when the backup command was taken (which was after the point of disaster).
> You really need to read the sections about backup and restore in Books
> Online to understand how these things work in SQL Server. If you want more
> help with this particular situation, please provide more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tim" <tsullivan@.websul.net> wrote in message
> news:a1920b75.0403221607.6e054dc9@.posting.google.com...|||This is what you should have done in order to do the restore as you wish:

> 1- create database
> 2- enter data
BACKUP DATABASE
> 3- erase data
BACKUP LOG
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
The BACKUP DATABAE can of course be at an earlier point in time, but not
later, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403230651.23d72ffe@.posting.google.com...
> Tibor,
> Thanks for trying to help a dummy
> You are right on many fronts
> a) I need more knowledge in BOL
> b) I didn't decribe clearly, due to "a"
> Here is the timeline
> 1- create database
> 2- enter data
> 3- erase data
> 4 - enterprise manager backup database, backup transaction log
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
> I use osql to run the job, the job runs but all the data is still gone
> If I run the same restore from enterprise manager I am unable to
> select a date prior to the time of the 1st transaction log backup.
> I guess that is the point, you have to have a database backup from
> BEFORE the point in time you want to restore to...
> I am toast eh?
> Tim
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message news:<#6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
(BACKUP
it
haven't
BACKUP
to
to
disaster).
more|||Try like this
[url]http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp[/ur
l]

> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Timsql