我有一个MS SQL Server 2008 Express系统，其中包含一个数据库，我想“复制和重命名”(用于测试目的)，但我不知道一个简单的方法来实现这一点。
我注意到在R2版本的SQL Server中有一个复制数据库向导，但遗憾的是我无法升级。
我们讨论的数据库大约是1g。 我试图恢复我想复制到一个新数据库的数据库的备份，但没有运气。
当前回答
该程序以不同的名称将数据库复制到同一服务器。我依赖于这个网站上给出的例子，并进行了一些改进。
-- 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 Management Studio Express中的“Script database”命令，它位于资源管理器中数据库项本身的上下文菜单中。
你可以选择什么都脚本;当然，您需要对象和数据。然后将整个脚本保存到一个文件中。然后，您可以使用该文件重新创建数据库;只要确保顶部的USE命令被设置为正确的数据库。
您可以只创建一个新数据库，然后转到任务，导入数据，并将所有数据从您想要复制的数据库导入到刚刚创建的数据库。
如果你是MS SQL 2014及更新版本;
DBCC CLONEDATABASE (CurrentDBName, NewDBName)
GO
细节;
另一种方法是使用导入/导出向导，首先创建一个空数据库，然后选择具有源数据库的源服务器，然后在目标中选择具有目标数据库的同一服务器(使用您首先创建的空数据库)，然后点击完成
它会创建所有的表，并将所有的数据传输到新的数据库中，
该程序以不同的名称将数据库复制到同一服务器。我依赖于这个网站上给出的例子，并进行了一些改进。
-- 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)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列，添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?
