How to create a new SQL Server database from ASP.NET without using script

We have a production database that we apply changes to its structure frequently.

In order to create a new database from our ASP.NET app, we have to first create a new script from one of the current databases and then run it from our ASP.NET project to create a new database.

As this process is manual, it’s time consuming and is risky because of mistakes that happens in removing extra tags and adding other parameters.

I wonder is there any other way that allow us to clone a new database from existing database using ASP.NET?

Any advice is appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

dotnet ef does not have db clone commands.

Rather than creating script, you can clone/copy database using SSMS. Then update connection string in asp.net application, test and deply.
Here is the documentation of MS SQL server.
https://docs.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-ver15

Any database have the copy/clone feature.

And if you use EF Code First, Please check ef migrations for existing database. https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/existing-database

If you need to copy db only using application, you can create stored procedure and execute from application. here is the details.

1. The source database is backed up to a file using dynamic T-SQL execution
2. The backup is then restored to a new database, using the RESTORE DATABASE WITH MOVE OPTION again using dynamic T-SQL

The procedure takes 6 parameters (all parameters should be supplied):

1. Source database name
2. Source database logical data file name
3. Source database logical log file name
4. Backup file name and path
5. Destination database name
6. Destination database folder location for database files

Here is the T-SQL procedure source code:

USE master;
GO 
CREATE PROCEDURE dbo.CloneDBUsingBackupRestore ( 
@SourceDatabaseName VARCHAR(200) 
,@SourceDatafileLogicalName VARCHAR(200) 
,@SourceLogfileLogicalName VARCHAR(200) 
,@BackupFile VARCHAR(2000) 
,@TargetDatabaseName VARCHAR(200) 
,@TargetDatabaseFolder VARCHAR(2000) ) 
AS 
BEGIN 
SET NOCOUNT ON
DECLARE @tsql VARCHAR(2000) 
DECLARE @TargetDataFile VARCHAR(2000) 
DECLARE @TargetLogFile VARCHAR(2000) 
SET @TargetDataFile = @TargetDatabaseFolder + @TargetDatabaseName + '.mdf'; 
SET @TargetLogFile = @TargetDatabaseFolder + @TargetDatabaseName + '.ldf'; 
-- Backup the Source Database
 IF @SourceDatabaseName IS NOT NULL 
BEGIN 
  SET @tsql = CONCAT ( 'BACKUP DATABASE ' ,@SourceDatabaseName 
,' TO DISK = ' ,QUOTENAME(@BackupFile, '''') ) 
PRINT 'Executing query : ' + @tsql; 
EXEC (@tsql) 
END

-- Restore database from the Backup File into target Data File and target Log File 
SET @tsql = CONCAT ( 'RESTORE DATABASE ' ,@TargetDatabaseName 
,' FROM DISK = ' 
,QUOTENAME(@BackupFile, '''') 
,' WITH MOVE ' 
,QUOTENAME(@SourceDatafileLogicalName, '''') ,
' TO ' ,QUOTENAME(@TargetDataFile, '''') ,' 
, MOVE ' ,QUOTENAME(@SourceLogfileLogicalName, '''') 
,' TO ' ,QUOTENAME(@TargetLogFile, '''') ) 
PRINT 'Executing query : ' + @tsql EXEC (@tsql) 
SET NOCOUNT OFF 
END GO

Call Using ASP.Net Code.

using (SqlConnection con = new SqlConnection(connString))
{
   using (SqlCommand cmd = new SqlCommand("dbo.CloneDBUsingBackupRestore", con))
   {
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add("@SourceDatabaseName", SqlDbType.VarChar).Value 
          ="Northwind";
       cmd.Parameters.Add("@SourceDatafileLogicalName ", SqlDbType.VarChar).Value 
          ="Northwind_Log";
       cmd.Parameters.Add("@BackupFile", SqlDbType.VarChar).Value 
          ="C:SQLnw.bak";
       cmd.Parameters.Add("@TargetDatabaseName", SqlDbType.VarChar).Value 
          ="NorthwindClone";
       cmd.Parameters.Add("@TargetDatabaseFolder ", SqlDbType.VarChar).Value 
          ="C:SQL";
      con.Open();

      cmd.ExecuteNonQuery();  
      con.Close();  
    }
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0