How to monitor backup and restore progress in SQL Server

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

get or disconnect sessions from multiple servers GUI

https://gallery.technet.microsoft.com/scriptcenter/get-or-disconnect-sessions-e7493aef

How do I shrink all files quickly for all databases?

Run below query on DB server

DECLARE @SQL VARCHAR(MAX) = '' SELECT @SQL = @SQL + 'DECLARE @TA_LOGFILE_NAME VARCHAR(500)' SELECT @SQL = @SQL + 'DECLARE @TA_DATABASE_NAME VARCHAR(500)' SELECT @SQL = @SQL + 'USE [' + d.name + N']' + CHAR(13) + CHAR(10) + 'EXEC (''ALTER DATABASE ['+d.name+'] SET RECOVERY SIMPLE'');' + CHAR(13) + CHAR(10) + 'SELECT @TA_LOGFILE_NAME=NAME FROM SYS.DATABASE_FILES WHERE DATA_SPACE_ID=0' + CHAR(13) + CHAR(10) + 'SELECT @TA_DATABASE_NAME = DB_NAME()' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (@TA_LOGFILE_NAME, 1);' + CHAR(13) + CHAR(10) + 'EXEC (''ALTER DATABASE ['+d.name+'] SET RECOVERY FULL'');' + CHAR(13) + CHAR(10) + '--PRINT @TA_DATABASE_NAME + ''LOG SHRINKED''' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) FROM sys.databases d WHERE d.database_id > 4; EXEC(@SQL)

C# How to implement method that return list of SQL result?

http://stackoverflow.com/questions/18754688/c-sharp-how-to-implement-method-that-return-list-of-sql-result

Update COMPATIBILITY_LEVEL for all databases in sql server

USE MASTER DECLARE GET_DATABASES CURSOR READ_ONLY FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '120' DECLARE @DATABASENAME NVARCHAR(255) DECLARE @COUNTER INT SET @COUNTER = 1 OPEN GET_DATABASES FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN -- CHANGE DATABASE COMPATIBILITY EXECUTE sp_dbcmptlevel @DATABASENAME , '120' PRINT @DATABASENAME + ' changed' SET @COUNTER = @COUNTER + 1 END FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME END CLOSE GET_DATABASES DEALLOCATE GET_DATABASES GO