Table Back Up

ac_davis2002
ac_davis2002 used Ask the Experts™
on
Hi

I need to update a number of rows in a table.......before i do this is there a way i can back the data up at table level rather that taking a complete database backup...

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Right click on the Database, select Tasks the Export and export just that table.
Commented:
Simplest thing is to export data to a csv file.

Or as i would do it, use Transaction

Example
BEGIN TRANSACTION
    Update MyTable
    Set x = '1'

    Select *
    From MyTable
ROLLBACK TRANSACTION

 

Open in new window



Start transaction, update the table, select the data to check that its correct, then rollback to its original state.

Once you established that the data is correct, remove the begin and rollback transaction and the select

/Martin
Commented:
I don't think I've seen the option to backup a single table since SQL 6.5. Roughly 10 years ago. ;)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You can also make a copy of the table like so:

SELECT *
INTO dbo.table_name_backup
FROM dbo.table_name


Then drop the _backup table when you no longer need it.

Author

Commented:
Thanks for all your help...love the sql 6.5 comment!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial