SQL server Database backup restore Steps

SQL server Database restore sequence Steps


I have seen many people are still unclear about the database restoration sequence. Recently I have answered number of database restoration questions SSC, MSDN... I have got a comment from Anonymous user.

It’s simple task to the experienced DBA’s. However it’s very important to know the database restoration sequence. It’ll help number of situation.

For example,

Your database got damaged for anyone of the reason. (Or) You may get a request from DEV team to restore a database from production to development.

I already wrote an article about Step by step backup/restore using T-SQL. But it’s not fully focus the restoration sequence.

Here I am going to give you the scripts with explanation and examples too. I hope this will help you.

I have scheduled every Sunday full backup @1 AM (1 AM) and every @6 hours differential backup (daily 2 AM onwards) and every @3 hour log backup (2:30 Am onwards).

                          

Just for giving real time example I have moved the log files from current drive (C) to another one drive (D) and i started the SQL server. I have seen the database that shows without the expandable symbol (i.e +).

Now the database “IMPORTANT_DB” log file  has gone on 13 th jun 2011 Monday at 11:40 PM

Now How can i bring the database back to online?

As I already know about my backup policy even though I supposed to check the system tables because where the backup is located and the backup job got succeeded or not.

Here is the script to check the backup details.

SELECT

database_name,[type],backup_finish_date,

(backup_size/1024/1024) size_mb,physical_device_name,

[user_name]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(a.media_set_id=b.media_set_id) WHERE

DATABASE_NAME in('dbadata')

--type ='d'and

--and backup_finish_date>=getdate()-14

ORDER BY backup_finish_date DESC

It's very important to take the tail log backup before going to do anything. Lets try the tail log backup.

BACKUP LOG [dbadata] TO  DISK = N'C:\Backup\dbadata_Taillog.trn' WITH  NO_TRUNCATE

Msg 3447, Level 16, State 1, Line 1

Could not activate or scan all of the log files for database 'dbadata'.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

In my case,I can’t do the tail log backup.

Check the database if it is online or not using below command.

SELECT state_desc,DB_NAME (database_id) FROM SYS.MASTER_FILES WHERE state_desc  <>'ONLINE'

Let me try to access the database

USE DBADATA

Got error I can’t access the database

Msg 945, Level 14, State 2, Line 1

Database 'dbadata' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Let's check the error log

SP_READERRORLOG

Error: 17207, Severity: 16, State: 1.

FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15100) occurred while creating or opening file 'XXX \dbadata.ldf'. Diagnose and correct the operating system error, and retry the operation.

File activation failure. The physical file name "XXX dbadata.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

OK now run the following script and find the backup location.

SELECT

database_name,[type],backup_finish_date,

(backup_size/1024/1024) size_mb,physical_device_name,

[user_name]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(a.media_set_id=b.media_set_id) WHERE

DATABASE_NAME in('dbadata')

--type ='d'and

--and backup_finish_date>=getdate()-14

ORDER BY backup_finish_date DESC

Backup details from the scripts for that particular DB

Backup Device	Type
Y:\Backup\dbadata_full_12_jun_2011_1AM.bak	Full
Y:\Backup\dbadata_Diff_13_jun_2011_2AM.bak	Differential
Y:\Backup\dbadata_Diff_13_jun_2011_8AM.bak   Y:\Backup\dbadata_Diff_13_jun_2011_2PM.bak	Differential Differential
Y:\Backup\dbadata_Diff_13_jun_2011_8PM.bak	Differential
Y:\Backup\dbadata_log_13_jun_2011_230AM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_530AM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_830AM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_1130AM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_230PM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_530PM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_830PM.trn	Log
Y:\Backup\dbadata_log_13_jun_2011_1130PM.trn	Log

Steps to restore the database using available backups.

RESTORE FILELISTONLY FROM DISK ='Y:\Backup\dbadata_full_12_jun_2011_1AM.bak'

First put the database in single user mode by using the below command.

ALTER DATABASE DBAdata 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Here i am going to overwrite (replace) the database. If you want you can leave the damaged database and restore a new one.


Restore the latest full database backup has done on Sunday with norecovery option.

RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_full_12_jun_2011_1AM.bak'

WITH replace,norecovery

Next run the latest differential backup done on Monday at 8 PM with norecovery option.

RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_Diff_13_jun_2011_8PM.bak'

WITH norecovery

Then lastly run the log backup one by one done on Monday after the differential backup.

 

RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_log_13_jun_2011_830PM.trn'

with norecovery

 RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_log_13_jun_2011_1130PM.trn'

WITH recovery

If you missed anyone of the previous log backup then you will get this error. That’s why people are recommended to keep all log backup safely.

Msg 4305, Level 16, State 1, Line 2

The log in this backup set begins at LSN 3873000000044400001, which is too recent to apply to the database. An earlier log backup that includes LSN 3873000000042400001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.


What was given just an example.

Symantec Norton Ghost 15.0 (1 PC) 

SQL server Recovery Models and Backup types

In this post I am going to explain the importance of recovery models and backup types. 

Why I am writing this post ?

Because, when I was teaching about the topic to my PHP friend .He got doubts on bulk logged model and differential backups. I have given some examples and cleared his doubts. I am going to share this with you guys too.

I hope this will help for fresher's and DBA managers because they don't know about the recovery models and backups types. (RPO/RTO data’s at all). This will help for newbies especially an interview time.

Recovery models and backup types are important to plan the recovery point objective (RPO)and recovery time objective (RTO). We can’t differentiate both the recovery models and backup types. Simply it’s a mixture.

Let's see the discussion.

Manager:

We need minimum data loss and down time.

DBA:

Existing system has low disk space and the transaction log backups are failed often. We need more disk space to do this, Sir!

Manager:

I asked about this to management they will give soon until, you will do something with your DBA knowledge.

DBA:

Changing the recovery models full to simple and back to full and shrinking the log file daily.The DBA send an email to his manager. The shrinking is not good practices, Sir!

Manager:

???

(Story ended)


Let’s come to the point.

Recovery Models

SQL server has three types of recovery models.

1. Full

2. Bulk Logged

3. Simple

Full

•  In full recovery model all the database operations are fully logged. This means all the modifications have written fully to the log file.

• The full recovery model is always best one for production servers.

• In full recovery model we can take all kind of backups like full, differential, transaction Log and point in time (PTR) recovery too.

Bulk Logged

• The name itself you can understand the answer. Bulk operations have minimally logged in this mode.This means it has written only minimal roll backing information to the log file not whole (i.e not fully logged).The BCM page contains all the required information. See an example you can understand.

• Bulk logged recovery model is best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

• In bulk logged recovery model we can take all kind of backups like full, differential and transaction Log but, the drawback is point in time (PTR) recovery is not possible, when there is a bulk operations  have done to the transaction log file.

Keep in mind,in full and bulk logged recovery model the log files are grow big until the BACKUP LOG has done.

For more clarity. See an example:

Just assume 5,000 bulk operations have written in 8000 pages.

In full recovery model each 5,000 bulk operations have written to 8000 pages, because all the changes have written fully in the full recovery model.

But when we used bulk logged recovery model, it has written 1000 pages only ,Because SQL server has written the bulk changes to an extent wise not the page wise i.e. minimally logged operation (8 pages = 1 extent).It will helps to improve the overall server performance.

Now you know, Writing 8000 pages are good or 1000 pages.

Note: This bulk logged model is good only for the bulk operations.

Simple

• The name itself you can understand the answer. In simple recovery model is just simple this means SQL server will run the checkpoint every minute and truncate the transaction log file often.

• Most of the time the simple recovery model is good choose for non production servers.

• In simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

 Backup Types

SQL server has number of backup types. Here I am going to explain the main three types.

1. Full

2. Differential

3. Transaction log

I have recommended to reading Paul S Randals's backup survey post.

 

http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-having-the-right-backups.aspx

Full backup

• A full backup backs up the full/whole database. That is backs up all the committed data.

• The full database backup has been done in all the recovery models.

Differential backup

• A differential backup only backs up the changed data/extents that were modified after the full database backup has done.The DCM has tracked all the changed extents.

• The differential backup has been done in all the recovery models.

For more clarity. See an example:

Assume we are scheduled every Sunday full backup @12 am and daily differential backup 10 PM.

Note: The data changes daily 500 MB.

Question:

How much data got back up on Thursday?

The student answers 500 MB.

As I already told, differential database backup "backs up all the data that has changed since the last full database backup".

The answer is,

Monday 500MB + Tuesday 500 MB + Wednesday 500MB + Thursday 500MB= 2000MB

Edit: I added these lines after i had replied the forum question SSC.

So the differential backup on Thursday has all the data. That is (Monday+Tuesday+Wednesday+Thursday). It helps to bring the database online as quickly as possible.

Transaction log backup

• A transaction log backup backs up the transaction log file. That is all the modifications/changes.

• The transaction log backup only possible in full and bulk logged recovery model.

• The transaction log backup is important to minimize data loss and log files size too.

It's  very important to keep the transaction log backup as much as safe,because the restoration needs the sequence of the transaction log backup if, you deleted/missed any one of the backup then you can't recover the whole data.

I have suggested running the log backups often. It will reduce the log file size and helps to minimize your data loss.

 Question:
How to restore the database after a disaster? (OR) What is the sequence to restore the database?


Answer:
Restore a full backup and followed by recent differential backup and all the log backup one by one in a sequence.

For more clarity. See an example:

Assume we are scheduled every Sunday full backup @12 am and daily differential backup 10 PM and an hourly log backup.

The database crashed on Wednesday at 1:30 PM. Then the restoration will be on Sunday full backup+ Wednesday differential backup (Most recent)+ log backup @11+@12+@1.

Believe me guys, Test your backups often and keep at least two copy of the full backup safely, Because the full backup is important to restore the all other backup. This means without a full backup you can't restore the differential and log backups.

Step by step backup/restore using T-SQL

This article mainly for SQL server learners, who is going to become a DBA, in this article I am going to explain the backup and restore using T-SQL.

Why I choose the T-SQL instead of GUI?

Below are reasons,

I have seen number of articles and blogs shows the GUI.

Anyone can use the GUI to restore the database, if they don’t well in SQL.

Again most of the DBA’s are didn’t know the T-SQL to restore the database. I have recommended use the T-SQL to restore the database because it’s the great tool when you are migrating huge number of databases (Not to right click each database to restore) and time saver.

Backup:

I have enjoyed when I was read BrentOzar’s backup best practice articles. I have recommended my readers too.

Let's see the funny story

You have some problem in your mobile phone. You lost all your contact numbers even your girlfriend (wife) number too.Suddenly, she called to you then you asked who are you? …

So, keep do the backup regularly otherwise ready to update your resume - :)

Before going to restore section keep verify your backups regularly (sometimes your backup might be damaged)

Important: Backup is the free D/R solution, when you lost your database.

Step by step restore:

Backup has been taken in D drive.

USE MASTER

GO

BACKUP DATABASE DBA_TEST TO DISK ='D:\BACKUP\DBA_TEST.BAK' with copy_only

Filelistonly returns the logical file names, physical files of data, log locations & file sizes.

USE MASTER

GO

RESTORE FILELISTONLY FROM DISK = 'BACKUP LOCATION'

RESTORE FILELISTONLY FROM DISK ='D:\BACKUP\DBA_TEST.BAK'

Create the same copy\content of the database DBA_new from DBA_test.


Use XP_fixeddrives to find the drive free space.

EXEC XP_FIXEDDRIVES

SELECT * FROM SYS.MASTER_FILES

"sys.master_files" will show the current database files location. All the companies have certain rules like, data file will go one separate physical LUN (RAID level) and log file will go another physical LUN (RAID level).It will help to improve the performance.


So, before going to restore check and ask the same.


Restore database <database name> from disk =’backup location’
With
Move ‘logical name of data file’ to ’data file location’,
Move ‘logical name of log file’ to ’log file location’

RESTORE DATABASE DBA_NEW FROM DISK ='D:\BACKUP\DBA_TEST.BAK'

WITH

MOVE 'DBA' TO 'D:\DATA\DBA.MDF',

MOVE 'DBA_LOG' TO 'E:\LOG\DBA_LOG.LDF'

Look here for more backup options  WITH options (like overwrite,move etc).


After restored the backup you have to change the logical name.Now you can use the DBA_new database.

SELECT * FROM SYS.MASTER_FILES

ALTER DATABASE DBA_NEW MODIFY FILE

(NAME = DBA,NEWNAME = DBA_NEW )

ALTER DATABASE DBA_NEW MODIFY FILE

(NAME = DBA_LOG,NEWNAME = DBA_NEW_LOG )

The database restoration has done in different machine then,  transfers the logins and the passwords  by using this Microsoft script.