Export a SQL Server database from C# and re-import

Question

I'm looking for a way to create a backup of a SQL Server 2008 database in C# code. I then wish to re-import this database to a local database server, again using C# code.

I know this can be done via the SQL Server Management Studio very easily, but need an automated way to achieve this.

Answer 1

Quickest way would be to connect to the server and execute the SQL Commands to backup and restore SQL Server Databases directly. Google the following to get the syntax; For Backup Syntax; tsql backup database msdn For Restore Syntax; tsql restore database msdn

There are alternatives to the above though;

1. Use SMO (SQL Management Objects), a .Net library used to help manage SQL from .Net Applications. Here's a link with many links to some reference material that will be helpful; http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/5638666e-cd2e-467d-bd03-6d20e2cbbe1b/

2. Use SQL itself. Did you know SQL Maintenance Plans can perform Backups and Restores on more than the local server and can be scheduled to run at any time. You 'may' need to implement a job to copy the backup files over to the alternate server as well. At worst a combination of plans on the two servers can be used.

My preference would be to try the maintenance plans first Consider the fact that a DBA will see it know about it and be able to change it to adapt to the business environment. Also you can hang off error messaging that is emailed/logged when it fails and it's all built in functionality.

If you really want to or have to write an application, consider the SQL command option as SMO is limited to specific versions of SQL Server. Older versions use DMO. No idea what the next version will be =)

Answer 2

What you want to do is have a couple of scripts that you run against the master database from C# using the regular connection objects (SqlConnection, etc).

There are commands to backup and commands to restore to and from a file. All you then need to do is manage the file in your application.

To restore:

http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/

To backup:

http://msdn.microsoft.com/en-us/library/ms191304.aspx

There is also a set of DLLs from Microsoft to work against SQL Server databases that are not part of the ADO.NET provider set. Here is a tutorial covering these:

http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx