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.