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!

What a strange requirement. A user in a web application wants to clone a whole database? — Tim Schmelter, May 03 '12 at 15:15
If you have to insert/update/delete rows between databases, you certainly aren't *cloning* a database. If that was the case, you could simply backup the staging database and restore it to production. What's your goal? A SQL Server Maintenance Plan, SSIS package, or stored procedure may be a more appropriate location for this type of logic. Your web front-end could kick one of those things off. — Cᴏʀʏ, May 03 '12 at 15:15
@TimSchmelter I thought so too but I'm going on the assumption that it's poor English and by end user he means a client. But I'm sure you know that saying about assumptions... — Pete, May 03 '12 at 15:16
Yes, end user (client using asp.net app) want to push the entire data to production by himself without database administrator. — Win, May 03 '12 at 15:21
Actually, I would like the exact copy of database. But I do not know how to do it from .net code efficiently. Which is why I said insert/update/delete. :) — Win, May 03 '12 at 15:29

Aaron Bertrand · Answer 1 · 2012-05-03T15:45:34.453

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.

This is not answering the question... probably another question — huMpty duMpty, May 03 '12 at 15:17
I can clone db in few minutes. However, our end user want to do it himself from web application. :| — Win, May 03 '12 at 15:17
I did not down vote it instead I upvoted back. Staging SQL 2008 is on premises, but production is SQL Azure. It is my false that I did not mention it at the beginning. — Win, May 03 '12 at 15:43
@AaronBertrand: Because you worded it in question format, it seemed like just an attempt to elicit more information from the OP, which should certainly be done in the comments. Please don't act like these are **my** rules: you've been on [so] long enough to know its own [guidelines](http://stackoverflow.com/privileges/comment). — Cᴏʀʏ, May 03 '12 at 15:45
@AaronBertrand: I did not vote you down. For the record, your answer is good for reference, I'm not arguing that. — Cᴏʀʏ, May 03 '12 at 15:51

score 1 · Accepted Answer · answered May 03 '12 at 15:17

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.

answered May 03 '12 at 15:17

Oded

489,969
99
883
1,009

Thanks! I'll do some research abut Microsoft.SqlServer.Management. Do you come across any blogs or websites? – Win May 03 '12 at 15:32

score -1 · Answer 3 · answered May 04 '12 at 00:16

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 info@bluesyntax.net for information about the API if you decide to give it a try.

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

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

3 Answers3