SQL Server Mentalist

Hi Friends,

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

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

Following is list coverage of SQL queries in this post:

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

Following is Script:

-- 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
USE [MyDB]
GO
CREATE SCHEMA [myMyDB] AUTHORIZATION [dbo]

-- 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 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
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
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
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 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
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 databsae
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB1'
GO

-- 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 LOG [MyDB1] FROM DISK = N'D:\MyDBLog.trn'
WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
GO

Hope this helps!!

Quote of the day

Those who are lifting the world upward and onward are those who encourage more than criticize. ……..Elizabeth Harrison

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 8 : Pros and Cons of Money Data Type

· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook

· BISQL # 10 :Top 10 Things For SQL Server Performance Part – I

· BISQL # 12 :SQL Server Technical White Papers,All in one

· BISQL # 13 :Concept of Change Data Capture (CDC) in SQL Server 2008 Part –I

Hope you will like this post on Database Hero Script.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

                  For More information related to BI World visit my Mentalist Blog

                              Link Resource Blog >> Daily Interesting links

                             SQL Server Mentalist >> SQL Learning Blog

                             Business Intelligence Mentalist >> BI World

                                             Connect With me on

              | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                        Copyright © 2011 – 2012 Vishal Pawar