SQL server Database backup restores sequence Steps

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 transactional log files from current drive (C) to another 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.

https://sqlserverblogforum.com/alwayson/how-to-solve-the-lsn-mismatch-in-sql-server/

 

What was given just an example. Hope this post clears the backup restoration sequence. https://www.sqlservercentral.com/forums/topic/lsn-is-broken-and-im-unsure-why

I have added this link and following statement which is very clear.

Full or Diff backups do not affect the log chain – they are independent from backups.  Full backups set the differential base LSN – and differentials are tied to the previous full backup based on the differential base LSN.

The restore path from a full backup can be either:

1) Full Backup and all transaction log backups from the full through the point in time to be recovered
2) Full Backup – Differential Backup – and all transaction log backups from the differential through the point in time

For #2 – it doesn’t matter which differential backup is restored, what is important is that you have all transaction log backups from that point in time through the point in time being recovered.

3) Full Backup with all transaction log backups from the full through the point in time to be recovered. (I mean without differential aslo)