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

SINGLE USER modundan veritabanını MULTI USER olarak ayarlama

use master
GO

select 
    d.name, 
    d.dbid, 
    spid, 
    login_time, 
    nt_domain, 
    nt_username, 
    loginame
from sysprocesses p 
    inner join sysdatabases d 
        on p.dbid = d.dbid
where d.name = 'dbname'
GO

kill 56 --=> kill the number in spid field
GO

exec sp_dboption 'dbname', 'single user', 'FALSE'
GO

https://stackoverflow.com/questions/14652923/set-database-from-single-user-mode-to-multi-user 

Is there any way to get a list of open/allocated cursors in SQL server?

USE MASTER
GO
select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name
, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id


Açık kalan Cursorları listeleme


Kaynak: https://stackoverflow.com/questions/265605/is-there-any-way-to-get-a-list-of-open-allocated-cursors-in-sql-server