Introduction
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)=''
--for Change Logical Name
DECLARE @ChangeLogicalNameOfNewCreateDataBase 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 change logical name of new create database
SET @ChangeLogicalNameOfNewCreateDataBase='ALTER DATABASE'+' '+ @DatabaseName+' '+'MODIFY FILE ( NAME ='+@CopyDatabase+', '+'NEWNAME = '+@DatabaseName+')';
EXEC(@ChangeLogicalNameOfNewCreateDataBase);
SET @ChangeLogicalNameOfNewCreateDataBase='ALTER DATABASE'+' '+ @DatabaseName+' '+'MODIFY FILE ( NAME ='+@CopyDatabase+'_log, '+'NEWNAME = '+@DatabaseName+'_log)';
EXEC(@ChangeLogicalNameOfNewCreateDataBase);
--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