Difference between Saving Table And BAckup

Question

Difference between Saving Table And BAckup

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 19, 2008 at 2:56 pm

#127070

hello,
when ever i get a change request to make soem changes in the table normally just to be on safe side i take a back up of the database and then make the changes..so that if the user says the he wants to roll back ..i would just restore it..but today i thought..if the changes are made on the table ...why dnt i just copy the existing script of the table and then store ...if the user wants the table back again..then i can just run the script again...even if the new script has a delete on the table i can still get back the data from the original script as delete will not remove the data from the disk..please correct me if my imagination or procedure is wrong...and suggest me a new or a better procedure if someone cld...thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

Take the backup.

Scripting only generates the DDL (database definition language) that creates the table, not the select/insert/update/deletes that happen.

backup early, backup often, and run a RESTORE VERIFYONLY just to see if the backup is good.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

thank you..yeah i did test on my laptop..saving script is not help full...thkx

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 3

Or you could use a SQL script for the structure and export the data with the BCP utility.

Or save your tabledata by running:

select * into {savedtablename} from {tablename}

Wilfred
The best things in life are the simple things

HanShi SSC-Dedicated Points: 33508 More actions · Answer 4

You can also copy the table to another database (or with another name within the database). That will save you time if you have to roll back. The copy of one table is much faster then the restore of a database.

Of course clean up the copy if you know a rollback is not required anymore 😀

Edit: I see Wilfred offers almost the same solution and includes the script in his post...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

thank you for al your replies...i dnt know if i am authorized to copy the existing table in a new db..dat might create some confusion..i know it will be much faster..but normally if the db is very large..i check if there are any full backups..then i take a differential backup before applying any changes to the table...but when there is achange in stored procedure..i just copy the script....i think copying the script in store procedure works right?...suggestions welcome..thx..

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Saving the table, as suggested above is the best idea. Backup & restore (to another DB name I assume) is overkill for this application.

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 7

The problem with the backup solution is that you can only restore the entire database, not a single table (assuming you're using the standard MS tools). If there are other changes in the database, you'll lose these as well.

brainy SSCrazy Points: 2445 More actions · Answer 8

If you have changes in only one table why are u taking backup of the whole database... just take the backup of that table and store it with some tmp_[tablename]... using select * into command....

So that in future if they want to roll back you can easily roll back using the backup table.

Later you can delete that table if not required.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

see i do not know if am allowed to create a table in some database...bcoz inorder to save a table i have to save it in some db..i figured out that using either bcp or import/export i can just copy the table in an excel file and later i can export it back if they need to rollback...but i was wondering once i export the table back from the excel file it will get all the permissions on the table right...thanks for all ur suggestions..

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 10

BCP in requires the table to already be there (I think - I haven't actually checked), so you'd still need to have scripted the table before you made the changes, and you would script the security stuff at this point as well.

Also, as an alternative, since you'll need to have enough permissions on the database to restore the old table, doesn't this mean that you'll enough permissions to create a backup of the old table there anyway?