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 -

  1. -- Create databsae MyDB
  2. CREATE DATABASE [MyDB] ON PRIMARY
  3. ( NAME = N'MyDB', FILENAME = N'C:\MyDB.mdf' ,
  4. SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  5. LOG ON
  6. ( NAME = N'MyDB_log', FILENAME = N'C:\MyDB_log.ldf' ,
  7. SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  8. COLLATE SQL_Latin1_General_CP1_CI_AS
  9. GO
Script to create schema -
  1. --Script to create schema
  2. USE [MyDB]
  3. GO
  4. CREATE SCHEMA [myMyDB] AUTHORIZATION [dbo]
Script to create table with constraints -
  1. -- Script to create table with constraints
  2. create table myMyDB.Emp
  3. (
  4. EmpID int Primary key identity(100, 1),
  5. EmpName Varchar(20) Constraint UK1 Unique,
  6. DOB datetime Not Null,
  7. JoinDate datetime default getdate(),
  8. Age int Constraint Ck1 Check (Age > 18)
  9. )

Script to change the recovery model of the database -

  1. -- Script to change the recovery model of the databsae
  2. USE [master]
  3. GO
  4. ALTER DATABASE [MyDB] SET RECOVERY FULL WITH NO_WAIT
  5. GO
  6. ALTER DATABASE [MyDB] SET RECOVERY FULL
  7. GO
Script to take the full backup of database -
  1. -- Script to take the full backup of database
  2. BACKUP DATABASE [MyDB] TO DISK = N'D:\MyDB.bak'
  3. WITH NOFORMAT, INIT, NAME = N'MyDB-Full Database Backup',
  4. NOREWIND, SKIP, NOUNLOAD, STATS = 10
  5. GO