Создание копии схемы в SQL-сервере MS

Here's a quick and dirty stored procedure I created based on this answer over on 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

To execute:

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

There are six parameters:

• @SrcDB - the name of the source DB you want to clone
• @DstDB - the name of the destination DB you want to clone to
• @DataDir - the physical folder where the cloned DB's data file should be restored to
• @LogDir - the physical folder where the cloned DB's log file should be restored to. This is optional and if you leave it out then the log file will be restored into @DataDir
• @BackupDir - where to create the backup. This is optional and if left out will cause the @DataDir folder to be used
• @OverWrite - flag to indicate whether to forcibly overwrite the desination database if it already exists: 0 = Don't overwrite, 1 = Overwrite. This is optional and if not specified it will not overwrite an existing DB if it already exists

The stored procedure is pretty naive and assumes that your database just uses the default of two files - one for the data and one for the logs.

If you have more than two files then you'll need to amend the procedure to handle that condition.