YOUR's SQL SERVER: The Complete BACKUP & RESTORE

Backup and Restore

Check Point

The checkpoint process also writes dirty pages to disk, but it has no interest in keeping available buffers or memory pressure. The job of the checkpoint is to keep the potential time needed to recover the database to a small value.
Recovery is the process that the database must go through when it starts up to ensure transactional consistency. In part of the recovery process SQL uses the transaction log to work out what transactions had committed but may not have had their changes written to disk at the time of the shut down and it replays those transactions. That replay (roll forward) can be extensive on larger, busy databases. To reduce the amount of work required of this roll forward, the checkpoint process runs at regular intervals and writes all dirty data pages to disk and writes an entry into the log indicating that all changes before that point are now on disk.
One important thing to note here is that all dirty data pages are written to disk by the checkpoint, not just pages corresponding to committed transactions. Of course, this means that changes may be written to disk and later rolled back, either explicitly or because the transaction did not complete. This is not a concern. When the rollback happens, the data pages will again be modified in memory and later written to disk. If the SQL service restarts after the pages were written to disk but before the transaction commits, another part of the recovery process will undo those data changes.
The frequency at which the checkpoint process runs is controlled by a server setting: “recovery interval (min)”. This setting specifies how long a recovery should take. The higher this is set to, the less often checkpoint runs. The flip side is that the higher the recovery interval is, the more work checkpoint has to do each time it runs, hence if checkpoint is causing IO contention it may be better to reduce the recovery interval, rather than increasing it

Lazy writer

The job of the lazy writer is to find dirty pages in the buffer pool and write them out to disk and drop those pages from cache. It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries. The pages that it writes out are ‘older’ pages, ones that haven’t been used for a while.
If there’s lots of available buffers, the lazy writer will be doing relatively little work and the number of pages written to disk will be quite low. If the lazy writer’s consistently writing lots of data, it may indicate that there’s a memory bottleneck.

Differences between Checkpoint and Lazy writer

It’s important to understand how and when SQL makes changes in memory and how and when those changes are written to the data file. It has a big impact on memory use and IO patterns.
Data modification queries (insert, update, delete) always make changes to data pages in memory. In fact, queries in general only operate on in-memory data pages. When a query makes a change to an in-memory page, that page is marked as ‘dirty’, indicating that there are changes on that page that have to be written to disk before the page can be discarded from memory. That writing is the job of two system processes, the lazy writer and the checkpoint.

Checkpoint	Lazy writer
1. Flush dirty pages to Disk	1. Flush dirty pages to Disk.
2. Flush only Data pages to disk	2. Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
3. Default, Occurs approximately for every 1 minute	3. Occurs depending upon memory pressure and resource availability
4. Can be managed with sp_config - recovery interval option	4. It is lazy, SQL Server manages by its own.
5. Does not check the memory pressure	5. Monitor the memory pressure and try maintain the available free memory.
6. Crash recovery process will be fast to read log as data file is updated.	6. No role in recovery
7. Occurs for any DDL statement	7. Occurs per requirement
8. Occurs before Backup/Detach command	8. Occurs per requirement
9. Depends upon the configuration setting, we can control.	9. Works on Least recent used pages and removed unused plans first, no user control.
10. For simple recovery it flushes the t.log file after 70% full.	10. No effect on recovery model.
11. Can manually /Forcefully run command “Checkpoint”	11. No command for Lazy Writer
12. Very Less performance impact	12. No performance impact

Recovery Models

1. FULL

2. BULK LOGGED

3. SIMPLE

Recovery Model

Ø It is a database level property.

The following features depends on the recovery model

§ What is recorded in T.Log file

§ Point in time recovery is possible or not?

§ T.Log backups are possible or not?

§ When T.Log is truncated?

§ Log shipping and mirroring etc.

Ø SQL Server supports 3 recovery models

§ FULL

§ BULK LOGGED

§ SIMPLE

Ø We can set recovery model as follows

USE MASTER

ALTER Database <db Name> SET RECOVERY FULL/BULK_LOGGED/SIMPLE

Example

USE MASTER

ALTER DATABASE Test SET RECOVERY FULL

Ø To find a recovery model of the database

§ Select * from sys.databases

§ select DATABASEPROPERTYEX ('Test’, ‘Recovery')

1. Full Recovery Model

§ Every Transaction is recorded in T.Log file.

§ Log file grow very fastly.

§ To control log file growth we have to configure regular Log Backups.

§ We can recover data. (Point in time recovery)

§ We can configure all types of backups.

§ We can configure Log shipping, DB Mirroring.

Scenario

Ø We have configured

§ Every Sunday 9:00 PM FULL backup.

§ Every day 9:00 PM differential backup.

§ And every 1 hr. T.Log backups.

Ø Database was failed at 9:30 AM on Friday, and then what are the db recovery steps?

Solution

§ Take 9 to 9:30 duration transactions into backup i.e. taking tail log backup.

§ Restore last Sunday FULL backup.

§ Restore differential backup generated on Thursday.

§ Restore all Log backups up to 9:00 AM (12 Log backups)

§ Restore tail log backup with recovery.

2. Simple Recovery Model

§ Every transaction is recorded in T.Log file.

§ Log file is truncated once checkpoint occurs.

§ We may not recover up to point of failure.

§ No Log backups are allowed. Only Full and Differential backups are allowed.

FAQ

Ø My db recovery model is SIMPLE. My T.Log file is growing fastly. What may be the scenarios?

Solution:

§ If there are concurrent uncommitted transactions.

§ If the db is configured for transactional replication and the transactions made towards replication are not delivered from Log file into distributor.

3. Bulk Logged

§ Every transaction is recorded except bulk operations.

§ Only Log shipping is possible.

§ Log file is truncated at the time of log backup.

Database Backups

§ It is the process of taking data, meta data and services present in the database into O/S file.

§ Backup can be generated in two types of files

v .BAK (Can consists of any type of backup)

v .TRN (Transaction Log backup)

§ Multiple backups also we can write in a single file which maintains backups with position no.

Ø Advantages

v We can provide security for data.

v We copy\move database from one server to another.

v We can implement standby solutions with Log shipping.

v To recover data up to point of failure i.e., to avoid data loss.

Who can take backup?

Ø Only members of the following role can take backup

§ SysAdmin

v Can take backup of any database

§ db_owner

v Can take backup of respective db

§ db_backupoperator

v Can take backup of respective db

What backup contains?

§ Every backup file consists of two parts

v Backup Header

v Backup Data

§ Header consists of details of backup like

v Type of backup

v Date generated

v User name

v Host name

v Version No.

v Size etc.,

§ Second Part consists of the following details

v Schema and File structure

v Data

v Writes all extents which consists of at least one page with data.

Types of backups

§ Full Backup

§ Differential Backups

§ Transaction Log

§ File or Filegroup backup

§ Mirrored Backups

§ Striped Backups

§ Copy-Only Backups

§ Tail Log backup

§ Compression backups

1. Full Backup

§ Complete database is backed up.

§ It takes both data and T.Log files into backup.

§ Takes long time, depends on size of database.

Syntax

USE MASTER

BACKUPDATABASE <dbName> to Disk='............... .bak'

Example

v Take a full backup of “AdventureWorks’’ database into

d:\SQL_backups folder

Steps

§ Create the folder d:\SQL_backups

§ Grant read write permission to service account on the above folder.

§ Take new query

USE MASTER

BACKUPDATABASE AdventureWorks to disk='d:\SQL_backups\Adv_Full.bak'

with stats=10

§ To display backup header

USE MASTER

Restore headeronly from disk='d:\SQL_Backups\Adv_Full.bak'

§ To display files taken into backup

v Restore filelistonly from disk='d:\SQL_Backups\Adv_Full.bak'

§ To verify backups

v Restore verifyonly from disk='d:\SQL_Backups\Adv_Full.bak'

FAQ

§ How can we imagine or calculate size of backup?

Solution

§ backup size = no. of used extents in all data files * 64 + backup header (KB)

(DEFAULT SIZE OF PAGE HEADER IS 96 BYTE)

Example

