February 15, 2011 at 4:40 am
Hi,
Whilst indulging in a spot of 'late night coding' last night, I've managed to do the classic update without a where clause. D'oh.
My db was set to simple recovery mode, with an overnight backup. I immediately disabled the backup job, as I didn't have time to fix the error there and then, so no backup was taken last night and I have the full incremental tlog since the last backup.
I'm not a DBA and my client doesn't have anyone competent to turn to. So, I was hoping someone might do me a favour and review my plan and advise on whether this is a sensible approach:
Step 1: Switch db to full recovery mode.
Step 2: Script:
----------------------------------------
-- transfer data and objects to a new db
----------------------------------------
-- pre-existing procs used to create new db
-- and to copy objects from old to new
exec Dev_Utils.dbo.Make_Database
@db_name = 'Control_Tables_Copy'
,@db_size = '1024'
,@debug = 0
go
exec Dev_Utils.dbo.up_Transfer_Objects
@source_db = 'Control_Tables'
,@destination_db = 'Control_Tables_Copy'
,@transfer_procs = 1
,@transfer_funcs = 1
,@transfer_tables = 1
,@preserve_data = 1
,@debug = 0
GO
-----------------------
-- identify update time
-----------------------
DBCC log ('Control_Tables',3)
-- update details
/*
description: UPDATE;0x0105000000000005150000005946532b7f4b314ad6540e2595480000
begin time: 2011/02/14 23:44:17:663
end time of previous transaction: 2011/02/14 23:44:01:787
*/
----------------------------------------------------
-- backup tail of transaction log without truncating
----------------------------------------------------
backup log Control_Tables to disk = N'E:\Data Transformation\SQL Backup\Control_Tables_Tlog.trn'
with
no_truncate
, noformat
, noinit
, name = N'Control_Tables-Transaction Log Backup'
, skip
, norewind
, nounload
, norecovery
, stats = 10
, checksum
go
-- verify
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'Control_Tables'
and backup_set_id=(
select max(backup_set_id)
from msdb..backupset
where database_name=N'Control_Tables'
)
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''Control_Tables'' not found.', 16, 1)
end
restore verifyonly from disk = N'E:\Data Transformation\SQL Backup\Control_Tables.trn'
with
file = @backupSetId
, nounload
, norewind
go
----------------------
-- restore last backup
----------------------
restore database Control_Tables from disk = N'C:\Control_Tables.bak'
with file = 1
, norecovery
, nounload
, replace
, stats = 10
go
--------------------------------
-- restore tlog to point in time
--------------------------------
restore log Control_Tables from disk = N'E:\Data Transformation\SQL Backup\Control_Tables_Tlog.trn'
with
file = 1
, recovery
, stopat = '14 February 2011 22:44:02'
go
Step 3: Re-run scripts that were executed after the error.
Assumptions:
Restoring to any point between the end time of the previous tran and the start time of my erroneous tran is fine.
Question:
I can rerun the additional SQL that was run after the error, but should I be looking for any system actions that will need to be re-run?
Regards, Iain
February 15, 2011 at 5:07 am
You do not have the full transaction log since the last backup. Since the database was in simple recovery, the log would have been getting truncated whenever a checkpoint ran (every couple of minutes).
The backup log that you are planning to run will fail. It will fail because there needs to be a full backup taken after the switch to full recovery, and then the log backup will only contain the changes since that full backup. Before that point the log records were being truncated on checkpoint and overwritten.
I suspect the best you're going to be able to do here is restore the last full backup you had and copy over what you had in there at the backup time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 6:13 am
Hi GilaMonster,
Thanks for the reply. Recovery interval is SQL managed. My db is not very transactional, so checkpoints are infrequent.
I've checked using the LOP_BEGIN_CKPT,LOP_END_CKPT operations in the log and the last checkpoint on the db is concurrent with the last db backup.
Does your advice still hold?
Cheers, Iain
February 15, 2011 at 6:24 am
If it's just the one table that was affected, could you not restore the full backup into a new database and then update the live database's table with values from its equivalent in this new restore?
February 15, 2011 at 6:25 am
Yes.
The database is in simple recovery model. No log backups are possible. Upon switch to full recovery you will have to take a full backup before you start taking log backups and then the log backup will only cover the period from the full backup in full recovery up to the log backup.
No point in time restore is possible in simple recovery, no log backup can cover a period of time where the database was in simple recovery.
Since the log is intact, you could buy a log reader tool (like Apex SQL Log Rescue) and use that, but they start at around $1000 and you will need the full version to recover any data
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 6:35 am
Hi Mazzz, yes, that's the other option. I just have about a day's worth of changes to apply to the backup. It isn't the end of the world, but is still a bit of a pain.
GilaMonster, thanks, I'll pass on the $1000 option 🙂
At least I now know a few things I didn't this morning, so I'll take that as a positive. I'd like to say that I'll also stop coding past bedtime, but somehow, I think it might not be an option 🙂
Regards, Iain
February 15, 2011 at 7:05 am
On a side note, are you developing against the production database?
You really, really, really should not do that, for at least the reason you just discovered. If you take your client's data and business seriously, you'll get a copy of the database and develop and test there so that you protect the clients data at all time.
Also, you might want to consider setting up the database in full recovery with log backups. Remember, Murphy's Law applies to databases.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 15, 2011 at 7:15 am
Grant Fritchey (2/15/2011)
On a side note, are you developing against the production database?
Never. I might not be a DBA, but I'm also not a *complete* idiot 😀
On the recovery model front, you're absolutely right. I'm used to having a friendly DBA around to make sure things like this are looked after. That said, I code pessimistically, expecting that things will fail and that cock ups might happen. I really should have taken this approach to setting up my dev db. For the sake of saving a bit of disk space, I've had a pain in the backside job to do today.
I think someone once said that mistakes are the schoolbooks from which we learn - well, I guess I've learned something today...
Cheers, Iain
Edit: typos
February 15, 2011 at 7:18 am
irobertson (2/15/2011)
Never. I might not be a DBA, but I'm also not a *complete* idiot 😀
😀
We're so used to DBAs with no backups of production databases that many of us assume the worst so that we only get pleasant surprises. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 7:22 am
Grant Fritchey (2/15/2011)
On a side note, are you developing against the production database?You really, really, really should not do that, for at least the reason you just discovered. If you take your client's data and business seriously, you'll get a copy of the database and develop and test there so that you protect the clients data at all time.
Also, you might want to consider setting up the database in full recovery with log backups. Remember, Murphy's Law applies to databases.
Ha! Sorry, didn't mean to imply that. Best of luck!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 15, 2011 at 7:26 am
lol, no worries. It's times like these that my favourite Douglas Adams quote comes to mind:
"He attacked everything in life with a mix of extraordinary genius and naive incompetence, and it was often difficult to tell which was which."
So, you're probably right to ask the question 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply