Is possible to clone SQL database from ASP.Net application efficiently?

Question

There are a lot of database cloning tools out there such as SQL Data Compare.

However, end users (using ASP.Net Application) want to clone the staging SQL Server 2008 database to production SQL Azure database himself when contents is ready.

I'm sure that I can compare each and every table using Entity Framework, and insert/update/delete each row. Is there any better method?

Thank you for your help!

Answer 1

You could use the different classes in the Microsoft.SqlServer.Management namespace to manage all aspects of SQL Server.

This includes doing a backup from one server and restore into another (including changing logfile names and whatever else is needed).

In essence, if you can do it in SSMS, you can use these classes to do the same.

I have used this in the past do do something close to what you are describing.

Answer 2

Why not backup the database and restore it?

You can run a backup and restore from your web application without much difficulty. You should probably write the code to handle the backup in a stored procedure instead of trying to write the logic in your application code. Something like:

CREATE PROCEDURE dbo.InitiateClone
  @DBName     SYSNAME
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'BACKUP DATABASE ' + QUOTENAME(@DBName)
     + ' TO DISK = ''\\Common_network_path\wherever\' + @DBName + '.BAK''
     + WITH INIT;';

  EXEC sp_executesql @sql;

  SET @sql = N'SELECT name, type_desc FROM sys.
END
GO

Now the app that asks for the backup can consume the data and log file name to pass to the procedure on the other server:

CREATE PROCEDURE dbo.FinishClone
  @NewDBName    SYSNAME,
  @OldDBName    SYSNAME,
  @DataFileName VARCHAR(255),
  @LogFileName  VARCHAR(255)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'RESTORE DATABASE ' + QUOTENAME(@NewDBName)
     + ' FROM DISK = ''\\Common_network_path\wherever\' + @OldDBName + '.BAK''
     + ' WITH MOVE ''' + @DataFileName + ''' TO ''D:\datapath\' 
     + @NewDBName + '_data.mdf'','
     + ' MOVE ''' + @LogFileName + ''' TO ''D:\logpath\'
     + @NewDBName + '_log.ldf'';';

  EXEC sp_executesql @sql;
END
GO

The only thing you need to worry about is if two users try to clone the same source database at the same time, so you may want to put some kind of queuing or semaphore control in there. I also omitted error handling for brevity (e.g. making sure the new name doesn't already exist). It also assumes you have simple databases (one data file and one log file). But it's a start.

EDIT since we know the destination is Azure:

With Azure I think your options are limited. I don't think you can perform a restore this way. How are you going to initiate the creation of the new database on Azure? Once that's done then you can still consider some of the third party tools for comparing and synchronizing. Red Gate's tools, for example, have command-line interfaces, which means you can certainly invoke them in response to requests from your web application.

Answer 3

Backing a database is a lot more difficult than reading tables and records. The sequence of the restore is critical, and so is the performance aspect of the restore operation to avoid throttling as much as possible.

You might be able to script a DACPAC operation; I am not sure how to do that, but it may be possible. The DACPAC is the official Microsoft solution for backing up and restoring databases. Here is a link to the DACPAC overview; you can probably find a way to use this programmatically: http://msdn.microsoft.com/en-us/library/dd193245.aspx

Using third party tools, the Enzo Backup tool will soon provide an API to backup/restore a database programmatically. The API will allow developers to start/stop/send alerts upon completion of database backup and restore operations against a SQL Azure database (limited support for SQL Server). The API is available upon private request at this point; please contact [email protected] for information about the API if you decide to give it a try.

[DISCLAIMER: I am the author of the backup tool]