Thursday, October 23, 2014

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

checkDB 

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;

Backup_CheckSUm_option

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

Restore_Verifyonly

I hope this might helpful you somewhere.

Thanks!!

RJ!!

Tuesday, October 21, 2014

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  a best way and give 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

Backup_step1

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

Backup_step2

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.

Backup_step3

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

backup_step5

Or else you can use following command

BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;

You will get compressed backup.

backup

I hope this will help you somewhere.

Enjoy!!!

RJ!!