Crisis TSQL

Full Backup

BACKUP DATABASE [DBName] TO DISK = 'E:\path\DBName.Bak' WITH FORMAT

Restore

RESTORE DATABASE [DBName]
FROM DISK = 'E:\path\DBName.Bak'
WITH RECOVERY

Backup File Contents

RESTORE FILELISTONLY
FROM DISK = 'E:\path\DBName.Bak'

Restore moving files

RESTORE DATABASE [DBName]
FROM DISK = 'E:\path\DBName.Bak'
WITH MOVE 'datafilelogicalname' TO 's:\path\dbname.ldf',
MOVE 'logfilelogicalname' TO 'l:\path\dbname.ldf'
WITH REPLACE, RECOVERY

Prevent connections if replacing db

ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Restore with log files

RESTORE DATABASE [DBName]
FROM DISK = N'E:\path\dbname_Full.bak'
WITH NORECOVERY -- NORECOVERY allows more backup files to be restored
GO

RESTORE LOG [DBName]
FROM DISK = N'E:\path\dbname_log_1.bak'
WITH NORECOVERY
GO

RESTORE LOG [DBName]
FROM DISK = N'E:\path\dbname_log_2.bak'
WITH RECOVERY -- Change to RECOVERY after final log backup
GO

Set Single User , Read Only, Multi User Modes

USE master;
GO
ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [DBName] SET READ_ONLY;
GO
ALTER DATABASE [DBName] SET MULTI_USER;
GO


Shrink Log File

USE [DBName]; GO;
DBCC SHRINKFILE (N'DBName_log' , 0, TRUNCATEONLY)


Attach MDF without LDF

USE [master] ;GO;

-- Method 1: Preffered
EXEC sp_attach_single_file_db @dbname='DBName', @physname=N'C:\path\DBName.mdf'

-- Method 2:
CREATE DATABASE DBName ON (FILENAME = N'C:\path\DBName.mdf')
FOR ATTACH_REBUILD_LOG