SQL Database Backup - Restore Using SQL Query

Here we have some script to take database backup and restore quickly using SQL queries.
We can bypass the wizard by using the following TSQL scripts.

In below our database name is Test_DB, for which we will take backup and restore using TSQL scripts.

Backup Database

USE [master];
GO

/*
@date - Will Create a Date Timestamp to attach with database Backup file name for our future reference (this is an optional, we can set anything instead of Date Timestamp)
@disk1 : This is a Database backup File Location
*/

DECLARE @date NVARCHAR(19) = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), ':', ''),'-',''),' ',''),
        @disk1 NVARCHAR(256) = N'';

SET @disk1 = N'C:\DataBase Backup\Test_DB_' + @date + N'.bak';

BACKUP DATABASE [Test_DB]
TO  DISK = @disk1
WITH NOFORMAT,
     NOINIT,
     NAME = N'Test_DB-Full Database Backup',
     SKIP,
     NOREWIND;

Restore Database

USE [master];
GO

--Main DB
RESTORE DATABASE [EMS_Amajuba_Niren]
FROM DISK = N'C:\DataBase Backup\Test_DB_20190511091135.bak' --do a find and replace on the date string
WITH FILE = 1,
     --REPLACE, /* On second restore, add this to overwrite the previous backup */
     MOVE N'Test_DB'
     TO N'C:\Data\Test_DB.mdf',
     MOVE N'Test_DB_Log'
     TO N'C:\Log\Test_DB.ldf',
     STATS = 5;
GO

Some times we have error while database restore that database on which we are going to restore is still in use by some one, we can use below script to check whoever is using the database or if any active query window for this database

SELECT spid,
       hostname,
       program_name,
       hostprocess,
       loginame,
       login_time
FROM sys.sysprocesses
WHERE dbid IN
      (
          SELECT database_id FROM sys.databases WHERE name = 'EMS_Amajuba_niren'
      );