Crear copy del esquema en el server de MS SQL

Soy relativley nuevo al server del ms SQL. Tengo que crear una database de testing de exisitng database de testing con el mismo esquema y get los datos de la producción y llenar la recién creada database vacía. Para esto yo estaba usando generar scripts en SSMS. Pero ahora tengo que hacerlo en forma regular en un trabajo. Guíame por favor cómo puedo crear las bases de datos vacías automáticamente en un punto del time.

Gracias

Venkatesh

Aquí está un procedimiento almacenado rápido y sucio que creé basado en esta respuesta sobre sobre desbordamiento de la stack:

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 

Ejecutar:

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

Hay seis parameters:

  • @SrcDB – el nombre del DB de origen que desea clonar
  • @DstDB – el nombre del DB de destino al que desea clonar
  • @DataDir : la carpeta física en la que se debe restaurar el file de datos del DB clonado
  • @LogDir : la carpeta física en la que se debe restaurar el file de logging del DB clonado. Esto es opcional y si lo dejas fuera entonces el file de logging será restaurado en @DataDir
  • @BackupDir – donde crear la copy de security. Esto es opcional y si se @DataDir se utilizará la carpeta @DataDir
  • @OverWrite – indica si se debe sobrescribir forzosamente la database de desinstallation si ya existe: 0 = No sobrescribir, 1 = Sobrescribir. Esto es opcional y si no se especifica no sobrescribirá un DB existente si ya existe

El procedimiento almacenado es bastante ingenuo y asume que su database sólo utiliza el valor pnetworkingeterminado de dos files, uno para los datos y otro para los loggings.

Si tiene más de dos files, deberá modificar el procedimiento para manejar esa condición.