Veritabanı ortamları oluşturma

DECLARE @environments TABLE (Environment VARCHAR(255) not null);
INSERT @environments(Environment) VALUES ('Test'),('Dev'),('DevAkif'),('DevGuest');
DECLARE @environment VARCHAR(255);
DECLARE @prefix VARCHAR(255)='DB_Name_';
DECLARE @path VARCHAR(255) =N'C:\MSSQL\Backup\Yesterday\Backup.bak';
DECLARE @backup_dbname VARCHAR(255) = 'DB_Name';
DECLARE @backup_dbname_log VARCHAR(255) = 'DB_Name_log';
DECLARE @data_path VARCHAR(255) ='C:\MSSQL\DATA\';
DECLARE @mdf VARCHAR(4)= '.mdf';
DECLARE @log VARCHAR(4)='_log';
DECLARE @ldf VARCHAR(4)= '.ldf';

DECLARE CreateEnvironmentsCursor CURSOR FOR
SELECT Environment FROM @environments;

OPEN CreateEnvironmentsCursor 
FETCH NEXT FROM CreateEnvironmentsCursor INTO @environment ;

WHILE @@FETCH_STATUS = 0 
BEGIN 
    PRINT @environment+ ' yedek alınıyor...';
    DECLARE @environment_db_name VARCHAR(255) =@prefix+@environment;
    DECLARE @environment_logical_name VARCHAR(255) = @environment_db_name;
    DECLARE @environment_mdf_path VARCHAR(255) = @data_path +@environment_db_name+ @mdf;
    DECLARE @environment_logical_log_name VARCHAR(255) = @environment_db_name + @log;
    DECLARE @environment_ldf_path VARCHAR(255) = @data_path +@environment_db_name+@log+@ldf;
    IF EXISTS (SELECT name FROM sys.databases WHERE name = @environment_db_name)
        BEGIN
            EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = @environment_db_name
            USE [master]
            EXEC('DROP DATABASE '+@environment_db_name)
        END
    RESTORE FILELISTONLY FROM DISK=@path

    RESTORE DATABASE @environment_db_name
    FROM DISK = @path
    WITH
    MOVE @backup_dbname TO @environment_mdf_path,
    MOVE @backup_dbname_log TO @environment_ldf_path

    EXEC('ALTER DATABASE '+@environment_db_name+' MODIFY FILE (NAME=['+@backup_dbname+'], NEWNAME='+@environment_logical_name+')');
    EXEC('ALTER DATABASE '+@environment_db_name+' MODIFY FILE (NAME=['+@backup_dbname_log+'], NEWNAME='+@environment_logical_log_name+')');

    EXEC('USE '+@environment_db_name +'; '
     +' ALTER DATABASE '+@environment_logical_name+' SET RECOVERY SIMPLE WITH NO_WAIT;'
    +'DBCC SHRINKFILE('+@environment_logical_log_name+') ;'
    +'ALTER DATABASE '+@environment_logical_name+' SET RECOVERY FULL WITH NO_WAIT;');

    IF USER_ID(@environment_db_name) IS NULL
    BEGIN
        CREATE USER [TestUser] FOR LOGIN [TestUser] -- Önceden oluşturulan user
    END
        ALTER ROLE [db_owner] ADD MEMBER [TestUser]

    FETCH NEXT FROM CreateEnvironmentsCursor INTO @environment
END

CLOSE CreateEnvironmentsCursor 
DEALLOCATE CreateEnvironmentsCursor