backing up a table 1

[aspiringprog]

Hi

I have to test some delete statements on a table in test. Instead of having to restore the test server each time I run the delete statements, I am trying to back up the table before the DML statements and restore after the queries are run.

I know there is a backup command in sql server

i am using

backup oldtable to newtable

The message i get is that back up table is not yet implemented.

Am i supposed to create the new backup table first, before tring out this command?

Is there any other way?

Thanks

 

[SQLDenis]

SELECT * INTO NewTableName FROM OldTable

this will only create the skeleton table stutcture and all the data (no constraints, PK's FK's, triggers etc etc etc)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

I don't know of a way to use the backup command to just back up a table. You could simply create new tables to hold the original data, then restore the data to the original tables from the backup tables.

Jim

 

[aspiringprog]

I was thinking of creating a new temp table ..but there are close to 200 columns in the original table. It just seems like a lot of work to create such a huge table, just for testing purposes. That is why I was wondering if there was a quicker and less time consuming way,,

 

[jbenson001]

Well as Denis has pointed out.. the select into is the easiest way to go.