我有一个MS SQL Server 2008 Express系统,其中包含一个数据库,我想“复制和重命名”(用于测试目的),但我不知道一个简单的方法来实现这一点。
我注意到在R2版本的SQL Server中有一个复制数据库向导,但遗憾的是我无法升级。
我们讨论的数据库大约是1g。 我试图恢复我想复制到一个新数据库的数据库的备份,但没有运气。
我有一个MS SQL Server 2008 Express系统,其中包含一个数据库,我想“复制和重命名”(用于测试目的),但我不知道一个简单的方法来实现这一点。
我注意到在R2版本的SQL Server中有一个复制数据库向导,但遗憾的是我无法升级。
我们讨论的数据库大约是1g。 我试图恢复我想复制到一个新数据库的数据库的备份,但没有运气。
当前回答
Install Microsoft SQL Management Studio, which you can download for free from Microsoft's website: Version 2008 Microsoft SQL Management Studio 2008 is part of SQL Server 2008 Express with Advanced Services Version 2012 Click download button and check ENU\x64\SQLManagementStudio_x64_ENU.exe Version 2014 Click download button and check MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe Open Microsoft SQL Management Studio. Backup original database to .BAK file (db -> Task -> Backup). Create empty database with new name (clone). Note comments below as this is optional. Click to clone database and open restore dialog (see image) Select Device and add the backup file from step 3. Change destination to test database Change location of database files, it must be different from the original. You can type directly into text box, just add postfix. (NOTE: Order is important. Select checkbox, then change the filenames.) Check WITH REPLACE and WITH KEEP_REPLICATION
其他回答
右键单击要克隆的数据库,单击Tasks,单击Copy database ....遵循向导,您就完成了。
这里提到的解决方案都不适合我-我使用的是SQL Server Management Studio 2014。
相反,我不得不取消“选项”屏幕中的“在恢复前进行尾日志备份”复选框:在我的版本中,默认情况下它是选中的,并阻止恢复操作完成。 取消检查后,还原操作继续进行,没有出现问题。
如果数据库不是很大,你可以看看SQL Server Management Studio Express中的“Script database”命令,它位于资源管理器中数据库项本身的上下文菜单中。
你可以选择什么都脚本;当然,您需要对象和数据。然后将整个脚本保存到一个文件中。然后,您可以使用该文件重新创建数据库;只要确保顶部的USE命令被设置为正确的数据库。
原来我试图从备份中错误地进行恢复。
最初我创建了一个新数据库,然后尝试在这里恢复备份。 我本应该做的,也是最终有效的,是打开恢复对话框,并在目标字段中输入新数据库的名称。
因此,简而言之,从备份中恢复是有效的。
谢谢大家的反馈和建议
该程序以不同的名称将数据库复制到同一服务器。我依赖于这个网站上给出的例子,并进行了一些改进。
-- 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