0

My problem: I got a virtual Windows XP machine with a specific database driven software installed using SQL Server 2005. A similar machine is also in use by several customers. One customer reports an SQL Server error, which I cannot reproduce because I got only data for testing.

What are the steps I can tell the customer to backup only the tables (not stored procedures etc.) of a specific database? How do I restore the data to my development machine, overwriting existant test data?

Thanks a lot!

elsni
  • 425
  • 1
  • 4
  • 8

3 Answers3

1

What you'll want to do is have the customer take a "Copy Only" full backup of the database to send to you. This way, the backup will not mess with their current backup process/library. The command to do so is:

BACKUP DATABASE [<DB Name>] TO  DISK = N'C:\Backup_File.bak' WITH  COPY_ONLY

This will backup the entire database. Have the customer ship the backup file to you (I suggest zipping them first as well, since they are generally highly compressible).

Once you've received the backup file, you'll need to do a restore. There's no direct way to only restore the tables. I'd say the best way would be to restore the customer's database to a new database, then use SSIS to copy the table data back to your test database.

Evan M.
  • 842
  • 9
  • 16
0

I would have the customer just do a full backup and then restore it to your server. If you really want just the data you could have the customer right click on the DB go to Tasks - Generate Scripts and go through the wizard. Keep in mind I dont necessarily believe that will give you an identical copy of the DB. You could also use Detach - Copy the DB files to you - and then Re-Attach. IMO best way is the backup and restore. If you're concerned with "contaminating" your server then I would use a test VM and snapshot/rollback.

Tim
  • 226
  • 1
  • 3
0

SQL Server does not provide a way to backup only the data from a database. You'll need to have them create a full backup to send to you. Once you have the full backup, you can restore just the objects (i.e., tables) you want.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12