☰

在 MS SQL 服务器中创建架构的副本

tag-icon • tag-icon sql-server-2005 database
在 MS SQL 服务器中创建架构的副本

我对 MS SQL 服务器还比较陌生。我需要从现有测试数据库中创建一个具有相同架构的测试数据库，并从生产中获取数据并填充新创建的空数据库。为此，我使用了 SSMS 中的生成脚本。但现在我需要在工作中定期执行此操作。请指导我如何在某个时间点自动创建空数据库。

谢谢

文卡特什

答案1

这是我基于以下代码创建的快速而肮脏的存储过程这个答案Stack Overflow 上：

CREATE PROCEDURE CloneDatabase
(
  @SrcDB VARCHAR(250), -- source DB
  @DstDB VARCHAR(250), -- destination DB
  @DataDir VARCHAR(250), -- where to put cloned database
  @LogDir VARCHAR(250) = NULL, -- where to put cloned DB logs
  @BackupDir VARCHAR(250) = NULL, -- folder to use for backup
  @Overwrite BIT = 0 -- Set to 1 if Destination DB should be dropped
)
AS

------------------------------------
IF @LogDir IS NULL SET @LogDir = @DataDir
IF @BackupDir IS NULL SET @BackupDir = @DataDir

/* PART 1: Backup the good database */

DECLARE @BackupFile VARCHAR(250)
DECLARE @BackupName VARCHAR(250) 
SET @BackupFile = @BackupDir + @SrcDB + '.bak'
SET @BackupName = N' - Full Database Backup of: ' + @SrcDB

PRINT 'Backup to: ' + @BackupFile

BACKUP DATABASE @SrcDB 
  TO  DISK = @BackupFile WITH NOFORMAT, 
  INIT,  
  NAME = @BackupName, SKIP, 
  NOREWIND, 
  NOUNLOAD,  
  STATS = 33

-- Check to see if we should overwrite if the destination already exists
DECLARE @DoRestore BIT 
SET @DoRestore = 0

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DstDB) OR @Overwrite = 1
BEGIN
  SET @DoRestore = 1
END

IF @DoRestore = 1
BEGIN
  /* PART 3: Restore the backup to the new location */

  --
  -- Find out our logical file names in the source DB. 
  -- This is slightly naive and assumes that there is just one log file 
  -- and one datafile, so beware!
  --

  DECLARE @LogicalDataFileName VARCHAR(250), @LogicalLogfileName VARCHAR(250)
  SET @LogicalDataFileName = 
    (SELECT m.Name FROM sys.databases d 
    JOIN sys.master_files m ON m.database_id = d.database_id
    WHERE d.name = @SrcDB AND m.type = 0 AND m.state = 0)

  SET @LogicalLogfileName = 
    (SELECT m.Name FROM sys.databases d 
    JOIN sys.master_files m ON m.database_id = d.database_id
    WHERE d.name = @SrcDB AND m.type = 1 AND m.state = 0)

  DECLARE @DstPhysicalDataFileName VARCHAR(250)
  DECLARE @DstPhysicalLogFileName VARCHAR(250) 
  SET @DstPhysicalDataFileName = @DataDir + @DstDb + '.mdf'
  SET @DstPhysicalLogFileName = @DataDir + @DstDb + '_log.ldf'

  RESTORE DATABASE @DstDB
      FROM DISK = @BackupFile WITH FILE = 1,  
      MOVE @LogicalDataFileName TO @DstPhysicalDataFileName,  
      MOVE @LogicalLogfileName TO @DstPhysicalLogFileName,  
      REPLACE,
      NOUNLOAD,
      STATS = 33

  /* PART 4: Delete all tables' data in the clone */

  PRINT N'Clearing down data: ' + @DstDB
  DECLARE @SQL VARCHAR(MAX)
  SET @SQL = 'USE ' + @DstDB + ' ; ' +
     'EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"; ' +
     'EXEC sp_MSForEachTable "DELETE FROM ?"; ' + 
     'EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";'
  EXEC(@SQL)
END

执行：

EXEC CloneDatabase 
  @SrcDB = 'SourceDatabase',
  @DstDB = 'DestinationDatabase',
  @DataDir = 'd:\MSSQL\data\',
  @LogDir = 'd:\MSSQL\Logs\'

共有六个参数：

  • @SrcDB- 您要克隆的源数据库的名称
  • @DstDB- 您要克隆到的目标数据库的名称
  • @DataDir- 应将克隆的数据库数据文件还原到的物​​理文件夹
  • @LogDir- 克隆数据库的日志文件应还原到的物​​理文件夹。这是可选的，如果您忽略它，则日志文件将还原到@DataDir
  • @BackupDir- 在哪里创建备份。这是可选的，如果省略，将导致@DataDir使用文件夹
  • @OverWrite- 标志指示是否强制覆盖目标数据库（如果已存在）：0 = 不覆盖，1 = 覆盖。这是可选的，如果未指定，则不会覆盖已存在的现有数据库

该存储过程非常简单，假定您的数据库仅使用两个文件的默认值 - 一个用于数据，一个用于日志。

如果您有两个以上的文件，那么您将需要修改程序来处理该情况。

相关内容