SQL DB backup and Restore script

Backup DB

USE [master]
BACKUP DATABASE [DB_NAME] TO  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
BACKUP DATABASE [AB_NAME_model] TO  DISK = N'F:\SQLBackup\build_AxDB_model.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Restore DBs

#USE [master]

RESTORE DATABASE [DB_NAME] FROM  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Find clients using the DB

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)
INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = 'DB_NAME'

To Kill

USE [master];

DECLARE @kill varchar(8000) = '';  

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  

FROM sys.dm_exec_sessions

WHERE database_id  = db_id('DBNAME')

EXEC(@kill);

Find Top #n tables with max size

USE [D_WBX_BUILD]
select top 30 schema_name(tab.schema_id) + '.' + tab.name as [table],
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
     on tab.object_id = ind.object_id
join sys.partitions part
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;


Command to delete data from the table

use [DB_NAME]
--    TRUNCATE TABLE        dbo.DMFWBXASNPACKSTRUCTUREENTITY