/****** Object: StoredProcedure [dbo].[uspCopyToBeta] Script Date: 10/08/2013 11:04:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[uspCopyToBeta] ( @DbName sysname ) AS -- IMPORTANT! The procedure requires a signature -- and modifying the procedure will require it to be -- re-signed. Ensure that you do so after altering -- the procedure. BEGIN SET NOCOUNT, XACT_ABORT ON; BEGIN TRY DECLARE @betaDb sysname, @backupSetId int, @backupSetPosition int, @msg nvarchar(MAX), @sql nvarchar(MAX), @moves nvarchar(MAX); -- Validate that the input is valid IF @DbName IN ('master', 'model', 'msdb', 'tempdb') RAISERROR('Cannot operate on a system database.', 15, 1) WITH NOWAIT; IF NOT EXISTS(SELECT NULL FROM sys.databases WHERE name = @DbName) RAISERROR('Invalid database name. Verify the name of database', 15, 1) WITH NOWAIT; IF @DbName LIKE '%Beta' RAISERROR('You cannot copy a beta database; use a live database as the target for copying to beta.', 15, 1) WITH NOWAIT; -- Set up the parameters SET @betaDb = @DbName + N'Beta'; --Create a copy-only backup of the live database SET @sql = N'BACKUP DATABASE ' + QUOTENAME(@DbName) + N' TO DISK = N''C:\SQL_Temp\Temp.bak'' WITH COPY_ONLY, FORMAT, INIT, NAME = N''Backup From Live to Beta'', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM'; --PRINT @sql; --For debugging EXEC sys.sp_executesql @sql; --Verify backup SELECT @backupSetId = backup_set_id, @backupSetPosition = position FROM msdb..backupset WHERE database_name = @DbName AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @DbName ); IF @backupSetPosition IS NULL BEGIN SET @msg = N'Verify failed. Backup information for database ''' + @DbName + ''' not found.'; RAISERROR(@msg, 16, 1); END; RESTORE VERIFYONLY FROM DISK = N'C:\SQL_Temp\Temp.bak' WITH FILE = @backupSetPosition, NOUNLOAD, NOREWIND; --Verify that all files backed up have @DbName in it IF EXISTS( SELECT NULL FROM msdb..backupfile WHERE backup_set_id = @backupSetId AND NOT physical_name LIKE '%' + @DbName + '%' ) RAISERROR(N'Cannot restore the backups because the filenames do not contain the database name and thus cannot be automated. Contact an administrator.', 15, 1) WITH NOWAIT; --Build the MOVEs SET @moves = STUFF(( SELECT N', MOVE ''' + logical_name + ''' TO N''' + REPLACE(physical_name, @DbName, @betaDb) + N''' ' FROM msdb..backupfile WHERE backup_set_id = @backupSetId FOR XML PATH(N'') ), 1, 2, N''); --PRINT @moves; -- For debugging --Restore backup to beta SET @sql = N'RESTORE DATABASE ' + QUOTENAME(@betaDb) + N' FROM DISK = N''C:\SQL_Temp\Temp.bak'' WITH FILE = 1, ' + @moves + N', NOUNLOAD, REPLACE, STATS = 10;'; --PRINT @sql; -- For debugging EXEC sys.sp_executesql @sql; --PRINT 'Beta restored successfully'; -- For debugging END TRY BEGIN CATCH DECLARE @err nvarchar(MAX) = CAST(ERROR_NUMBER() AS nvarchar(11)) + N': ' + CAST(ERROR_MESSAGE() AS nvarchar(MAX)); RAISERROR(@err, 15, 1) WITH NOWAIT; END CATCH; END; GO GRANT EXECUTE ON [dbo].[uspCopyToBeta] TO [BackupOperators] AS [dbo];