SQL scripts that may be helpful for MS SQL Server DBAs in their daily tasks:
1.Script to Backup a Database:
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName.bak'
WITH INIT, FORMAT, COMPRESSION;
2.Script to Restore a Database:
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName.bak'
WITH REPLACE, RECOVERY;
3.Script to Check Database Integrity:
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;
Script to Shrink Transaction Log File:
DBCC SHRINKFILE ('YourDatabaseName_log', 1);
SELECT
r.session_id,
r.start_time,
r.status,
r.command,
t.text AS [SQL Text]
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE
r.status NOT IN
('background', 'sleeping')
ORDER BY
r.start_time DESC;
4..Script to Grant Permissions to a User:
USE [YourDatabaseName]
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [YourSchema].[YourTable]
TO [YourUserName];
5.Script to Update Statistics for a Table:
USE [YourDatabaseName]
GO
UPDATE STATISTICS [YourSchema].[YourTable];
