•  
  • Backup/Restore (3)

SQL server Database backup restores Steps

by Muthukkumaran kaliyamoorthy Published on: July 6, 2011
Comments: 15 Comments
Tags: ,
Categories:Backup/Restore, DBA

SQL server Database restores sequence Steps

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

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

For example,

Your database got damaged in any one of the reasons. (Or) You may get a request from the DEV team to restore a database from production to development.

I have already written 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 hour 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 by using below command.

 

SELECT state_desc,DB_NAME(database_id) FROMSYS.MASTER_FILESWHERE 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 SQLServer errorlog for details.
Let’s check the error log

EXEC SP_READERRORLOG

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

FileMgr::StartLogFiles: Operating system error 2(failed to retrieve textfor this error. Reason: 15100) occurred while creating or opening file’XXX \dbadata.ldf’. Diagnose and correct the operating system error, and retry the operation.
Fileactivation 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, nocheckpoint occurred to the database, or the database was read-only. This error could occur if the transactionlogfile 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 of 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.

RESTOREDATABASE DBAdata FROMDISK ='Y:\Backup\dbadata_Diff_13_jun_2011_8PM.bak'
WITHnorecovery

 

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 any one of the previous log backups then you will get this error. That’s why people are recommended to keep all log backups safely.

Msg 4305, Level 16, State 1, Line 2

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

Msg 3013, Level 16, State 1, Line 2

RESTOREDATABASE is terminating abnormally.

What was given just an example. Hope this post clears the backup restoration sequence.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

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 recovery 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 Freshers and DBA managers.

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

Let’s see the discussion.

Manager:

We need minimum data loss and downtime.

DBA:

The existing system has low disk space and the transaction log backups are often failing. 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 best practices, Sir!

Manager:

???

 

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 like all redo information. This means all the modifications have written fully to the log file.
  • The full recovery model is always the best one for production servers.
  • In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Full recovery mode, the transaction log file will grow like anything(big), until we do a log backup. In other words, The log file will not be reusable/clear until the log backup taken.

Bulk Logged

  • The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.
  • Bulk logged recovery model is the 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 kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations  have done with the transaction log file.

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

For more clarity. See an example: (This is not a concept just an example)

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

In full recovery model each 5,000 bulk operations have written in 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 help to improve the overall server performance.

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

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

 

Simple

  • The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.
  • Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If you’re planning to take the log backup then don’t put in simple)
  • In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

Now you could know the answer for the following questions.

What is the use of Full, Bulk logged and Simple recovery model.

Which one is best for your case?

 

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 recommend to read 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 backs up all the 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 file 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/missing any one of the backups then you can’t recover the whole data.

It’s good to take a log backup 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 recent full backup and followed by a recent differential backup and all the log backup one by one, that has done after a recent differential backup.

For more clarity. See an example:

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

The database crashed on Wednesday at 11PM. Then the restoration will be on Sunday full backup (@12AM)+ Wednesday differential backup (Most recent @8PM) + log backup @9+@10+@11 PM.

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

Note: The example has given is just for more understanding. Take a look at MSDN for more information.

 

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

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 a 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 DBAs hasn’t used the command and they don’t know the T-SQL syntax to restore the database. I recommend using the T-SQL to restore the database because it’s the greatest tool when you are migrating a huge number of databases (Not to right click each database to restore) and time saver.

Backup:

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

Let’s see the funny story

You have some problem with 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 doing 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 the log file will go another physical LUN (RAID level). It will help to improve the performance.

So, before going to restore checks 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'


You can also overwrite the database by using REPLACE option if you don’t need the existing database.
For more : Look the restoration options  WITH options (like overwrite(REPLACE),move etc).
RESTORE DATABASE DBA_TEST FROM DISK ='D:\BACKUP\DBA_TEST.BAK' WITH REPLACE
After complete a restoration its good to change the logical name. But It will not cause anything if you aren’t changing it.
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.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Sunday, June 25, 2017