我有一个MS SQL Server 2008 Express系统，其中包含一个数据库，我想“复制和重命名”(用于测试目的)，但我不知道一个简单的方法来实现这一点。
我注意到在R2版本的SQL Server中有一个复制数据库向导，但遗憾的是我无法升级。
我们讨论的数据库大约是1g。 我试图恢复我想复制到一个新数据库的数据库的备份，但没有运气。
当前回答
来自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在“恢复状态”!
其他回答
<！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
在SQL Server 2008 R2操作系统中，将数据库以文件的形式备份到文件夹中。 然后选择“Database”文件夹中的恢复选项。 在向导中输入目标数据库中需要的新名称。 然后选择“从文件恢复”并使用刚才创建的文件。 我就这样做了，它非常快(我的DB很小，但仍然) 巴勃罗。
使用MS SQL Server 2012，您需要执行3个基本步骤:
First, generate .sql file containing only the structure of the source DB right click on the source DB and then Tasks then Generate Scripts follow the wizard and save the .sql file locally Second, replace the source DB with the destination one in the .sql file Right click on the destination file, select New Query and Ctrl-H or (Edit - Find and replace - Quick replace) Finally, populate with data Right click on the destination DB, then select Tasks and Import Data Data source drop down set to ".net framework data provider for SQL server" + set the connection string text field under DATA ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15 do the same with the destination check the table you want to transfer or check box besides "source: ..." to check all of them
你完成了。
这是我使用的脚本。有点棘手，但很有效。在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
推荐文章
- SQL OVER()子句-它什么时候有用，为什么有用?
- 检查SQL Server登录是否已经存在
- 我如何使用ROW_NUMBER()?
- 如何检查表上持有哪些锁
- SQL Server数据库备份恢复到低版本
- SQL Server:过滤sp_who2的输出
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- 如何将SQL Azure数据库复制到本地开发服务器?
- SQL Server 2008不能用新创建的用户登录
- SQL Server中User和Login的区别
- 在参数声明中，varchar(MAX)的大小是多少?
- 数据库性能调优有哪些资源?
- 为两列的组合添加唯一的约束
