该程序以不同的名称将数据库复制到同一服务器。我依赖于这个网站上给出的例子，并进行了一些改进。

-- Copies a database to the same server
-- Copying the database is based on backing up the original database and restoring with a different name

DECLARE @sourceDb nvarchar(50);    
DECLARE @destDb nvarchar(50);
DECLARE @backupTempDir nvarchar(200)

SET @sourceDb =  N'Northwind'         -- The name of the source database
SET @destDb =    N'Northwind_copy'    -- The name of the target database
SET @backupTempDir = N'c:\temp'       -- The name of the temporary directory in which the temporary backup file will be saved
-- --------- ---

DECLARE @sourceDb_ROWS nvarchar(50);  
DECLARE @sourceDb_LOG nvarchar(50);
DECLARE @backupPath nvarchar(400); 
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);
DECLARE @sqlServerDbFolder nvarchar(100);

Declare @Ret as int = -1
Declare @RetDescription nvarchar(200) = ''

-- Temporary backup file name
SET @backupPath = @backupTempDir+ '\TempDb_' + @sourceDb + '.bak'    

-- Finds the physical location of the files on the disk
set @sqlServerDbFolder = (SELECT top(1) physical_name as dir
                           FROM sys.master_files where DB_NAME(database_id) = @sourceDb  );

