Uncategorized

Delete large backup history

msdb can get massive when the backup history isn’t purged regularly. In that state, sp_delete_backuphistory can run for a very long time (and sometimes becomes a maintenance nightmare). I built an alternative cleanup script that: purges old rows in batches (TOP + loop) is designed to be restartable and predictable…

Read more
Uncategorized

Create and restore database from database snapshot

USE [master] GO CREATE DATABASE [MY_DB_01] ON ( NAME = N’MY_DB’, FILENAME = N’E:\DATA\MY_DB.ss’ ) AS SNAPSHOT OF [MY_DB]; GO — REVERT TO SNAPSHOT USE [master] GO — Close existing connections DECLARE @Sql VARCHAR(1000) SET @Sql = ” SELECT @Sql = @Sql + ‘kill ‘ + CONVERT(NVARCHAR(3), [spid]) + ‘;…