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 2008 Forums

SQL Server Administration (2008)

restore

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-09 : 10:29:37

Hi,
I would like to change the datatype of one table which has millions of records. The fields is to be changed from varchar(50) to char(4)
I have the script for this...
If something goes wrong during running this script then how can I get back to how the table was originally?
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-09 : 10:41:03

1. Make a full database backup before running the script.
2. If it's an ALTER TABLE statement, if something goes wrong it will fail and automatically roll back.
3. If somehow things go totally kablooey, restore from the backup you made.

You'll obviously want to test this on a different server ahead of time, using the backup you made in step 1.

An alternate method is to create a new table with the proper structure, copy the data into it, then drop the original table and rename the new table to the old name. That way you can stop at any time if something goes wrong.

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 14:53:22

I would suggest (for minimal user-interference):


ADD NewColumn with CHAR(4) datatype
SET NewColumn to LEFT(OriginalColumn, 4) [teal]-- i.e. OUTSIDE a transaction

BEGIN TRANSACTION
UDPATE MyTable
SET NewColumn = LEFT(OriginalColumn, 4)
WHERE     NewColumn <> LEFT(OriginalColumn, 4) COLLATE SomeBinaryCollation
      OR (NewColumn IS NULL AND OriginalColumn IS NOT NULL)
      OR (NewColumn IS NOT NULL AND OriginalColumn IS NULL)
EXEC sp_rename OriginalColumn, DeleteColumn, COLUMN
EXEC sp_rename NewColumn , OriginalColumn, COLUMN
COMMIT

DROP COLUMN DeleteColumn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources