MS SQL-Database Backup Types and Methodology

 Resource Database :The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.                   

 To determine the version number of the Resource database, use:             SELECT SERVERPROPERTY('ResourceVersion');

To determine when the Resource database was last updated, use:    SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

To access SQL definitions of system objects, use the OBJECT_DEFINITION function       SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

What is snapshot in 2005

Database Snapshots are a new Enterprise Edition-only tool, introduced in SQL 2005, that provide a read-only, "virtual" copy of a database, at a given point in time.

Snapshots are read only static views of the database

sp_helpdb 'AdventureWorks2012'

name                                            FILENAME

AdventureWorks2012_Data      C:\AdventureWorks2012_Data.mdf

AdventureWorks2012_Log              C:\AdventureWorks2012_log.ldf

---------------------------------------------------------------------------------------------------------------

Create a snapshot of the database.

CREATE DATABASE AdventureWorks_dbss1800 ON

( NAME = AdventureWorks2012_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\SQLSERVER2012\MSSQL11.SQLSERVER12\MSSQL\DATA\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF AdventureWorks2012;

GO

----------------------------------------------------------------------------------------------------------------

sp_helpdb 'AdventureWorks_dbss1800'

name                                                       FILENAME

AdventureWorks2012_Data                   C:\AdventureWorks2012_Data.mdf

AdventureWorks2012_Log                          C:\AdventureWorks2012_log.ldf

MSDB database be moved

Yes!  The MSDB database files can be moved.  The are 2 methods to move the MSDB database files.  One is a more common method, especially during a new install with limited users or uptime requirements.  The other lets the SQL Server instance stay up and interrupts only the MSDB-dependent parts of the operation such as the SQL Server Agent.

Option 1

This is the more common method that is generally suggested 

Step 1- Inform SQL Server that during the next startup of the service the MSDB database data and/or log file(s) will be somewhere else. 

During this step it will allow the file name to be changed, but it is not advised to change the physical file name or extension.

The following code will change the expected location of the MSDB database data and log files respectively from wherever they currently are to the root C drive.  It assumes the default logical names.

ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBData , FILENAME = 'C:\MSDBData.mdf' );

ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\MSDBLog.ldf' );

Executing these commands will give a very clear output message.

The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.

Step 2 -Now that SQL Server has been informed of the new location(s), stop the SQL Server and SQL Server Agent services so that it will release its lock on those 2 (or more) MSDB database files.

Step 3 -Move the file(s) to the intended location(s) that were given to SQL Server in step 1.

Step 4 -Start the SQL Server service.

SQL Server should start right up.  Immediately check MSDB in SSMS.  Does it look like the screenshot from the very beginning of this tutorial with the words "(Recovery Pending)" at the end?  If so, there is a problem with the file location of the MSDB files.  Perhaps they aren’t in the same place the catalog thinks they are or maybe they are in the right place, but the service account can’t see them there due to a permission issue.  Sometimes the Event Viewer, if using Windows, offers more details as to why some databases don’t come online and should be consulted.  In any case, work to resolve that issue and consider putting the files back, if necessary, to get the instance up and running again.

Option 2

Start by stopping the SQL Server Agent service and kill any other processes that might be using MSDB.  Then, simply back up the MSDB database and immediately restore MSDB with the REPLACE option and use the MOVE keyword to select a new location for any files.

BACKUP DATABASE [msdb] TO  DISK = N'C:\backups\MSDB.bak';

GO

RESTORE DATABASE [msdb] FROM DISK = N'C:\backups\MSDB.bak' 

WITH  REPLACE,

MOVE N'MSDBData' TO N'C:\Backups\MSDBData_NEW.mdf',  

MOVE N'MSDBLog' TO N'C:\Backups\MSDBLog_NEW.ldf';

GO

Once the restore finishes successfully, restart the SQL Server Agent.

Moving the master Database

To move the master database, follow these steps.

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.

4. In the Existing parameters box, select the -d parameter. In the Specify a startup parameter box, change the parameter to the new path of the master data file. Click Update to save the change.

5. In the Existing parameters box, select the -l parameter. In the Specify a startup parameter box, change the parameter to the new path of the master log file. Click Update to save the change.
   The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.
   -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:
   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf

6. Click OK to save the changes permanently and close the SQL Server (instance_name) Properties dialog box.

7. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

8. Move the master.mdf and mastlog.ldf files to the new location.

9. Restart the instance of SQL Server.

10. Verify the file change for the master database by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc  FROM sys.master_files  WHERE database_id = DB_ID('master');   

11. At this point SQL Server should run normally. However Microsoft recommends also adjusting the registry entry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, where instance_ID is like MSSQL13.MSSQLSERVER. In that hive, change the SQLDataRoot value to the new path. Failure to update the registry can cause patching and upgrading to fail.