Back up the data in tables

[jkhilton]

Hello,

A brief history of the problem...

I work for a computer games company that uses databases to build their games, What we are needing to do is to be able,at the click of a button, to backup data from certain tables in SQL Server 2000 to a specified directory, so that the programmers can back up the data in a specific table and then try out a new setting etc. and if it doesn't work how they want, put the original data back into the table again.

Is there any way to do this via a VB Script or a TSQL script?
I am not a SQL person really, so appologies if this is a little vague.

thanks in advance


John Hilton

 

[MAK9974]

1. BCP or
2. DTS

 

[TerryStover]

Look at books online under BCP utility:
Copying Data From a Query to a Data File

You can run it from a stored procedure using the xp_cmdshell command. There's some effort in getting xp_cmdshell to work on a server and it's a security hole.

If you set up a dts package you can use the dtsrun utility to execute.

I've always used the xp_cmdshell / bcp route because it makes it easier to dynamically assign file names (so you can keep multiple generations of backup files). After running the bcp export you can zip the files with gzip into an appropriately named archive file.

 

[jkhilton]

Thanks for the help, I will look into that today.


John