A utility to transfer database - DbDataTransfer
I came across with this thread in the ASP.NET Forum today and notice that Vicenc created a tool for transferring database to a remote location, and it looks promising!
DbDataTransfer is a utility to transfer a database from server to server. It can be used as a replacement for the Export/Import feature on Sql Server Enterprise Manager (well...not ready now...)
If you are using a provider who doesn't allow you to do backups (for example webmatrixhosting) you can use it to download you database to a local server and then automate backups on local server.
Use
Just start the program, inform source and destination connection strings to connect to both databases and hit Go!.
Both databases need to be identic, so you first need a correct database on the destination server. By now it doesn't CREATE/DROP anything, it expects all objects were created previouslly.
There is a configuration file with two entries (one for each database) where you can configure source and destination Connection Strings, so you don't have to write it every time (this will be used too on the future batch mode).What it does
Reads one table, transfers to destination and moves to next table. As you can see there is no magic in it.
At every execution it disables all triggers and all constraints for all tables in destination database before inserting rows. Then deletes all data on destination tables and transfers source rows. If the table has an identity column it preserves the column values.
After the work is done, triggers and constraints are reenabled.Future work
- Save log
- Transfer only selected tables
- Work in batch mode. Start from command line with parameters and do all the work without user intervention (usefull to schedule)
- Improve speed
- Recreate objects instead of deleting data (in this way there is no need to have identical database structures)
- ...
Yes, that's really cool and I love that ASP.NET community!