Export SQL Server database from C # and re-import

Question

I am looking for a way to back up a SQL Server 2008 database in C # code. Then I want to re-import this database to the local database server using C # code again.

I know that this can be done with SQL Server Management Studio very easily, but this requires an automated way.

Answer 1

What you want to do is to have several scripts that you run against the main database with C # using regular connection objects ( SqlConnection , etc.).

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

Recovery:

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

For backup:

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

There is also a Microsoft set of DLLs for working with SQL Server databases that are not part of the ADO.NET provider set. Here is a tutorial on these:

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

Answer 2

The fastest way is to connect to the server and execute SQL commands to back up and restore SQL Server databases directly. Google to get the syntax; For fallback syntax; Tsql msdn backup database restore syntax; Tsql msdn recovery database

There are alternatives to the foregoing.

• Use SMO (SQL Management Objects), the .Net library used to manage SQL from a .Net Application. There will be a link with many links to some reference materials that will be useful; http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/5638666e-cd2e-467d-bd03-6d20e2cbbe1b/

• Use SQL itself. Did you know that SQL service plans can back up and restore to more than a local server and can be scheduled to run at any time. You may need to complete the task to copy the backup files to an alternate server. In the worst case scenario, you can use a combination of plans on two servers.

My preference is to try service plans first. Consider the fact that the database administrator will see that he knows about it and will be able to change it to adapt to the business environment. You can also turn off error messaging, which is sent by e-mail or logged when it fails, and all of them are built into the functionality.

If you really want or need to write an application, consider the SQL command option, because SMO is limited to specific versions of SQL Server. Older versions use DMO. I don’t know what the next version will be =)

Answer 3

A simpler solution would be to write an SSIS package that simplifies the job. (as a service plan) Then, through the .NET application (web or winform), you can control the call of the package using the Microsoft.SQlServer.ManagedDTS link, or use the console application and schedule it to run every x time to automate your process. If you do not want to use C #, just pay for your service plan