In this article, we will learn about how to create a new database with the help of an existing database in same Microsoft SQL Server through the help of Procedure in Local Database.

In this article, I explain the process to create the procedure by two methods.

    HardCode
    Dynamically

The below code is of Stored Procedure for creating New Copy Database with New Name in the same server,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_post_new_database]
     (
   @DatabaseName varchar(100) = 'CopyDatabase',  --Name of New Database which we going to create
   @CopyDatabase varchar(100) = 'OriginalDatabase'    --Name of Database
)
AS
BEGIN
DECLARE  @BackupDataFileNameAndPath VARCHAR(MAX)=''
DECLARE  @OpenDataFileNameAndPath VARCHAR(MAX)=''
DECLARE  @PathBackUpDatabase VARCHAR(MAX) = 'D:\db\'  -- We also make it dynamic ,for dynamic we need pass parameter to Our Store Procedure
DECLARE  @OpenPathDatabase VARCHAR(MAX) = 'D:/db/' -- We also make it dynamic ,for dynamic we need pass parameter to Our Store Procedure
DECLARE  @fileExtenion_mdf VARCHAR(MAX)=''
DECLARE  @fileExtenion_ldf VARCHAR(MAX)=''
DECLARE  @Open_ldf VARCHAR(MAX)=''
SET @BackupDataFileNameAndPath = @PathBackUpDatabase+@DatabaseName+'.bak'  --for dynamic  | for HardCode --> 'D:\db\CopyDatabase.bak'
--Open BackUpdataBase
SET @OpenDataFileNameAndPath = @OpenPathDatabase+@DatabaseName+'.bak'   --for dynamic
SET @fileExtenion_mdf=@PathBackUpDatabase+@DatabaseName+'_Data.mdf' --for dynamic   | for HardCode -->  'D:\db\CopyDatabase_Data.mdf'
SET @fileExtenion_ldf=@PathBackUpDatabase+@DatabaseName+'_Log.ldf'  --for dynamic   | for HardCode -->  'D:\db\CopyDatabase_Data_Log.ldf'
SET @Open_ldf=@CopyDatabase+'_Log'  --for dynamic
--BackUp Our database which name of Our New Created Database
BACKUP DATABASE @CopyDatabase  -- <- for dynamic | for HardCode -->  BACKUP DATABASE 'OriginalDatabase'
   TO DISK =@BackupDataFileNameAndPath    -- <- for dynamic | for HardCode -->  TO DISK = 'D:\db\CopyDatabase.bak'
   WITH FORMAT,
      MEDIANAME = 'MyServerDatabaseBackups',
      NAME = 'Full Backup of My  OriginalDatabase';
  --Used for View the Name Of .mdf and .ldf which are in CopyDatabase.bak
   RESTORE FILELISTONLY
   FROM disk =@OpenDataFileNameAndPath;    -- <- for dynamic | for HardCode -->  FROM disk ='D:/db/CopyDatabase.bak';
-- Restore the files for BackupDataBase.
RESTORE DATABASE @DatabaseName
   FROM disk=@OpenDataFileNameAndPath   -- <- for dynamic | for HardCode -->  FROM disk='D:/db/CopyDatabase.bak'
   WITH RECOVERY,
   -- Below two line Move .mdf and .ldf file from  CopyDatabase.bak and restore in Your Server with
   --Our Given new Database name and Your Desire location in You local Disk
   MOVE @CopyDatabase TO @fileExtenion_mdf,  -- <- for dynamic | for HardCode -->    MOVE 'OriginalDatabase' TO 'D:\db\CopyDatabase_Data.mdf' ,
   MOVE @Open_ldf TO @fileExtenion_ldf;    -- <- for dynamic | for HardCode -->     MOVE 'OriginalDatabase_Log' TO 'D:\db\CopyDatabase_Data_Log.ldf' ;
   --For Delete .bak File From Disk
   --Create BackUp Device "sp_addumpdevice" Procedure set Path of Our CopyDatabase.bak file for delete
   EXEC sp_addumpdevice 'disk', @CopyDatabase, @BackupDataFileNameAndPath ;   -- <- for dynamic | for HardCode -->  EXEC sp_addumpdevice 'disk', 'OriginalDatabase','D:\db\CopyDatabase.bak' ;
   --"sp_dropdevice" Procedure  delete CopyDatabase.bak file from our Local Disk because we already restore new created database in Server
   EXEC sp_dropdevice @CopyDatabase, 'delfile' ;    -- <- for dynamic | for HardCode -->  EXEC sp_dropdevice 'OriginalDatabase', 'delfile' ;
END
GO

HostForLIFEASP.NET SQL Server 2019 Hosting