MSSQL: Backup (COPY_ONLY) and restoring database under different name.

Back up the database with COPY_ONLY to reduce interference to the production environment.

USE MASTER ;

BACKUP DATABASE VCDB TO DISK = 'C:\VCDB\MyDatabase1.bak' WITH COPY_ONLY ;

--Do not have to create new db first. Can be restore with new db name. The new db name will be created automagically.

USE MASTER ;

RESTORE DATABASE VCDB4 FROM DISK = 'C:\VCDB\MyDatabase1.bak' WITH MOVE 'VCDB' TO 'C:\VCDB\VCDB_3.mdf', MOVE 'VCDB_log' TO 'C:\VCDB\VCDB_3_log.ldf' ,REPLACE, RECOVERY

Left click and right click on Task - Restore database will do too

Select -  Restore Files and Filegroups

MSSQL: reduce the tempdb usage

READ COMMITTED SNAPSHOT can reduce the usage of tempdb .

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v%3dsql.105)Default and query to find out what I have now

2 Types

• A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.
• A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.

 ALTER DATABASE VCDB_2016 SET READ_COMMITTED_SNAPSHOT ON;  

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v=sql.105)

Occasionally, vpx_text_array flushing can cause tempdb to grow exponentially.  Setting READ_COMMITTED_SNAPSHOT to ON may be the only option if DB recovery already in SIMPLE mode.

MSSQL: How to mirror a database from one server to another.

How to mirror a database from one server to another.

Mirroring a database to a second node is fairly simple. There are basically 4 steps where Restore will be in NORECOVERY mode.

Prerequisite

1. The database must be in a FULL RECOVERY MODE. 
2. Identical windows and SQL Server logons and privileges.

Verify database recovery model.

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'VCDB_2016' ; 

If it is not in FULL RECOVERY such as SIMPLE or BulkLogged, switch it to FULL.

USE MASTER

ALTER DATABASE VCDB_2016 SET RECOVERY FULL;

Step 1: Full Database Backup

BACKUP DATABASE VCDB_2016 TO DISK = N'C:\BACKUP\VCDB_2016_BACKUP.bak' WITH FORMAT;

Step 2: Backup transaction logs

BACKUP LOG VCDB_2016 TO  DISK = N'E:\BACKUP\VCDB_2016_BACKUP.trn'

GO

Both Database and Transaction Logs backup.

Step 3: Copy to node 2 and Restore Database with NORECOVERY

RESTORE DATABASE VCDB_2016 FROM DISK = N'E:\BACKUP\VCDB_2016_BACKUP.bak' WITH NORECOVERY

GO

Step 4: Copy to node 2 and Restore with Transaction Log with NORECOVERY

RESTORE LOG VCDB_2016 FROM DISK = N'E:\BACKUP\VCDB_2016_BACKUP.trn' WITH FILE=1, NORECOVERY

GO

MSSQL: Setting trace on deadlock

-- Monitoring deadlock after SQL Server Started.

DBCC TRACEON(1222,-1)

- to turn off

DBCC TRACEOFF(1222,-1)

-- Monitoring deadlock requires SQL Server to start or restart.

Startup Parameter = -T1222

-- Deadlock information will be written into the error log.

Open the error log and search for "deadlock". If there are deadlock around, it should indicate the query and tables a few lines down below.

MSSQL: Creating auditing in SQL Server.

This blog will demonstrate how to set up Audit in vCenter SQL Server database. 

There are 3 layers of auditing. They are Server-level, Database-level, and Audit-level. In this blog, I am going to cover the Database-level auditing on VCDB database..

An audit event is to use to track and auditing changes made to the database.

SQL Server Database Audit has 3 main parts.

• Server Audit. This to be created at the Master database level.
• Audit Specification. This to be created at the user database level.
• Turn ON both Server Audit and Audit Specification

The audit locations can be directed to either audit file, security event log or application event log. The following example will direct the audit information to an application log.

 Use Master  
 create server audit svr_audit to application_log  
 go  
 alter server audit svr_audit with (state=on)  

New Server Audit will show up under the database instance level.

 use VCDB_2016  
 create database audit specification db_audit_spec  
 for server audit svr_audit add (insert on dbo.vpx_version by public);  
 go  
 alter database audit specification db_audit_spec with (state=on);  

On the database tree, the new audit will show up and by default, it is OFF. This can be turned ON or OFF.

Query the status of the Server Audit status.

 select * from sys.dm_server_audit_status;  

Audit specifications can be located with the following query.

 select * from sys.database_audit_specification_details  

That's it. Auditing is useful if DBA needs to locate suspicious activities in certain objects.

Additional useful reference can be found at this link. 
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc280663(v=sql.105)