Script to copy entire database in MS SQL Server

sql-script-copy-entire-database-in-ms-sql-server.sql

-- Script to copy entire database in MS SQL Server
-- (Source: https://stackoverflow.com/a/79691/826308)

-- VARIABLES
DECLARE @dbSrcName as nvarchar(max) = 'Northwind';
DECLARE @dbCopyName as nvarchar(max) = 'Northwind_20211026';
DECLARE @dbCopyFilesPath as nvarchar(max) = 'E:\\Backups\\';
-- (change if needed)
DECLARE @dbSrcLogicalName as nvarchar(max) = @dbSrcName;
DECLARE @dbSrcLogicalLogName as nvarchar(max) = @dbSrcName + '_log';
DECLARE @dbCopyBakPath as nvarchar(max) = @dbCopyFilesPath + @dbCopyName + '.bak';
DECLARE @dbCopyMDFPath as nvarchar(max) = @dbCopyFilesPath + @dbCopyName + '.mdf';
DECLARE @dbCopyLDFPath as nvarchar(max) = @dbCopyFilesPath + @dbCopyName + '.ldf';

-- SOURCE SERVER DB TO BAK
BACKUP DATABASE @dbSrcName
  TO DISK = @dbCopyBakPath

-- TARGET SERVER 
RESTORE FILELISTONLY
  FROM DISK = @dbCopyBakPath

-- RESTORE DEVICE NAMES, MDF/LDF
RESTORE DATABASE @dbCopyName
  FROM DISK = @dbCopyBakPath
  WITH MOVE @dbSrcLogicalName TO @dbCopyMDFPath,
  MOVE @dbSrcLogicalLogName TO @dbCopyLDFPath
GO