ALL In One DataBase Hero Script

This section covers most of the essential scripts of DBA which we can use on a daily basis in the development of any BI project.

In this post, I have explained all the queries with respect to ‘MyDB’ as the database name. So, at your end, you will need to change that according to the name of your database.

Following is the list of SQL queries covered in this post

How to create database.
Script to create schema
Script to create table with constraints
Script to change the recovery model of the database.
Script to take the full backup of database.
Script to take the Differential Database backup.
Script to take the Transaction Log backup that truncates the log
Backup the tail of the log (not normal procedure)
Script to get the backup file properties
Script to delete the backup history of the specific database
Full restore with no recovery (status will be Restoring)
How to Restore transaction log with recovery

Create database -

-- Create databsae MyDB
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyDB.mdf' ,
SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDB_log', FILENAME = N'C:\MyDB_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Script to create schema -

--Script to create schema
USE [MyDB]
GO
CREATE SCHEMA [myMyDB] AUTHORIZATION [dbo]

Script to create table with constraints -

-- Script to create table with constraints
create table myMyDB.Emp
(
EmpID int Primary key identity(100, 1),
EmpName Varchar(20) Constraint UK1 Unique,
DOB datetime Not Null,
JoinDate datetime default getdate(),
Age int Constraint Ck1 Check (Age > 18)
)

Script to change the recovery model of the database -

-- Script to change the recovery model of the databsae
USE [master]
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL
GO

Script to take the full backup of database -

-- Script to take the full backup of database
BACKUP DATABASE [MyDB] TO DISK = N'D:\MyDB.bak'
WITH NOFORMAT, INIT, NAME = N'MyDB-Full Database Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

Script to take the Differential Database backup -

--Script to take the Differential Database backup
BACKUP DATABASE [MyDB] TO DISK = N'D:\MyDB.diff.bak'
WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'MyDB-Diff Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

Script to take the Transaction Log backup that truncates the log

--Script to take the Transaction Log backup that truncates the log
BACKUP LOG [MyDB] TO DISK = N'D:\MyDBTlog.trn'
WITH NOFORMAT, INIT, NAME = N'MyDB-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Backup the tail of the log (not normal procedure)

-- Backup the tail of the log (not normal procedure)
BACKUP LOG [MyDB] TO DISK = N'D:\MyDBLog.tailLog.trn'
WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N'MyDB-Transaction Log Backup',
NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10
GO

Script to get the backup file properties

-- Script to Get the backup file properties
RESTORE FILELISTONLY FROM DISK = 'D:\MyDB.bak'
-- Script to Restore Full Database Backup
RESTORE DATABASE [MyDB1] FROM DISK = N'D:\MyDB.bak'
WITH FILE = 1, MOVE N'MyDB' TO N'D:\MyDBdata.mdf',
MOVE N'MyDB_log' TO N'D:\MyDBlog_1.ldf',
NOUNLOAD, STATS = 10
GO

Script to delete the backup history of the specific database

-- Script to delete the backup history of the specific databsae
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB1'
GO

Full restore with no recovery (status will be Restoring)

-- Full restore with no recovery (status will be Restoring)
RESTORE DATABASE [MyDB1] FROM DISK = N'D:\MyDB.bak'
WITH FILE = 1, MOVE N'MyDB' TO N'D:\MyDBdata.mdf',
MOVE N'MyDB_Log' TO N'D:\MyDBLog_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

Restore transaction log with recovery

-- Restore transaction log with recovery
RESTORE LOG [MyDB1] FROM DISK = N'D:\MyDBLog.trn'
WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
GO

Hope this helps!!