§ BACKUP SIZE=599*64+96/1024=37.53125 (APPROX 37.53 MB)

Note

§ To check the used extents

USE AdventureWorks

DBCC showfilestats

FAQ

§ What is your backup strategy?

Answer

§ Backup strategy refers to types of backups and frequency which we have scheduled.

§ It depends on

v Size of database

v Recovery model

v Transaction Rate

v Availability (24/7)

Scenario 1

§ My database size is 6GB and daily 500 transactions.

Suggestible strategy

§ Daily Full backups.

§ Every 4 hrs. Differential backups.

§ For every 1hr Tail Log backup.

Scenario 2

§ My Database size is 800GB. Daily 50000 transactions.

Suggestible strategy

§ Weekly FULL backup.

§ Daily Differential backup, Every 1hr or ½ an hour T.Log backups.

2. Differential Backups

§ Changes made after full backup can be taken into file with differential backup.

§ It is generated fastly as compared with full backup.

§ It reduces time of recovery in case of database failure.

Syntax

USE MASTER

BACKUP Database <dbName> to Disk='..........' with Differential

Steps

§ Go to AdventureWorks database.

§ Perform some changes in any table.

§ Take new query

USE AdventureWorks

BACKUP Database Test to disk='d:\SQL_backups\Adv_Full.bak' with differential

§ View backup header

v RESTORE HEADERONLY FROM DISK='d:\backups\Adv_Full.bak'

§ Getting backups information

v We can get backup details from msdb database.

v In MSDB we have 5 tables related to backups.

· backupset

o Complete backup details

o Types

D - Full

I - Differential

F - File/file group

L - T.Log backup

· backupmediafamily

o Consists of file path

· backupmediaset

o Compression and mirrors details

· backupfile

o File names which are taken into backup.

· backupfilegroup

o Filegroup names which are taken into backup.

Note

Ø To check recent backups of all databases

SELECT T1.Name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101), 'Not Yet Taken') AS LastBackUpTaken

FROM master.sys.databases T1 LEFT OUTER JOIN

msdb.dbo.backupset T2

ON T2.database_name = T1.name

GROUP BY T1.Name

ORDER BY T1.name

Ø To get complete backups information of a particular database

SELECT S.database_name, m.physical_device_name,

cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,

CAST (DATEDIFF(second,s.backup_start_date, s.backup_finish_date) AS VARCHAR(4))+' '+'Seconds'

TimeTaken, s.backup_start_date,

CASE S.[type]

WHEN 'D' THEN 'FULL'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END as BackupType,

s.server_name, s.recovery_model

FROM msdb.dbo.backupset s

inner join msdb.dbo.backupmediafamily m

ON s.media_set_id = m.media_set_id

WHERE s.database_name = 'AdventureWorks'

ORDER BY database_name, backup_start_date, backup_finish_date

3. Transaction Log Backups

§ It takes backup of T.Log file only.

§ It takes all committed transactions from the last full or differential or log backup.

§ These are incremental backups.

§ It is possible only in FULL and BULK LOGGED recovery models only.

Advantages

§ To truncate the T.Log file periodically.

§ To get transactions up to point of failure.

§ To implement T.Log shipping

Syntax

USE MASTER

BACKUP LOG <dbName> TO DISK='...........'

Example

§ Go to Test database

§ Perform some changes in any one table.

§ Take new query

USE MASTER

BACKUP LOG AdventureWorks TO DISK='d:\Backups\Adv_Full.bak'

Scenario

§ My database recovery model was SIMPLE and I have taken FULL backup. I was unable to take T.Log backup hence I have changed recovery model to FULL. Can I take T.Log backup now?

Solution

v Not allowed. Full backup of SIMPLE recovery model cannot work as base for T.Log backups.

Once again we have to take FULL backup then T.Log backup.

FAQ

Ø My Backup was failed and now what may be the possible scenarios?

· No disk space.

· Server was busy

· msdb was offline

· Agent service was stopped.

· Network problem.

4. File or Filegroup backups

· To take backup of a file or entire filegroup.

· We can take at a time only one filegroup backup.

· It is required for very large databases where Full backup takes long time.

Syntax

