August 3, 2015 at 10:25 am
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
August 3, 2015 at 11:15 am
The problem is, you can't restore parts of transactions. Instead of restoring to your existing database, I'd suggest restoring your database to the point prior to the delete, recover just the information that was deleted and insert that back into the existing database (not using any restore functions, just INSERT ... SELECT). That way you can keep the data that was entered today.
To do this, you'll need a place that has enough space to let you restore a second copy of your database. Other than that, it's a straight forward point in time restore. Here's an article I wrote[/url] on it a while ago. It's still applicable for your standard restores.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2015 at 11:22 am
Restore the full backup as a new database, restore the transaction log backups until just before the data was deleted. Bring the DB online. Copy the deleted rows over to the read database.
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
August 3, 2015 at 11:30 am
I have full backup taken every day at 10 PM and transaction log every 15 mins.
So, the data got deleted around 3:30 PM on Friday.
In order to restore I have to use :
Thursday Full backup which was taken at 10 PM and do I need to use all the transactional log backups from (thursday) 10:15 PM till Friday 3:30 PM.
Can you send me syntax?
August 3, 2015 at 11:34 am
Yes, you'll need all of them
Syntax is nothing fancy. A RESTORE DATABASE with the norecovery option, a bunch of RESTORE LOG with the NORECOVERY and STOPAT options (where stop at is a time just before the delete happened).
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
August 3, 2015 at 12:06 pm
PJ_SQL (8/3/2015)
I have full backup taken every day at 10 PM and transaction log every 15 mins.So, the data got deleted around 3:30 PM on Friday.
In order to restore I have to use :
Thursday Full backup which was taken at 10 PM and do I need to use all the transactional log backups from (thursday) 10:15 PM till Friday 3:30 PM.
Can you send me syntax?
Take a look at the article I referenced. It shows the syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2015 at 2:23 pm
Actually, I would need to restore on new database full backups and all the logs, what would be the process?
August 3, 2015 at 4:47 pm
Pretty much exactly the same as any other restore. The only exception is you'll need to use the WITH MOVE option to change the location of the files. Here's the documentation on the RESTORE command. There's an example of WITH MOVE there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2015 at 9:14 am
Thanks you All.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply