如何在SQL Server 2008 Express的同一台服务器上克隆SQL Server数据库?

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

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

您可以尝试分离数据库，在命令提示符下将文件复制到新的名称，然后附加两个db。

在SQL:

USE master;
GO
EXEC sp_detach_db
@dbname = N'OriginalDB';
GO

在命令提示符下(为了这个例子，我简化了文件路径):

copy c:\OriginalDB.mdf c:\NewDB.mdf
copy c:\OriginalDB.ldf c:\NewDB.ldf

还是用SQL:

USE master;
GO
CREATE DATABASE OriginalDB
ON (FILENAME = 'C:\OriginalDB.mdf'),
(FILENAME = 'C:\OriginalDB.ldf')
FOR ATTACH;
GO
CREATE DATABASE NewDB
ON (FILENAME = 'C:\NewDB.mdf'),
(FILENAME = 'C:\NewDB.ldf')
FOR ATTACH;
GO

右键单击要克隆的数据库，单击Tasks，单击Copy Database...。遵循向导，您就完成了。

原来我试图从备份中错误地进行恢复。

最初我创建了一个新的数据库，然后尝试恢复这里的备份。 我应该做的，并且最终起作用的，是打开恢复对话框，并在目标字段中输入新数据库的名称

因此，简而言之，从备份中恢复是有效的。

谢谢大家的反馈和建议

在SQL Server 2008 R2中，将数据库以文件的形式备份到文件夹中。 然后选择“Database”文件夹中的恢复选项。 在向导中输入目标数据库中需要的新名称。 然后选择“从文件恢复”并使用刚才创建的文件。 我就这样做了，它非常快(我的DB很小，但仍然) 巴勃罗。< / p >

这是我使用的脚本。有点棘手，但很有效。在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

1. 安装Microsoft SQL Management Studio，你可以从微软的网站免费下载:

   2008年版本

   Microsoft SQL Management Studio 2008是SQL Server 2008 Express with Advanced Services的一部分

   2012年版本

   单击下载按钮，选中ENU\x64\SQLManagementStudio_x64_ENU.exe

   2014年版本

   单击下载按钮，检查MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

2. 打开Microsoft SQL Management Studio。

3. 备份原始数据库到。bak文件(db ->任务->备份)。
4. 用新名称创建空数据库(克隆)。注意下面的注释，因为这是可选的。
5. 点击克隆数据库并打开恢复对话框(见图片)
6. 选择“Device”，添加步骤3中的备份文件。
7. 更改测试数据库的目标
8. 更改数据库文件的位置，必须与原来的位置不同。您可以直接输入文本框，只需添加后缀。(注意:顺序很重要。选中复选框，然后更改文件名。)
9. 检查WITH REPLACE和WITH KEEP_REPLICATION

使用MS SQL Server 2012，您需要执行3个基本步骤:

1. 首先，生成只包含源DB结构的.sql文件
   • 右键单击源DB，然后任务，然后生成脚本
   • 遵循向导并在本地保存.sql文件
   李< / ul > < / >

   • 其次，将源DB替换为.sql文件中的目标DB

     • 右键单击目标文件，选择新的查询和Ctrl-H或(编辑 - 寻找并替换 - 快速更换)
     李< / ul > < / >
     • 最后，填充数据
       • 右键单击目标DB，然后选择任务和导入数据
       • 数据源下拉设置为“.net框架数据提供程序用于SQL server”+设置Data ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15下的连接字符串文本字段
       • 对目的地做同样的处理吗
       • 选中要转移的表，或者在“源:…”之外的复选框进行全选
       李< / ul > < / >

你完成了。

另一种方法是使用导入/导出向导，首先创建一个空数据库，然后选择具有源数据库的源服务器，然后在目标中选择具有目标数据库的同一服务器(使用您首先创建的空数据库)，然后点击完成

它会创建所有的表，并将所有的数据传输到新的数据库中，

这里提到的解决方案都不适合我-我使用的是SQL Server Management Studio 2014。

脚本基于乔回答(分离，复制文件，附加两者)。

1. 以Administrator帐户运行management Studio。

这是不必要的，但可能在执行时访问被拒绝错误。

2. 为执行xp_cmdshel配置sql server

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

3. 运行脚本，但在@dbName和@copyDBName变量中输入你的db名称。

USE master;
GO


DECLARE @dbName NVARCHAR(255) = 'Products'
DECLARE @copyDBName NVARCHAR(255) = 'Products_branch'


-- get DB files
CREATE TABLE ##DBFileNames([FileName] NVARCHAR(255))
EXEC('
INSERT INTO ##DBFileNames([FileName])
SELECT [filename] FROM ' + @dbName + '.sys.sysfiles')


-- drop connections
EXEC('ALTER DATABASE ' + @dbName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE')


EXEC('ALTER DATABASE ' + @dbName + ' SET SINGLE_USER')


-- detach
EXEC('EXEC sp_detach_db @dbname = ''' + @dbName + '''')


-- copy files
DECLARE @filename NVARCHAR(255), @path NVARCHAR(255), @ext NVARCHAR(255), @copyFileName NVARCHAR(255), @command NVARCHAR(MAX) = ''
DECLARE
@oldAttachCommand NVARCHAR(MAX) =
'CREATE DATABASE ' + @dbName + ' ON ',
@newAttachCommand NVARCHAR(MAX) =
'CREATE DATABASE ' + @copyDBName + ' ON '


DECLARE curs CURSOR FOR
SELECT [filename] FROM ##DBFileNames
OPEN curs
FETCH NEXT FROM curs INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = REVERSE(RIGHT(REVERSE(@filename),(LEN(@filename)-CHARINDEX('\', REVERSE(@filename),1))+1))
SET @ext = RIGHT(@filename,4)
SET @copyFileName = @path + @copyDBName + @ext


SET @command = 'EXEC master..xp_cmdshell ''COPY "' + @filename + '" "' + @copyFileName + '"'''
PRINT @command
EXEC(@command);


SET @oldAttachCommand = @oldAttachCommand + '(FILENAME = "' + @filename + '"),'
SET @newAttachCommand = @newAttachCommand + '(FILENAME = "' + @copyFileName + '"),'


FETCH NEXT FROM curs INTO @filename
END
CLOSE curs
DEALLOCATE curs


-- attach
SET @oldAttachCommand = LEFT(@oldAttachCommand, LEN(@oldAttachCommand) - 1) + ' FOR ATTACH'
SET @newAttachCommand = LEFT(@newAttachCommand, LEN(@newAttachCommand) - 1) + ' FOR ATTACH'


-- attach old db
PRINT @oldAttachCommand
EXEC(@oldAttachCommand)


-- attach copy db
PRINT @newAttachCommand
EXEC(@newAttachCommand)


DROP TABLE ##DBFileNames

-- 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 ->任务→备份)。

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在“恢复状态”!

& lt; !doctype html>

<head>
<title>Copy Database</title>
</head>


<body>
    

<?php
    

$servername = "localhost:xxxx";
$user1 = "user1";
$pw1 = "pw1";
$db1 = "db1";
    

$conn1 = new mysqli($servername,$user1,$pw1,$db1);
    

if($conn1->connect_error) {
die("Conn1 failed: " . $conn1->connect_error);
}
    

$user2 = "user2";
$pw2 = "pw2";
$db2 = "db2";
    

$conn2 = new mysqli($servername,$user2,$pw2,$db2);
    

if($conn2->connect_error) {
die("Conn2 failed: " . $conn2->connect_error);
}
    

$sqlDB1 = "SELECT * FROM table1";
$resultDB1 = $conn1->query($sqlDB1);
    

if($resultDB1->num_rows > 0) {
while($row = $resultDB1->fetch_assoc()) {
$sqlDB2 = "INSERT INTO table2 (col1, col2) VALUES ('" . $row["tableRow1"] . "','" . $row["tableRow2"] . "')";
$resultDB2 = $conn2->query($sqlDB2);
}
}else{
echo "0 results";
}
    

$conn1->close();
$conn2->close();
    

?>
    

</body>

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

-- 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

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

DBCC CLONEDATABASE (CurrentDBName, NewDBName)
GO

详细信息