USE MASTER

BACKUP DATABASE <dbName> filegroup= 'filegroupName' to Disk='..............'

Example

USE MASTER

BACKUP DATABASE Test filegroup = 'primary' to Disk='d:\backups\Test_Primary_FG.bak'

Example

v Take a backup of PRIMARY filegroup of Test database.

USE MASTER

BACKUP DATABASE Test filegroup = ‘primary’ to disk=’d:\backups\Test_Primary_FG.bak’

Example

Ø Create a backup device with the name Test_BD and take full backup into the device.

Step 1

§ Creating backup device

USE MASTER

sp_addumpdevice @devtype = 'disk', @logicalname = 'Test_BD',

@physicalname =‘d: \backups\Test_BD.bak'

Step 2

§ Taking full backup of Sales db into backup device

BACKUP DATABASE Sales TO Test_BD

Copy Only Backups

§ A normal backup effects the backup plan and recovery steps.

§ If we want to take backup without effecting existing plan we can use Copy-Only backups.

· SS supports

o Copy-only Full backup

o Copy-Only T.Log backup

Syntax

§ BACKUP DATABASE/log <dbName> to disk='.........’ with copy_only

Example

§ BACKUP DATABASE Test to Disk='d:\backups\Test_copyOnly.bak' with copy_only

Striped Backups

§ If the size of backup is large where there is no required disk space in any drive we can split backups into multiple files in different disks. These backups are called striped backups.

§ If the server consists of multiple CPUs then it provides better performance.

Example

BACKUP DATABASE [AdventureWorks]

DISK = 'D:\backups\Adv1.bak',

DISK = 'E:\backups\Adv2.bak'

WITH NOFORMAT, STATS = 10

Note

· WITH FORMAT means INDEPENDENT & NO FORMAT means DEPENDENT

Mirrored Backups

§ In order to reduce restoration problems we can take backup of same database in multiple locations so that if any one media set is get damaged so we can restore from other.

§ Max we can write 4 mirrors for same media set.

Syntax:

BACKUP DATABASE <dbName> to Disk=’……………..’

mirror to Disk=’…………………..’

with format

Ø Create Mirrored Database Backup

Example

Ø Create AdventureWorks full backup into two different files

§ Let’s go through the below example to understand in detail how to use the Mirrored Backup Set feature. In these examples I am creating three backup files and writing each type of backup to the same file. This does not need to be done this way, but it just keeps it simpler by creating one file per mirror backup set instead of having a lot of files. So based on this the backup files will each have three backups as follows:

File 1 = full backup
File 2 = differential backup
File 3 = transaction log backup

The first step is to create the full backup. Here we are creating the full backup with two mirror copies.

-- Take a Full Backup of Products Database Using WITH FORMAT clause

BACKUP DATABASE Products

TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'

MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'

MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

WITH FORMAT

Next we create the differential backup again with two mirror copies and this is written to the same backup files create above.

-- Take a Differential Backup of Products Database

BACKUP DATABASE Products

TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'

MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'

MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

WITH DIFFERENTIAL

Lastly, we create the transaction log backup with two mirror copies and this is written to the same backup files create above.

-- Take Transactional Log Backup of Products Database

BACKUP LOG Products

TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'

MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'

MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

We can then run the RESTORE VERIFYONLY command to see if the backup files are readable.

-- Verify all the Mirrored database backup sets

RESTORE VERIFYONLY

FROM DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'

RESTORE VERIFYONLY

FROM DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'

RESTORE VERIFYONLY

FROM DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

To show that you can mix and match the backups to use for the restore, I will restore the backups as follows:

Full Backup - from Products_Mirror_Set_2
Differential Backup - from Products_Mirror_Set_1
Transaction Backup - from Products_Mirror_Set_3

-- Restore Full Backup of Products database using NORECOVERY Mode,

-- the backup file used is from Mirror Set 2

RESTORE DATABASE [Products]

FROM DISK = N'D:\DBBackup\Products_Mirror_Set_2.bak'

WITH FILE = 1, NORECOVERY, STATS = 10

-- Restore Differential Backup of Products database using NORECOVERY Mode,

-- the backup file used is from Mirror Set 1

RESTORE DATABASE [Products]

FROM DISK = N'C:\DBBackup\Products_Mirror_Set_1.bak'

WITH FILE = 2, NORECOVERY, STATS = 10

-- Restore Transactional Log Backup of Products database using RECOVERY Mode,

-- the backup file used is from Mirror Set 3

RESTORE LOG [Products]

FROM DISK = N'E:\DBBackup\Products_Mirror_Set_3.bak'

WITH FILE = 3, RECOVERY, STATS = 10

As you can see you can create mirrored backup copies and then use the files from any set in order to complete the restore.

Error: 3271

· There is no sufficient disk space for backup

Restores

§ It is the process of creating database from existing backup file.

§ It creates database with all the transactions, captured in the backup.

§ To restore database the user must have either

o sysadmin (or)

o dbcreater role

§ By default once we try to restore database the data file and T.Log files of database are created in similar path of source server.

§ We can get complete restoration details in the following MSDB tables.

o Restorehistory

o RestoreFile

o Restorefilegroup

Syntax

USE MASTER

Restore Database/Log <dbName> from Disk='.............'

[with replace/norecovery/recovery/standby/Move]

Recovery States

v SQL Server supports 3 recovery states

· WITH RECOVERY

· WITH NORECOVERY

· WITH STANDBY

1. WITH RECOVERY

v It is default recovery model.

v Database becomes operational.

v We cannot apply further backups.

2. WITH NORECOVERY

v Database becomes non-operational.

v We can apply further backups.

v In mirroring we have to restore database in mirror server WITH NORECOVERY.

3. WITH STANDBY

v Database is restored in read only mode.

v It allows further backups to be restored.

v In Log shipping in standby server we will maintain database in read-only mode.

Exercise

· Take Full backup of Test database into

d:\backups\Test_full.bak

· Take Log backup of Test database into

d:\backups\Test_Log.trn

· Connect another instance

· Restore the database in that instance with same name

Steps

I. Go to first server take two backups

USE MASTER

BACKUP DATABASE Test to Disk='d:\backups\Test_full.bak'

BACKUP Log Test to Disk='d:\backups\Test_tran.trn'

II. Go to Second server

A. Create two folders

d:\TestData and

e:\TestLog and Grant Read-Write permissions to second server service account

B. R.C on Databases --> Restore Database --> To Database : Test

From Device --> Click on browse button

C. Add

D. Select Test_Full.bak file

E. OK

F. Under Restores --> Select Checkbox

G. Go to Options

H. Under Restore As

d:\TestData\Test.mdf

e:\TestLog\Test_log.ldf

I. Under Recovery State --> Select "Restore with norecovery"

J. OK

Restoring T.Log backup

A. R.C on Test database--> Tasks --> Restore --> Transaction Log

B. Select option --> From File or Tape

C. Click on browse button

D. Add --> Select Log backup --> OK

E. OK

Steps

v Go to first server take backups

USE MASTER

BACKUP DATABASE SQLDEV to Disk='d:\backups\SQLDEV_full.bak'

BACKUP Log SQLDEV to Disk='d:\backups\SQLDEV_tran.trn'

v Go to Second server create two folders and grant read write permissions to service account of second server.

d:\Test_Data

e:\Test_Log

v Take new query in second instance and run the following restore command

Check logical and physical file names

§ restore filelistonly from disk='d:\backups\SQLDEV_full.bak'

Restoring full backup with no recovery

§ Restore database SQLDEV

from Disk='d:\backups\SQLDEV_full.bak'

with

move 'SQLDEV' to 'd:\Test_data\SQLDEV.mdf',

move 'SQLDEV_log' to 'e:\Test_log\SQLDEV_log.ldf',

norecovery

Restoring T.Log backup with recovery

§ RESTORE Log SQLDEV

from Disk='d:\backups\SQLDEV_tran.trn'

Taking Tail Log Backup

· A Tail log backup is the last Transaction log backup that you make prior to restoring a database.

· If your db crashes for whatever reason, you have to backup your transaction log so that you can do point in time recovery.

· It is possible only

a) If the recovery model of database is other than SIMPLE.

b) If the transaction log file was not damaged physically.

c) If there is FULL backup exists.

Steps

· Taking tail log backup of SQLDB database.

1. Take Full backup of database.

USE MASTER

BACKUP DATABASE SQLDB to Disk =‘d:\backups\SQLDB.bak'

2. Insert some data into any one table of SQLDB database.

3. Stop server

4. Go to database files folder. Here

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

5. Move the Data File to another location.

6. Restart the instance.

7. Now database goes into suspect mode.

8. Stop the server.

9. Copy Transaction Log file into different location. For Ex: - d:\Log folder.

10. Start server.

11. Create a new database with same name i.e., SQLDB. By deleting existing one.

12. Copy the old log file and over write the newly created log file of the database by stopping server.

13. Start server again.

14. Now take the tail log backup

BACKUP Log SQLDB to Disk='d:\backups\sqldb_tail.bak' with no_truncate

15. Now restore the FULL backup by using WITH NORECOVERY.

RESTORE DATABASE SQLDB from Disk='d:\backups\SQLDB.bak' with norecovery

16. Restore the Tail Log backup WITH Recovery

RESTORE Log SQLDB from Disk='d:\backups\sqldb_tail.bak'

17. Observe the changes made after FULL backup.

Restoring database from striped backups

· Striping backup involves storing backup data into multiple files.

· While restoring we have to mention all the media families.

Syntax

RESTORE DATABASE <dbName>

from Disk='.............file1.....',

Disk='..................file2.....'

Example

RESTORE DATABASE [AdventureWorks] FROM

DISK = 'D:\Backups\Adv1.bak',

DISK = 'D:\Backups\Adv2.bak'

Using STOPAT Option

· To Restore Database up to required Date and Time.

· It is possible only with T.Log backups.

Syntax

RESTORE LOG <dbName> FROM DISK='................'

WITH STOPAT='DATE AND TIME'

Example

RESTORE LOG [Test] FROM

DISK = 'D:\Backups\Test_BD.bak'

WITH FILE = 1, STOPAT= '20120909 12:30:59'

Backup Compression

· SQL Server 2008 supports to compress the backups.

· We can compress up to 75%.

· It provides better performance as disk I/O reduces, but uses more CPU resources.

· We can implement this by 2 methods

Method 1

· By setting server level property

backup compression default

Example

sp_configure 'backup compression default', 1

reconfigure

Method 2

· At the time of taking backup

USE MASTER

BACKUP DATABASE <dbName> to Disk='...........'

with compression/no_compression

Example

· Take AdventureWorks database backup into d:\backups and compress it.

Steps

§ Enable compression option

sp_configure 'backup compression default', 1

reconfigure

§ Taking full backup

USE MASTER

BACKUP DATABASE AdventureWorks to Disk='d:\backups\Adv_Comp.bak'

§ Verify the backup file size

FAQ

· I have taken Full and T.Log backup in same file. Now I am restoring the database using the following command. Both the backups are restored or not?

o Restore database <dbName> from disk='..............'

Solution

§ Only Full backup is restored.

Data Compression

· To make fixed type column values into variable type data.

· Depends on data size requirement SS adjust the memory usage with compression.

· SS supports 2 types of compressions

o Page

o Row

Example

· Compress “SalesOrderDetail” Table of Sales schema present in AdventureWorks db

USE [AdventureWorks]

ALTER TABLE [Sales].[SalesOrderDetail]

REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = ROW)

Emp_Id int	ename varchar (40)	sal money
9999	Suresh	25000

Resource Database

· The resource database (mssqlsystemresource) database is the new system databases available in SQL Server 2005 and above.

Why Resource database is important?

· The Resource database appears to be a critical database as the SQL Server service is now dependent on this.

How to Backup Resource Database?

· In order to take the backup of Resource Database we need to perform a file based or a disk based backup of the mssqlsystemresource.mdf and mssqlsystemresource.ldf files, by copy paste method. There is no Full backup or online backup for this database.

How to Restore Resource database?

· Use Copy Paste Method only, overwrite the existing files from the backup location.