-- Clears the file name and leaves the directory name
set @sqlServerDbFolder = REVERSE(SUBSTRING(REVERSE(@sqlServerDbFolder), CHARINDEX('\', REVERSE(@sqlServerDbFolder)) + 1, LEN(@sqlServerDbFolder))) + '\'

-- Finds the logical name for the .mdf file
set @sourceDb_ROWS = (SELECT f.name LogicalName FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id
                      where d.name = @sourceDb  and   f.type_desc = 'ROWS' )

-- Finds the logical name for the .ldf file
set @sourceDb_LOG = (SELECT f.name LogicalName FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id
                      where d.name = @sourceDb  and   f.type_desc = 'LOG' )

-- Composes the names of the physical files for the new database  
SET @destMdf = @sqlServerDbFolder + @destDb + N'.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + N'_log' + N'.ldf'

-- If the source name is the same as the target name does not perform the operation
if @sourceDb <> @destDb      
    begin 

    -- Checks if the target database already exists
    IF Not EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @destDb)
    begin 
        -- Checks if the source database exists
        IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @sourceDb) and (@sqlServerDbFolder is not null)
        begin 

            -- Opens the permission to run xp_cmdshell
            EXEC master.dbo.sp_configure 'show advanced options', 1
            RECONFIGURE WITH OVERRIDE
            EXEC master.dbo.sp_configure 'xp_cmdshell', 1
            RECONFIGURE WITH OVERRIDE
         
            -- If the temporary backup directory does not exist it creates it
            declare @md as nvarchar(100) = N'if not exist ' + @backupTempDir + N' md ' +@backupTempDir  
            exec xp_cmdshell  @md,  no_output

            -- Creates a backup to the source database to the temporary file
            BACKUP DATABASE @sourceDb TO DISK = @backupPath 

            -- Restores the database with a new name
            RESTORE DATABASE @destDb FROM DISK = @backupPath
            WITH REPLACE, 
            MOVE @sourceDb_ROWS TO @destMdf, 
            MOVE @sourceDb_LOG TO  @destLdf

            -- Deletes the temporary backup file
            declare @del as varchar(100) = 'if exist ' + @backupPath +' del ' +@backupPath 
            exec xp_cmdshell  @del ,  no_output

            -- Close the permission to run xp_cmdshell
            EXEC master.dbo.sp_configure 'xp_cmdshell', 0
            RECONFIGURE WITH OVERRIDE
            EXEC master.dbo.sp_configure 'show advanced options', 0
            RECONFIGURE WITH OVERRIDE
         
            set @ret = 1
            set @RetDescription = 'The ' +@sourceDb + ' database was successfully copied to ' + @destDb 
        
        end
        else
        begin
          set @RetDescription = 'The source database '''+ @sourceDb + ''' is not exists.'
          set @ret = -3
        end

    end
    else
    begin
      set @RetDescription = 'The target database '''+ @destDb + ''' already exists.'
      set @ret = -4
    end
end
else
begin
  set @RetDescription = 'The target database ''' +@destDb + ''' and the source database '''+ @sourceDb + ''' have the same name.'
  set @ret = -5
end

select @ret as Ret, @RetDescription as RetDescription

这是我使用的脚本。有点棘手，但很有效。在SQL Server 2012上测试。

DECLARE @backupPath nvarchar(400);
DECLARE @sourceDb nvarchar(50);
DECLARE @sourceDb_log nvarchar(50);
DECLARE @destDb nvarchar(50);
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);
DECLARE @sqlServerDbFolder nvarchar(100);

SET @sourceDb = 'db1'
SET @sourceDb_log = @sourceDb + '_log'
SET @backupPath = 'E:\DB SQL\MSSQL11.MSSQLSERVER\MSSQL\Backup\' + @sourceDb + '.bak'    --ATTENTION: file must already exist and SQL Server must have access to it
SET @sqlServerDbFolder = 'E:\DB SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
SET @destDb = 'db2'
SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf'

BACKUP DATABASE @sourceDb TO DISK = @backupPath

RESTORE DATABASE @destDb FROM DISK = @backupPath
WITH REPLACE,
   MOVE @sourceDb     TO @destMdf,
   MOVE @sourceDb_log TO @destLdf

解决方案，基于这条评论:https://stackoverflow.com/a/22409447/2399045。 只需设置:DB名称，临时文件夹，DB文件文件夹。 运行后，您将拥有名称为“sourceDBName_yyyy-mm-dd”格式的DB副本。

-- Settings --
-- New DB name will have name = sourceDB_yyyy-mm-dd
declare @sourceDbName nvarchar(50) = 'MyDbName';
declare @tmpFolder nvarchar(50) = 'C:\Temp\'
declare @sqlServerDbFolder nvarchar(100) = 'C:\Databases\'

--  Execution --
declare @sourceDbFile nvarchar(50);
declare @sourceDbFileLog nvarchar(50);
declare @destinationDbName nvarchar(50) = @sourceDbName + '_' + (select convert(varchar(10),getdate(), 121))
declare @backupPath nvarchar(400) = @tmpFolder + @destinationDbName + '.bak'
declare @destMdf nvarchar(100) = @sqlServerDbFolder + @destinationDbName + '.mdf'
declare @destLdf nvarchar(100) = @sqlServerDbFolder + @destinationDbName + '_log' + '.ldf'

SET @sourceDbFile = (SELECT top 1 files.name 
                    FROM sys.databases dbs 
                    INNER JOIN sys.master_files files 
                        ON dbs.database_id = files.database_id 
                    WHERE dbs.name = @sourceDbName
                        AND files.[type] = 0)

SET @sourceDbFileLog = (SELECT top 1 files.name 
                    FROM sys.databases dbs 
                    INNER JOIN sys.master_files files 
                        ON dbs.database_id = files.database_id 
                    WHERE dbs.name = @sourceDbName
                        AND files.[type] = 1)

BACKUP DATABASE @sourceDbName TO DISK = @backupPath

RESTORE DATABASE @destinationDbName FROM DISK = @backupPath
WITH REPLACE,
   MOVE @sourceDbFile     TO @destMdf,
   MOVE @sourceDbFileLog  TO @destLdf

来自SSMS:

1 -备份原始数据库到。bak文件(your_source_db -> Task -> Backup)。

2 -右键点击“数据库”和“恢复数据库”

3 -设备>…(按钮)>添加>选择your_source_db.bak

4 -在“General”选项卡，在“Destination”部分，将“Database”your_source_db重命名为new_name_db

5 -在“文件”选项卡，勾选“重新定位所有文件到文件夹”，

在“还原为”列中重命名两个lignes，以保持与new_name_db(。mdf _log.ldf)

6 -在“选项”选项卡，“恢复选项”部分，勾选两个拳头选项(“覆盖…”'， '保存…')，对于'恢复状态':'恢复与恢复'

也要确保在“尾日志备份”部分的选项是不勾选的，以避免保持源db在“恢复状态”!

如果你是MS SQL 2014及更新版本;

DBCC CLONEDATABASE (CurrentDBName, NewDBName)
GO

细节;

如果数据库不是很大，你可以看看SQL Server Management Studio Express中的“Script database”命令，它位于资源管理器中数据库项本身的上下文菜单中。

你可以选择什么都脚本;当然，您需要对象和数据。然后将整个脚本保存到一个文件中。然后，您可以使用该文件重新创建数据库;只要确保顶部的USE命令被设置为正确的数据库。