Way to sync production and testing databases

I need to sync the data of a ms-sql 2005 & 2008 server to my development server (mssql 2005 & 2008).

The problem is I can't create .bak files, no permissions (to access the network share where the backup is saved). The problem is not that I may not view the data (I have complete read access to the production server). I can also backup data to csv fil开发者_运维百科es and read them in on my system, but that's rather painful (encoding, foreign key reference constraints, having to do it for every table, etc...). The problem is that the admin has no time to set me the necessary permissions (he has larger problems...).

How can I still sync the data to my development server ? I need some kind of syncing to a file, then transport anyhow (manual by FTP if necessary), and then update my development database. I've already tried backing up to a back file on a network share, but that doesn't work either.

I don't mind if I have to code a bit in my free time, but I really don't want to do all the low level stuff myself, like resolving constraint dependencies.

While I am at it, it would be great if I could merge data from several production servers on my development machine.

Any ideas or pointers ?

Check out sql data compare:

http://www.red-gate.com/products/SQL_Data_Compare/

This is what we use when we have to sync databases.

You could try the project http://sqlinserter.codeplex.com/.

It creates scipts in the form insert into table1 (col1,col2) values (1,2)

Redgate sql compare does the job, but costs a bunch of bucks http://www.red-gate.com/products/SQL_Compare/

You might want to take a look at the RedGate SQL Data Compare tool.

What are you testing? Unless you're doing performance or integration tests, production data won't be very useful since it contains a whole lot of generic cases (which repeat millions of times) and few corner cases.

Therefore, I rarely sync those two. What I do is: I have a development database (a set of SQL files) which the first test sets up. This contains artificial test data plus data which caused bugs in production. To make this useful, the test database is as small as possible.

Then we have an integration test system which is simply a mirror of the production database. This is used for performance tests and some big, automatic tests but not for unit testing.

Your administrator doesn't have regular backups scheduled for the production database?

READ permission is not sufficient to backup a database. You won't be able to get indexes, relationships, or stored procedures without the BACKUP DATABASE permission.

You don't need these permissions if the administrator can simply grant you access to the backup files. If there are not backup files of your production database, he could have MUCH MUCH bigger problems in the future.