How to take Transaction Log backup ? TIP# 68

 

As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN

 

 

Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK ='E:\IndiandotnetTransaction.trn'

When you run above command you will get following screen and your log backup is done.

I hope this will help you.

Thanks & Enjoy!!!

RJ!!

How to take differential backup ? TIP #67

Friends,
We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.
Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.
Differential backup can speed up your recovery process or we can say restore process.
In below example we would understand how to take differential backup
To take differential backup we can run following command
BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\IndiandotnetDB.dif'
WITH DIFFERENTIAL;
GO
Or we can take differential backup by following steps also
Right click the database select database backup option you will get same screen which we explain at the time of full backup.
if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif

Once you click OK button. your differential backup will be completed.
I hope this tip might help you somewhere.
Thanks & Enjoy!!!
RJ!!

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

 

In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail

I hope this tip might help you somewhere.

Thanks !!

Rj!!

Did I take right backup ! how to ensure backup can be restore ? TIP # 65

 

Problem:- We have seen last time how to take backup in tip # 64.   but sometimes it happened we took backup  and we are not able to restore it. It might be corrupted.

Now , Next step thought come in our mind how to ensure we took right backup which can be restore.

Solution:-  To ensure backup is correct. We can check following option as shown in below figure.

in Reliability section  check following option

1) Verify backup when Finished

2) Perform checksum before writing  to media

 

Once this option is check SQL server automatically cross check verify the backup when it is finished and by checking “Verify backup when Finished”

With CheckSum SQL Server cross check before taking backup.

We can also write following query

BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM;

Now to assure more we can write following command and verify whether the database can be restored or not whether the backup set is valid or not.

RESTORE VERIFYONLY
FROM DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM

I hope this might helpful you somewhere.

Thanks!!

RJ!!

How to take compressed backup in SQL Server TIP #64

Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.
We take backup but we don’t have enough space available.
Solutions:  SQL server provided  the best way and gave us solution of compressed backup. Now how to take compressed backup let see step by step here.
Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure

Step 2:-  Once you click the backup option an new screen will appear as shown below

Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.

Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown
once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below

Or else you can use following command
BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;
You will get compressed backup.

I hope this will help you somewhere.
Enjoy!!!
RJ!!

How to attach database MDF files with SQL Server tip #25

 

In this tip we will understand how to attach a database. I took Adventureworks2012 database Mdf file which we need to attach.

Now do follow steps

1) Open the sql server and right click on Database folder of object explorer window.

When you right click you will get a menu as shown below

2) When you click on Attach menu you will get a new window which is shown below

3) Now I click on Add button  and select the Mdf file which you want to attach in my case I added Adventuresworks2012.mdf file which is on D drive. Once I added that file I  got following screen as shown below

4) Now click on OK button below when you click it your database will stored (attached)  in SQL Server as shown in figure

Hope it will help you

Enjoy !!!

How to determine when Last backup taken of a database n SQL Server? TIP #15

Sometime we require to know when we have taken last backup.
So let me share here all the backup, log shipping related detail you can find in the MSDB database which is system database.
SQL server maintain all the backup related detail in the MSDB database table
As Show in the figure

Now we can run the following command to determine the database backups
GO
SELECT Name,
       database_creation_date,
       backup_start_date ,
       backup_finish_date,
       database_name,
      bmf.physical_device_name
FROM MSDB.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf  ON bmf.media_set_id = bs.media_set_id
WHERE database_name='DBayDB'

GO
WHERE DBAY is database name when you run this command you will get all the backup taken for this database.

Enjoy !!!