Point in time, UNDO/Restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Point in time, UNDO/Restore

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 14:09:07

I have full Recovery mode.

I just accidently deleted a handful of records out of a table about 10 minutes ago. My last backup was last night.

Can I go back 10 minutes ago for a specific table? If so how... DO I need to backup now, then use the trans-logs to recover up to a specific point?

Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 14:14:52

Restore your last full backup. Then restore each of the transaction logs that have occurred since then including the one that contains this delete. On the last tlog restore, use the STOPAT option in the WITH portion of the RESTORE LOG command. You'll need to also use the WITH NORECOVERY or WITH STANDBY for each restore until you are done restoring. On the file restore, you use WITH RECOVERY to bring the database online at the end.

Performing a full backup now will not help you with this restore, but it also won't hurt anything. It's pointless to do it right now though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 14:16:24

I'm going to move this thread over to the admin forum since it's more appropriate than the t-sql forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 14:40:51

Can you please clarify this:

Are you saying I should do another backup now (My last full backup wass last night), then restore from that backup with the Stopat time?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 14:47:00

No. Like I said running a full backup is pointless here as it won't be used to restore your data.

Do you have all of the transaction log backups after your last full backup from last night?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 14:55:20

I believe I am likley not backing up properly. I backup my logs at the same time as my db via a script each night. The last time the below code was ran was at 12:30am today.


Backup Database mydb to Disk = @Path1
backup log mydb to Disk = @Path2

I have not run a backup log since. Is this OK?

I appologize, but I am not very familiar with this.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 14:58:50

You should be backing up your tlogs more frequently as with your current implementation you could lose a full day's worth of data. We backup our tlogs every 15 minutes so we should only lose up to 15 minutes of data in the case of a major problem.

So now you'll need to run a manual backup log to get the delete entries. Then restore your full backup from last, restore your tlog backup from last night, restore your new tlog backup and provide the STOPAT option. With the first two restores, make sure to provide the WITH NORECOVERY option to let it know you intend to apply more backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 15:00:29

Check out my maintenance routines to see how I perform backups. My job schedule is that isp_Backup for the fulls is run once nightly and then isp_Backup for the tlog is run every 15 minutes.

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 15:26:09

I followed the instructions, but I can not get the db to recover


declare @dt as datetime
set @DT = '2007-11-28 12:47:000'

RESTORE DATABASE MYDB
	FROM DISK = 'C:\MYDB.bak'
	WITH
		REPLACE,
		NORECOVERY
GO

RESTORE LOG MYDB
FROM DISK = 'C:\MYDB_LOG11-28-2007.bak' --Most recent
WITH RECOVERY, STOPAT = @Dt,
FILE = 1

Shouldn't this bring the db out of Restoring mode?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 15:29:41

You need to apply the transaction log backup from last night as well. So you should have another restore log command in there. It goes in between your two current commands.

Also posting the error is always helpful.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 15:39:59

My db is not coming out of restoring mode. I attached the output messages and my exact code.


declare @dt as datetime
set @DT = '2007-11-28 12:47:000'

RESTORE DATABASE SpProductionDB
	FROM DISK = 'C:\DellSqlBackup\SpProducitonDb11-27-2007.bak'
	WITH REPLACE, NORECOVERY

RESTORE LOG SpProductionDB
FROM DISK = 'C:\DellSqlBackup\SpProducitonDb_LOG11-27-2007.bak'
WITH NORECOVERY, STOPAT = @Dt,
FILE = 1

RESTORE LOG SpProductionDB
FROM DISK = 'C:\DellSqlBackup\SpProducitonDb_LOG11-28-2007.bak'
WITH RECOVERY, STOPAT = @Dt,
FILE = 2

Processed 0 pages for database 'SpProductionDB', file 'SPProductionDBNew_Data' on file 1.
Processed 372 pages for database 'SpProductionDB', file 'SPProductionDBNew_Log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 372 pages in 0.338 seconds (9.008 MB/sec).
Processed 0 pages for database 'SpProductionDB', file 'SPProductionDBNew_Data' on file 2.
Processed 2 pages for database 'SpProductionDB', file 'SPProductionDBNew_Log' on file 2.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 2 pages in 0.139 seconds (0.088 MB/sec).

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-28 : 15:42:10

Now try this:

restore database SpProductionDB with recovery

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 15:44:46

I am not familiar with those messages. I doubt that it matters, but remove the STOPAT option in your first RESTORE LOG command. You only need that on the last one that contains this date/time.

Are there multiple backups in your files? I'm curious why you are using the FILE option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 15:46:59

The problem was one of the logs had more then 1 file. Thanks for all your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 15:50:57

quote:
Originally posted by Van

Now try this:

restore database SpProductionDB with recovery

You don't need that command since his last RESTORE LOG command includes the WITH RECOVERY option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-28 : 15:54:52

Yea I know, but since he was getting errors that I was unfamilar with, I wondered if maybe that would work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources