March 16, 2010 at 9:01 pm
Is there anyway at all even by hacking to apply transaction logs to an existing database without having to do a full restore?
In other words, user joe deleted 5 tables at 5:00 and I would like to roll back those transactions to 4:59 without restoring the database. Mainly because the database is so large and it would take so much time.
Thanks
March 16, 2010 at 11:42 pm
You will need to do a full restore.
There are tools that will read the transaction log and some say they can rollback a transaction. I have not successfully rolled back a transaction using them though (have tried a couple). That's not to say that they may or may not work for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 12:29 am
This is very common issue. But no out box solution for this.
You can have another database created from full back up. Then restore log backups on that up to particular point and copy the tables back to the original database.
March 17, 2010 at 4:38 am
Point in Time restores (which is what you're referring to) cannot be done without starting from the last FULL backup. You can't re-apply Transaction Logs (or roll them back) to a database without doing this step. However, as previously suggested, you could restore to a separate server / instance / database and just fix the tables.
If you don't know for sure what changes were made, there are third party tools for reading Transaction Logs. You can use that to figure out what was deleted / updated / inserted and then manually write T-SQL to reverse the effects of the transactions in question.
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.
March 17, 2010 at 4:41 am
I should add that the use of Partial Backups or File / Filegroup Backups (if you do use them) can be used to restore your tables if you happen to have that filegroup backed up by itself. I've never done these types of restore myself, but BOL should have plenty of information on the process of restoring them.
I do recall hearing that a File / Filegroup can be restored without having to restore the entire DB.
March 17, 2010 at 9:33 am
Brandie Tarvin (3/17/2010)
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.
Excellent recommendation. These can also be used to notify the DBA that somebody made an attempt to alter a table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 9:36 am
Brandie Tarvin (3/17/2010)
I should add that the use of Partial Backups or File / Filegroup Backups (if you do use them) can be used to restore your tables if you happen to have that filegroup backed up by itself. I've never done these types of restore myself, but BOL should have plenty of information on the process of restoring them.I do recall hearing that a File / Filegroup can be restored without having to restore the entire DB.
Filegroup backups and Piecemeal restores are a most excellent choice in SQL 2005 and above. You may have to restore multiple filegroups, but it could save you a substantial amount of time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 2:52 pm
Brandie Tarvin (3/17/2010)
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.
Would you base the trigger on a specific user(s) to avoid legitimate updates by an application ?
March 17, 2010 at 2:54 pm
homebrew01 (3/17/2010)
Brandie Tarvin (3/17/2010)
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.Would you base the trigger on a specific user(s) to avoid legitimate updates by an application ?
Why would an application be allowed to create or drop tables? Or alter a table definition (besides Microsoft CRM)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 2:57 pm
Wasn't an app. It was a developer dba running a script that dropped the tables. Thought I could go back in time to a point without having tio restore full database.
JT
March 17, 2010 at 3:07 pm
vidya_pande (3/17/2010)
This is very common issue. But no out box solution for this.You can have another database created from full back up. Then restore log backups on that up to particular point and copy the tables back to the original database.
You would only want to use this solution if you are sure that no other DML's have been run against these tables, otherwise you would lose this data when copying the tables from the database restored to a point in time on a new server.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
March 17, 2010 at 3:12 pm
CirquedeSQLeil (3/17/2010)
homebrew01 (3/17/2010)
Brandie Tarvin (3/17/2010)
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.Would you base the trigger on a specific user(s) to avoid legitimate updates by an application ?
Why would an application be allowed to create or drop tables? Or alter a table definition (besides Microsoft CRM)?
By the time I typed my post, my brain had changed the scenario to deleting "records" not "tables" ..... never mind ....... I'm going home now.
March 17, 2010 at 4:14 pm
homebrew01 (3/17/2010)
CirquedeSQLeil (3/17/2010)
homebrew01 (3/17/2010)
Brandie Tarvin (3/17/2010)
Thirdly, you can implement the use of DDL statements in your code to prevent this happening in the future. That way, it'll refuse to make the changes when Joe User tries this stunt again.Would you base the trigger on a specific user(s) to avoid legitimate updates by an application ?
Why would an application be allowed to create or drop tables? Or alter a table definition (besides Microsoft CRM)?
By the time I typed my post, my brain had changed the scenario to deleting "records" not "tables" ..... never mind ....... I'm going home now.
Oh ok, that makes perfect sense to me.
Thanks:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 3:59 am
jorge.torralba (3/17/2010)
Wasn't an app. It was a developer dba running a script that dropped the tables. Thought I could go back in time to a point without having tio restore full database.
Only if you've actually already done File / Filegroup or Partial backups. And even then, it isn't a true P.I.T. restore.
CirquedeSQLeil (3/17/2010)
Filegroup backups and Piecemeal restores are a most excellent choice in SQL 2005 and above. You may have to restore multiple filegroups, but it could save you a substantial amount of time.
FYI: File / Filegroup restores were part of SQL Server *before* SQL 2005. Only Partial backups are new to 2005. So if you do use the F/FG backup method for SQL 2000, you can use it to restore in SQL 2000 & 2005 (keeping in mind you first have to restore the full db to SQL 2005 and it will be upgraded). Not sure if it was available earlier, though.
March 18, 2010 at 9:12 am
Brandie Tarvin (3/18/2010)
... (keeping in mind you first have to restore the full db to SQL 2005 and it will be upgraded).
You only need to restore the Primary Filegroup (the filegroup containing the system objects). This filegroup can be as small as 50mb. However, after that, you would need to restore each filegroup necessary to access the requisite data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply