Backup and Restore Resource Database in SQL Server

Introduction

Microsoft SQL Server 2005 introduced a new system database namely Resource database. It’s a read-only system database which is hidden from users and in this article we will discuss how to backup and restore Resource Database in SQL Server. DBA should backup Resource Database along with other System Databases in SQL Server as part of Disaster Recovery and most importantly document the location of each and every system and user databases.

What is a Resource Database?

Resource database is a read-only system database which is hidden from users. System objects such as sys.objects are physically stored in Resource Database which appears logically in the SYS schema of each database. However, resource database will only store system objects and you cannot store user data or metadata. Resource database consists of two files namely mssqlsystemresource.mdf and mssqlsystemresource.ldf. Most importantly the ID of resource database is always 32767. The ID value of resource database has remained same across all versions of SQL Server 2005 to SQL Server 2014. For more information, see Resources Database in SQL Server.

What is the Importance of Resource Database?

Resource Database makes upgrading SQL Server to a New Version an easier and a faster procedure. In the previous versions of SQL Server, upgrading to a new version required dropping and creating system objects. However, since the resource database contains all system objects, an upgrade can now be achieved simply copying the resource database (mssqlsystemresource.mdf and mssqlsystemresource.ldf) files to the local server.

Where can I find Resource Database?

To Identify the Location of Resource Database in SQL Server execute the below TSQL query.

/* Identify the Location of Resource Database in SQL Server */

Use master
GO

SELECT
'ResourceDB' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
FROM sys.sysaltfiles
WHERE DBID = 32767
GO

 

The physical file names of Resource database are mssqlsystemresource.mdf & mssqlsystemresource.ldf. Every instance of SQL Server has one and only one associated Resource Database related .MDF and .LDF files and the files are not shared between instances.

DBA must document the location of Resource and Master Database for each server which they maintain along with location of other System and User Databases.

Important Note: MSDN mentions that the Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location. For more information, see Physical Properties of Master.

Default Location of Resource Database in Default Instance of SQL Server is mentioned below for your reference:-

SQL Server 2014: <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\
SQL Server 2012: <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\
SQL 2008 R2:<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\
SQL Server 2008: <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\
SQL Server 2005: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

How to Backup Resource Database in SQL Server?

SQL Server cannot backup Resource Database hence DBA will have to perform file-based or disk-based backup by considering mssqlsystemresource.mdf and mssqlsystemresource.ldf files as if they are .EXE files. Using the XCOPY, ROBOCOPY or COPY command you can copy the .MDF and .LDF files even when SQL Server is up and running.

Script to Copy Resource Database Files Using XCOPY Command

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.mdf" "D:\DatabaseBackups\" /Y

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.ldf" "D:\DatabaseBackups\" /Y

 

SQL Server Agent Job Step to Copy Resource Database Files Using XCOPY command

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.*" "D:\DatabaseBackups\" /Y

 

How to Restore Resource Database in SQL Server?

Restoring Resource Database means copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective location which you have documented within your Disaster Recovery Plan.

Moreover:-

• Resource Database should be present is the same location where master database files reside.
• In case if there is a hardware failure and you need to rebuild your environment by restoring master database on to a new drive location. Then before restoring master database using WITH MOVE option a copy of Resource Database’s .mdf and.ldf files should be present.
• If you could manage to find an older version of Resource Database they you will have to reapply the subsequent patches.

Next Steps

• It is highly recommended to document the location of Resource and Master Database along with other System and User Databases.
• Create an SQL Server Agent Job to backup Resource Database once a day or at least immediately after patching the server.
• Read Resources Database in SQL Server to See when last time Resource Database was updated, Identify the current version of Resource Database.

Read more: http://www.mytechmantra.com/LearnSQLServer/Backup-and-Restore-Resource-Database-in-SQL-Server/#ixzz3h4alB7K3
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Backup and its strategy

I get lot of mail from my friends to write something about different tropics. But sometimes it is not possible due to my heavy work load. But I tried to answer them as I can.

In this article one of my friends ask me to write something about different types of backup. So in this article I am trying to illustrate some points related to it.  I am searching the search engine and find some useful tropic related to it. Here I am gathering some tropics to provide small but complete solutions related to backup and its strategy.  

When we are going to take some database backup, we find two options in backup type.

1.    Full database backup

2.    Differential database backup

3.    Transaction Log backups

Full database backup

A full backup makes a full copy of the database. This type of backup is most reliable, but it is also the most time and resource consuming. Even so, users can continue connecting to the database, reading and changing data while full backup is in progress. Full backups take longer than any other type of backup, but they're also easiest to recover from as long as no transactional activity has occurred since the last full backup.

Differential database backup

A differential backup records changes that have occurred in the database since the last full backup. Differential backups are faster and smaller than full backups. However, in case of a disaster the full backup must be restored prior to restoring any differential backups. Suppose you take a full backup every 12 hours (12AM and 12 PM) and a differential backup every 4 hours. Further suppose that the database gets corrupted due to a hardware failure at 4:15PM. You must restore the full backup from 12PM first, followed by the differential backup from 4PM. In this scenario, if you weren't taking transaction log backups, you would only lose transactions that occurred from 4PM to the time when database became corrupted.

Transaction Log backups

Transaction Log backups let you backup transactions that have occurred since the last full or differential backup, or since the last time a transaction log backup was taken. Transaction log backups are not supported by the SIMPLE recovery model. Transaction log backups can only be restored after a full backup and differential backups (if any) have been restored. Transaction log backups impose less overhead on the server than full and differential backups; therefore, transaction log backups should be taken more frequently. Although transaction log backups take the least amount of time to generate, the total time required for recovering a database using such backups is the longest; you must restore the full backup first, then differential backups (if any) followed by all transaction log backups taken since the last differential backup.

Backup Strategy

#	Item	Steps
1	Determine What is Needed	Before you begin implementing your backup strategy you need to better understand what you are trying to protect, how much data you need to recover and also whether the data can be recreated easily or not. To get started you need to ask yourself a few questions such as the following:  How much data can be lost? A day, one hour, a week, none... What kind of processing occurs? Transaction, batch loading, reporting, a combination... This data be easily recreated if there is a failure or it is not the only source of this data? Do you have very big batch loads that take considerable amount of time or load a lot of data? Based on the answers to these questions you can determine the proper recovery model for your database as well as the proper backup types and backup schedule.
2	Recovery Model	Based on the answers to the questions in item #1 you can determine the recovery model for your database. How much data can be lost? A day, one hour, a week, none... If your answer is less than a day or none you should consider the Full recovery model. If your answer is a more than a day then you could use the Simple recovery model (this is assuming you are doing full backups every day). What kind of processing occurs? Transaction, batch load, reporting only, a combination... If your answer is transaction or a combination then you should consider the Full recovery model. If your answer is reporting only you could use the Simple recovery model (this is assuming this data is loaded from another source and can be easily recreated). If your answer is batch load you could use the Simple recovery model (this is assuming that you can reload the batch data without losing any data). This data be easily recreated if there is a failure or it is not the only source of this data? If your answer is No than you should use the Full recovery model. If your answer is yes then you could use the Simple recovery model (assuming that it is not that hard to recreate the data). Do you have very big batch loads that take considerable amount of time as well as load a lot of data? If your answer is No than you should use the Full recovery model. If your answer is yes then you could use the Bulk-Logged recovery model.  Depending on how you load your data you can minimize the logging in the transaction log and therefore have smaller transaction log backups. The only drawback is that if there is a bulk-logged activity in a transaction log backup you are not able to do a point in time recovery for the transaction log backup file that has the bulk-logged operation. If your answers include Full for any of these questions you should use the Full recovery model.  The only difference would be the answer to the last question where you could use Bulk-Logged instead of Full. Note: it is also possible to change your recovery model based on different processing, but you need to ensure your backup process follows any of these changes so you do not potentially loosed important data.
3	Select Backup Types	SQL Server offers many different types of backups such as the following: Full - backups entire database Differential - backups all changes since last full backup Transaction Log - backups transaction log for all changes since last transaction log backup File - allows you to backup one data file Filegroup - allows you to backup an entire filegroup Based on the recovery model you selected you should follow a plan such as the following: Full or Bulk-Logged Recovery - Full backups and transaction log backups Simple Recovery - Full backups In addition to doing the above, you can also introduce Differential backups in between your Full backups.  This is helpful if you do a lot of transaction log backups during the day, so that when you need to restore you can just restore the full backup, the latest differential backup and any transaction log backups after the differential backup. Other options include doing file or filegroup backups for very large databases.
4	Backup Schedule	The next thing you need to decide is when to schedule your backups.  The most intense backup is the full backup since it needs to read the entire database and write out the entire database.  Since this activity is disk I/O intensive the best time to do this is at low peak times, therefore most people run full backups during off hours. Here is a sample schedule, again this would be based on what you determined to do in step 3: Full Backups - midnight Differential Backups - every 3 hours Transaction Log Backups - every 1 hour
5	Backup Process	SQL Server offers many built in options to backup your database such as: Maintenance Plans Using Enterprise Manager (2000) or Management Studio (2005) Using sqlmaint.exe Writing T-SQL code using the BACKUP command
6	Document	As with all administration activities you should document your backup procedures and the criteria you will use to determine the recovery model as well as the backup types and backup schedule.  It is much easier to have a defined plan, so when a new database is created you can just follow the steps that have been outlined instead of having to figure this out each time a new database is introduced.
7	Backup to Disk	The fastest way to do SQL Server backups is disk to disk.  I guess doing the backup to memory would be faster, but this doesn't give you a permanent copy that can be restored or archived, plus the option doesn't really exist.  There are a lot of backup tools that allow you to go directly to tape, but it is better to have the latest backup on disk so the restore process is faster instead of having to pull the file from tape.
8	Archive to Tape	Once the backup has been created on disk you should then archive to tape for long term storage. These archive copies are not used all that often, but they do come in handy when you are doing some research or an audit and you need to get the database back to the state it was at some point in the past.
9	Backup to Different Drives	As mentioned above the backup process is a disk I/O intensive activity.  Therefore you should try to separate your disk reads from your disk writes for faster I/O throughput. In addition, it is better to have the backups on a physically separate disk therefore if one of the disks dies you don't lose both your data file and backup file.
10	Secure Backup Files	In a previous tip we talked about how native SQL Server backups are written in plain text and can be opened with a text editor and therefore the data could be comprised.  Based on this you need to ensure that your backup files are written to a secure location where only the people that need to have access to the files have access. Also, this ensures that the files are not tampered with or accidentally deleted and therefore unavailable when you need them.

Hope you like that.

DB Restore from Network Drive

I am a mail form one of my DBA friend to request about:

"Please advice that how I can restore the database from backup file placed on another server. Because while restoring any database system shows local drive as well as network drives attached."

To provide this solutions first we look at the, what MS says about it.

In MS Words

"For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running"

There are 2 options to solve this problem

Option-1

Just Copy the Backup file from network server to local drive of the SQL Server and then restore it.  Good one … Quick Solutions.

Option-2

Make a network drive and use this drive to restore database.

Only flipside is that this network drive mapping will remain till next SQL Server Service restart.

We can create network drive by 2 options

Option-A

Right click the "My network places" and Select the" Map network Drive" and provide drive name and UNC path to create network drive.

 Option-B

Use xp_cmdshell extended stored procedure to make network drive.

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Systax:

exec master..xp_cmdshell 'NET USE Y: \\MACHINE\SHARENAME <password> /USER:<user>'

EXEC xp_cmdshell 'NET USE K: \\Srv-1\Back pass123 /USER:DOM\joydeeep'

Hope you like this

Cannot open backup device

One of my DBA fried mailed me related to database backup.  He told me that the backup failed every time and gets an error message like this.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\AdventureWorks.bak'. Operating system error 5(Access is denied.).

To solve these problems I am searching the search engine and find some related note provides my MS mentioned bellow.

The MS Notes:

To enable backups to network drives, perform the following steps:

1.          The SQL Server service must be started using a domain user account to access any

         resources on a remote computer. Verify that the MSSQLServer service is started under a domain 

         account that has write access to both the Windows NT Server share and its underlying partition (if the 

         partition is formatted with the Windows NT file system or  NTFS).

2.            In Control Panel, double-click the Services icon.

3.            Select the MSSQLServer service and then click Startup.

4.            Examine the startup options for the service and verify that This Account is selected and   that a valid domain account is supplied in the      form Domain_Name\Domain_account (with the correct password).

NOTE: If you changed the service to run under a domain account, you must stop and restart the MSSQLServer service.

5.            Verify that the account specified has write access on the Windows NT share to    which you are backing up, as well as the underlying partition (if the partition is  formatted as NTFS). To do this, perform the following steps: 

           

a.            Right-click the share name in Windows NT Explorer.

b.            Click Sharing on the shortcut menu.

c.    On the Security tab, click Permissions.

The main thing that you have to do is

1.    Check in which account your SQL server is running

Start Menu àAll programsà MS SQL Server 2008àConfiguration ToolsàSQL Server Configuration Manager.
Then choose the Running SQL Server and right click it to get the properties. Then Look at the Log on Tab to find the account information.

2.    Give the full permission of these accounts.

Query Solving (Database Restore Related)

I have a Restore related question from one of my DBA Friends mentioned bellow

Query:

Hi, I tried restoring DB with below query 
RESTORE DATABASE sirwar

FROM DISK = 'F:\15.02\15.02.12 backup'

WITH REPLACE,

MOVE 'LogicalDatafileName' TO 'f:\sirwar.mdf',

MOVE 'LogicalLogfilename' TO 'f:\sirwar.ldf'

but this came with an error like this.......... Msg 3234, Level 16, State 2, Line 1 < ' 
Logical file 'LogicalDatafileName' is not part of database 'sirwar'. Use RESTORE FILELISTONLY to list the logical file names. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally.

Solutions mentioned bellow:

1) sp_helpdb on the source database. It's the first column in the 
    second result set, just copy and paste into the single quotes. 

2) RESTIRE FILELISTONLY on the back-up file. Again, it's the first 
    column. 

Restore Database to Point in Time (STOPAT)

RESTORE LOG ... WITH RECOVERY, STOPAT option allows you to restore database backup to a point in time.  This option gives you the ability to restore a database backup file prior to an event that occurred.
Each of these transactions has a LSN (log sequence number) along with a timestamp, so when you restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.

For expample my database got currupted at -- 13:58.
I have full backup at 13:00 and Transaction log backup at  14:06
I want to restore the log at this point 'Nov 07, 2012 01:57:00 PM'

Use AdventureWorks

go

-- Full Backup has taken 13:00

Select * from Emp where EmployeeID =5;

--Title =Tool Designer
--13:46 

UPDATE Emp SET Title ='Tool Designer Engineer' where EmployeeID=5;

GO

Select * from Emp where EmployeeID =6;

--Title =Marketing Manager 
-- 13:52

UPDATE Emp SET Title ='Assit Marketing Manager' whereEmployeeID =6;

GO

-- Database has been corrupted
-- 13:58

Select * from Emp where EmployeeID =7;

--Title =Production Supervisor - WC60
-- 14:01

UPDATE Emp SET Title ='Production Supervisor' where EmployeeID=7 ;

GO

-- 14:06 Transaction Backup completed

-- Restore Full Database @ 13:00 with NORECOVERY

use master

GO

RESTORE DATABASE AdventureWorks FROM

DISK ='E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_1.BAK',

DISK= 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_2.BAK',

DISK ='E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_3.BAK'

WITH NORECOVERY , STATS = 5

GO

-- Point in time restore

-- Restoring now Transaction log backup WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM'

RESTORE LOG AdventureWorks

FROM DISK='E:\MSSQL\Backup\TRAN\AdventureWorks_7_Nov2012_14h6m_TRAN_1.BAK'

WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM' , STATS = 5
GO

select EmployeeID,NationalIDNumber, ManagerID,Title

from Emp where EmployeeID IN(5,6,7)

Find Backup history for one week

use msdb

go
SELECT 
SERVERPROPERTY('Servername') AS Server_Name, 
bs.Database_name, 
bs.Backup_start_date, 
bs.Backup_finish_date, 
bs.Expiration_date, 
CASE bs.type 
WHEN 'D' THEN 'FULL' 
WHEN 'I' THEN 'Differential ' 
WHEN 'L' THEN 'Log' 
END AS Backup_type, 
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name, 
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ONbmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >=GETDATE() - 7) 
ORDER BY bs.database_name, 
bs.backup_finish_date 
Reference :http://msdn.microsoft.com/en-us/library/ms186299.aspx

Find SQL Server database backup compression

If you would like to know about the backup file compression on Sql Server 2008R2 and later version you can use the below scripts. 

Note: Systems databases are not compressed for backup (master,model and msdb)

select 
 server_name,
 database_name,
 [type] AS Backup_type,
 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],
 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  AS  [Compressed_Backup_Size (MB)],
 100- ((compressed_backup_size/backup_size)*100)   as "Compressed%",   
 backup_finish_date
from msdb.dbo.backupset
--where database_name ='db1' and [Type]='D'
order by backup_set_id desc

Stripe SQL Backups on multiple files

-- FULL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName    
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND  



--DIFFERENTIAL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName    
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH DIFFERENTIAL,
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 

How to restore database from RedGate SQLBackup Files

If you are using RedGate Sql backup to restore the database then you can use following t-sql code

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [AdventureWorks]
FROM
   DISK = ''\\myserver\Backup\AdventureWorks_01.sqb'',
   DISK = ''\\myserver\Backup\AdventureWorks_02.sqb''
WITH RECOVERY,
    MOVE ''AdventureWorks''     TO ''H:\MSSQL\Data\AdventureWorks.mdf'',
    MOVE ''AdventureWorks_log'' TO ''I:\MSSQL\Logs\AdventureWorks_log.ldf'',
REPLACE"'