March 2017 Entries

SQL Database is in use and cannot restore

USE masterGOALTER DATABASE <mydatabase>SET SINGLE_USER--This rolls back all uncommitted transactions in the db.WITH ROLLBACK IMMEDIATEGORESTORE DATABASE <mydatabase> FILE = N'mydatabase' FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\... WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10GO ......
Posted On Friday, March 31, 2017 3:17 PM | Comments (15)

SQL Server - Kill any live connections to the DB

Often you need to restore a DB or take oit offline only to find out the process aborts due to active connected sessions to the DB. Here is a quick sript that will kill all active sessions to the DB.USE [master];DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessionsWHERE database_id = db_id('myDataBase')EXEC(@ki... ......
Posted On Friday, March 17, 2017 3:47 PM | Comments (2)