August 12, 2011 at 3:55 pm
I did not intend to offend anyone.
I just thought that this could be a practice exercise in the event of a issue in the future.
I prefer Lightspeed to perform object level backup and restores although it is not cheap.
If you perform a SELECT INTO you are unable to perform a point in time recovery.
If you just want to make a copy of the data in a table to be safe that is good.
I would not rely on SELECT INTO as a Backup and recovery strategy.
Here is the link to Lightspeed.
http://www.sqldbops.com/2010/01/on-ms-sql-how-to-restore-table-or.html
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 13, 2011 at 1:20 am
Welsh Corgi (8/12/2011)
I did not intend to offend anyone.I just thought that this could be a practice exercise in the event of a issue in the future.
I prefer Lightspeed to perform object level backup and restores although it is not cheap.
If you perform a SELECT INTO you are unable to perform a point in time recovery.
If you just want to make a copy of the data in a table to be safe that is good.
I would not rely on SELECT INTO as a Backup and recovery strategy.
Here is the link to Lightspeed.
http://www.sqldbops.com/2010/01/on-ms-sql-how-to-restore-table-or.html
When performing a manual update, insert or delete in a production environment, where that update is an adhoc update (e.g. one time only, fix a data integrity issue - etc...) - I always take a backup of the table I am updating before I perform that update.
If the table in question is too large, I make sure I backup what I am going to be affecting.
The basic script is:
Begin Transaction; -- so I can roll it back when I make a mistake
Select * Into {backup table} From {original_table}; -- add where clause if necessary
Select {columns} From {original table} Where {criteria}; -- show a before picture
Update {original table}
Set {column = value}
Where {criteria};
Select {columns} From {original table} Where {criteria}; -- show after picture
Select * From {backup table}; -- show what we saved
Rollback Transaction; -- comment out this line when ready
-- Commit Transaction; -- uncomment this line to make changes permanent
Doing this has saved me countless times now - when I have had the wrong where clause, or the set was incorrect, or other issues.
Additionally, if I do commit the changes thinking everything is right - I still have the option of going to the backup table to see what it looked like before I made my changes. I have the option of joining to the table and getting the original values - for validation or to reset the table back to original, etc...
In my mind, this is just a CYA process...:-D
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2011 at 6:51 am
Every time I perform adhoc updates in production I create a copy of a table when I also wrap the changes in a transaction and rollback if necessary and commit the transaction after I have confirmed that the change is correct.
I also use a naming convention to include the date and the ticket number.
I also document every change and store it that I make, script etc to Cover myself and comply with SOX.
I also test in QA and execute the scripts and verify before I execute in the production environment.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply