Posts Tagged ‘TSQL’

TSQ: Estimated database restore completion

Here’s a query proving you approximate percentage compeled, and estimated finish time, of any database restores happening on a SQL Server instance… ?View Code TSQLSELECT st.[text], r.percent_complete, DATEADD(SECOND, r.estimated_completion_time/1000, GETDATE()) AS estimated_completion_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) st WHERE [command] = ‘RESTORE DATABASE’; The resultset will look something like below… text percent_complete estimated_completion_time […]

TSQL: Database Mirroring with Certificates

Here’s some more TSQL for the 70-462 exam. The script shows the actions needed to configure database mirroring using certificates for authentication. Explanatory notes are included but you’re likely to need the training materials for this to make sense. TSQL is not included for the backup/restore parts needed for database mirroring. ?View Code TSQLSELECT * […]

TSQL: Database Permission Excercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included. ?View Code TSQLCREATE DATABASE Saturn; GO   USE Saturn; GO CREATE ROLE Moon_Table_Editors; GO   — Create tables CREATE TABLE Mimas ( id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY, column1 VARCHAR(100) NOT NULL ); GO CREATE TABLE Thethys ( […]

TSQL: Partially Contained Databases 70-462

Here’s some TSQL for the Partially Contained Databases section of the 70-462. Explanatory comments are included. ?View Code TSQL– enable show advanced options and view current config EXEC sp_configure ‘show advanced options’, 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure; GO — enable the feature EXEC sp_configure ‘contained database authentication’, 1; GO RECONFIGURE WITH OVERRIDE; […]

TSQL: User-Defined Server Roles 70-462

Just a little TSQL for the User-Defined Server Roles exercise in the 70-462 training materials. Explanatory comments are included. ?View Code TSQLUSE [master]; GO   — Create server role CREATE SERVER ROLE [Login_Manager]; GO   — Assign permission to server role GRANT ALTER ANY LOGIN TO [Login_Manager]; GO   — Add login to role ALTER SERVER […]