ALL In One DataBase Hero Script - Set One

Hi Friends,

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.   
  3. CREATE DATABASE [MyDB] ON PRIMARY  
  4. NAME = N'MyDB', FILENAME = N'C:\MyDB.mdf' ,  
  5.  SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  6. LOG ON  
  7. NAME = N'MyDB_log', FILENAME = N'C:\MyDB_log.ldf' ,   
  8. SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
  9. COLLATE SQL_Latin1_General_CP1_CI_AS  
  10. 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  
Script to take the Differential Database backup -
  1. --Script to take the Differential Database backup  
  2. BACKUP DATABASE [MyDB] TO DISK = N'D:\MyDB.diff.bak'  
  3. WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'MyDB-Diff Backup',  
  4. NOREWIND, SKIP, NOUNLOAD, STATS = 10  
  5. GO  
Script to take the Transaction Log backup that truncates the log
  1. --Script to take the Transaction Log backup that truncates the log  
  2. BACKUP LOG [MyDB] TO DISK = N'D:\MyDBTlog.trn'  
  3. WITH NOFORMAT, INIT, NAME = N'MyDB-Transaction Log Backup',  
  4. SKIP, NOREWIND, NOUNLOAD, STATS = 10  
  5. GO  
Backup the tail of the log (not normal procedure)
  1. -- Backup the tail of the log (not normal procedure)  
  2. BACKUP LOG [MyDB] TO DISK = N'D:\MyDBLog.tailLog.trn'  
  3. WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N'MyDB-Transaction Log Backup',  
  4. NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10  
  5. GO  
Script to get the backup file properties
  1. -- Script to Get the backup file properties    
  2. RESTORE FILELISTONLY FROM DISK = 'D:\MyDB.bak'    
  3. -- Script to Restore Full Database Backup  
  4. RESTORE DATABASE [MyDB1] FROM DISK = N'D:\MyDB.bak'  
  5. WITH FILE = 1, MOVE N'MyDB' TO N'D:\MyDBdata.mdf',  
  6. MOVE N'MyDB_log' TO N'D:\MyDBlog_1.ldf',  
  7. NOUNLOAD, STATS = 10  
  8. GO  
Script to delete the backup history of the specific database
  1. -- Script to delete the backup history of the specific databsae  
  2. EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB1'  
  3. GO  
Full restore with no recovery (status will be Restoring)
  1. -- Full restore with no recovery (status will be Restoring)  
  2. RESTORE DATABASE [MyDB1] FROM DISK = N'D:\MyDB.bak'  
  3. WITH FILE = 1, MOVE N'MyDB' TO N'D:\MyDBdata.mdf',  
  4. MOVE N'MyDB_Log' TO N'D:\MyDBLog_1.ldf',  
  5. NORECOVERY, NOUNLOAD, STATS = 10  
  6. GO  
Restore transaction log with recovery
  1. -- Restore transaction log with recovery  
  2. RESTORE LOG [MyDB1] FROM DISK = N'D:\MyDBLog.trn'  
  3. WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10  
  4. GO  
Hope this helps!!

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now