T-SQL query to find "mssqlsystemresource" database ID and database files location

We all know that mssqlsystemresource is a system database that is introduced from SQL Server 2005 onwards.
Here are few links which I posted earlier which gives more information about mssqlsystemresource database.

T-SQL Query to find IP Address of SQL Server

Here is a handy T-SQL script to find the IP Address of the SQL Server you are connected to.

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP Address Of SQL Server]

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID

What extra information can we get from a backup file?

Consider a situation where you got a alert saying the "disk is almost full" and you are browsing the disk to find out what can be removed. During this process you come across a orphaned backup file with just some name specified to that file and you do not know from which database this backup file is generated, when was it taken, is it a full or differential or log backup, etc, etc...
We all know that if we have the proper backup file(s), we can recover a database fully, but without the proper information we cannot recover. In this case, we do not know the information about the backup file that was found.

So how to proceed?
One way is to restore the backup and check,

1. But restoring of a backup taken in higher version is not allowed in lower version. For example, a backup taken in SQL Server 2008 cannot be restored in  SQL Server 2005.
2. Without a full backup we cannot start a new database restore and in this case we do not know what is the backup type.

So what next?

Another way is to check without restoring. For this, SQL Server provides additional commands that can be used with the backup file using which we can get extra information from the backup file.
These commands are

1. RESTORE HEADERONLY
2. RESTORE FILELISTONLY
3. RESTORE LABELONLY

RESTORE HEADERONLY - Returns the backup header information of the specified backup.

And the header information includes information about

Column name	Description for SQL Server backup sets
BackupName	Backup set name.
BackupDescription	Backup set description.
BackupType	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	Expiration date for the backup set.
Compressed	Whether the backup set is compressed using software-based compression: 0 = No 1 = Yes
Position	Position of the backup set in the volume (for use with the FILE = option).
DeviceType	Number corresponding to the device used for the backup operation. Disk: 2 = Logical 102 = Physical Tape: 5 = Logical 105 = Physical Virtual Device: 7 = Logical 107 = Physical
UserName	User name that performed the backup operation.
ServerName	Name of the server that wrote the backup set.
DatabaseName	Name of the database that was backed up.
DatabaseVersion	Version of the database from which the backup was created.
DatabaseCreationDate	Date and time the database was created.
BackupSize	Size of the backup, in bytes.
FirstLSN	Log sequence number of the first log record in the backup set.
LastLSN	Log sequence number of the next log record after the backup set.
CheckpointLSN	Log sequence number of the most recent checkpoint at the time the backup was created.
DatabaseBackupLSN	Log sequence number of the most recent full database backup.
BackupStartDate	Date and time that the backup operation began.
BackupFinishDate	Date and time that the backup operation finished.
SortOrder	Server sort order. This column is valid for database backups only. Provided for backward compatibility.
CodePage	Server code page or character set used by the server.
UnicodeLocaleId	Server Unicode locale ID configuration option used for Unicode character data sorting. Provided for backward compatibility.
UnicodeComparisonStyle	Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. Provided for backward compatibility.
CompatibilityLevel	Compatibility level setting of the database from which the backup was created.
SoftwareVendorId	Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200).
SoftwareVersionMajor	Major version number of the server that created the backup set.
SoftwareVersionMinor	Minor version number of the server that created the backup set.
SoftwareVersionBuild	Build number of the server that created the backup set.
MachineName	Name of the computer that performed the backup operation.
Flags	Individual flags bit meanings if set to 1: 1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY.
BindingID	Binding ID for the database
RecoveryForkID	ID for the ending recovery fork. This column corresponds to last_recovery_fork_guid in the backupset table.
Collation	Collation used by the database.
FamilyGUID	ID of the original database when created. This value stays the same when the database is restored.
HasBulkLoggedData	1 = Log backup containing bulk-logged operations.
IsSnapshot	1 = Snapshot backup.
IsReadOnly	1 = Database was read-only when backed up.
IsSingleUser	1 = Database was single-user when backed up.
HasBackupChecksums	1 = Backup contains backup checksums.
IsDamaged	1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
BeginsLogChain	1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the Simple to the Full or Bulk-Logged Recovery Model.
HasIncompleteMetaData	1 = A tail-log backup with incomplete meta-data.
IsForceOffline	1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly	1 = A copy-only backup.
FirstRecoveryForkID	ID for the starting recovery fork. This column corresponds to first_recovery_fork_guid in the backupset table.
ForkPointLSN	If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. Otherwise, this value is NULL.
RecoveryModel	Recovery model for the Database, one of: FULL BULK-LOGGED SIMPLE
DifferentialBaseLSN	For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential. For non-differential backup types, the value is always NULL.
DifferentialBaseGUID	For a single-based differential backup, the value is the unique identifier of the differential base.
BackupTypeDescription	Backup type as string, one of: DATABASE TRANSACTION LOG FILE OR FILEGROUP DATABASE DIFFERENTIAL FILE DIFFERENTIAL PARTIAL PARTIAL DIFFERENTIAL
BackupSetGUID	Unique identification number of the backup set, by which it is identified on the media.
CompressedBackupSize	Byte count of the backup set. For uncompressed backups, this value is the same as BackupSize.

Example:

RESTORE FILELISTONLY - Returns the information about list of the database and log files contained in the backup. 

Column name	Description
LogicalName	Logical name of the file.
PhysicalName	Physical or operating-system name of the file.
Type	The type of file, one of: L = Microsoft SQL Server log file D = SQL Server data file F = Full Text Catalog
FileGroupName	Name of the filegroup that contains the file.
Size	Current size in bytes.
MaxSize	Maximum allowed size in bytes.
FileID	File identifier, unique within the database.
CreateLSN	Log sequence number at which the file was created.
DropLSN	The log sequence number at which the file was dropped. If the file has not been dropped, this value is NULL.
UniqueID	Globally unique identifier of the file.
ReadOnlyLSN	Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change).
ReadWriteLSN	Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change).
BackupSizeInBytes	Size of the backup for this file in bytes.
SourceBlockSize	Block size of the physical device containing the file in bytes (not the backup device).
FileGroupID	ID of the filegroup.
LogGroupGUID	NULL.
DifferentialBaseLSN	For differential backups, changes with log sequence numbers greater than or equal to DifferentialBaseLSN are included in the differential.
DifferentialBaseGUID	For differential backups, the unique identifier of the differential base. For other backup types, the value is NULL.
IsReadOnly	1 = The file is read-only.
IsPresent	1 = The file is present in the backup.

Example:

RESTORE LABELONLY - Returns the information about backup media of the given backup. 

Column name	Description
MediaName	Name of the media.
MediaSetId	Unique identification number of the media set.
FamilyCount	Number of media families in the media set.
FamilySequenceNumber	Sequence number of this family.
MediaFamilyId	Unique identification number for the media family.
MediaSequenceNumber	Sequence number of this media in the media family.
MediaLabelPresent	Whether the media description contains: 1 = Microsoft Tape Format media label 0 = Media description
MediaDescription	Media description, in free-form text, or the Tape Format media label.
SoftwareName	Name of the backup software that wrote the label.
SoftwareVendorId	Unique vendor identification number of the software vendor that wrote the backup.
MediaDate	Date and time the label was written.
Mirror_Count	Number of mirrors in the set (1-4).
IsCompressed	Whether the backup is compressed: 0 = not compressed 1 =compressed

Example:

SQL Server Data Type Conversion Chart

As part of the SQL Server Developer or DBA job, you may come across many instances where you are required to convert from one data types to another in order to complete the task given to you and the conversions can be implicit or explicit.
Microsoft has released a SQL Server Data Type Conversion Chart which helps in determining the conversions between data types and the type of conversion, whether it is a explicit or implicit conversion.


This poster can be downloaded form here.

How to rename a column in SQL Server

Simple way of renaming a column in SQL Server is

1. Go to the Table Designer 
2. Modify the column name 
3. Save the changes

Other way is using the Stored Procedure "sp_rename"

Syntax:

Exec sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ]

Example:

EXEC sp_rename N'dbo.tbl_Details.MailID', N'e-MailID', 'COLUMN'

When the above command is executed, the column named "MailID" in the table "tbl_Details" will be renamed to "e-MailID"

By using the stored procedure sp_rename, the following object types can be renamed

1. Column
2. Database
3. Index
4. Object
5. UserDataType 

Piecemeal Restore

What is Piecemeal Restore?
Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?
Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service,

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures

T-SQL Query to get SQL Server Start time and Uptime

Below query gives the SQL Server Start time and Up time details.
This option is available from SQL Server 2008 and above and requires "VIEW SERVER STATE" permission to execute the query

SELECT sqlserver_start_time AS [SQL Server Start Time]
   ,CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())/60 AS VARCHAR) + ' Hours ' 
   +CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())%60 AS VARCHAR) + ' Minutes' AS [Uptime]
FROM sys.dm_os_sys_info

Contained Database - SQL Server 2012 - New Features

There has been lot of talk about SQL Server 2012 and its features - out of which there are few which are definitely a big leap. One among them is CONTAINED DATABASES

What is Contained Database?

Any Database which is INSTANCE INDEPENDENT with no external dependencies and has self-contained mechanism of authenticating users is referred to as Contained DB.

The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.

Frequently used terminologies when using contained databases:

• Application Boundary - It is the boundary between the server instance and the application code


• Application Model - The application model is the programming surface of a contained database to which SQL Server applications are written. This surface area differs from non-contained databases in that it clearly separates the application from the instance. This separation is made by the application boundary.

Contained database functionality can be classified into -

• Unchanged and available for use within the application model. This classification includes most SQL Server objects, CREATE and ALTER statements, procedures, and so on.


• Not available within the application model. This classification includes purely instance-level functions such as instance settings, processor affinity, resource governance, or the creation of databases themselves.


• Available within the application model with certain changes or restrictions. This classification includes collations and authentication.

The management model includes everything that lies outside of the application model on the instance level. The management model concerns the maintenance and operation of the instance as a whole as it resides outside of a contained database.

• ContainedIt is a user entity that resides entirely within the application boundary.

• UncontainedIt is a user entity that crosses the application boundary.

• Non-contained databaseDatabase whose containment is set to NONE.

• Fully contained databaseDatabase that does not allow any objects or functions to cross the application boundary.

• Partially contained databaseDatabase that allows features to cross the application boundary. It is available in RTM

 Contained user

There are two types of users for contained databases.

1. Contained database user with password which are authenticated by the database.
2. Windows principals that can directly connect to the database and does not need any login in the master database.

What is the purpose of Contained DBs?

1. Is there a way to avoid any loss of information during Database movement?
2. Is there a way to avoid checking for orphan users when we do a DB restoration and end up fixing the same one by one? Saving time J
3. Is there a way to move all the jobs related to a DB (agent job information) along with the Database movement? Etc.

Answer to all the above questions is CONTAINED Databases feature in DENALI (SQL Server 2012)

What are the contents of Contained DB?

The contained database, keeps all necessary information and objects in the database, for example

• Tables
• Functions
• Constraints
• Schemas
• Types

It also stores all application-level objects in the database, including

• Logins
• Application-level agent jobs
• Persisted error messages
• Linked server information
• System settings

Implementing\Creating Contained DBs

There are 2 ways of implementing contained DB

1. Using SQL Server Management Studio (SSMS)
2. Using system stored procedures\scripts

Using SQL Server Management Studio (SSMS)

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level.

Right click on the instance and select 'properties'and then select ‘Advanced’ option to set the Containment property to True.

Step 2: Create a database and set its CONTAINMENT property to Partial.

NOTE: The current RTM version only supports PARTIAL containment

• Once the DB is created either using SSMS or scripts – set the DB containment property to ‘Partial’

Step 3: Create a Contained User within the newly created contained database.

Step 4: Login to the contained database using the user that exists in Contained Database.

Using system stored procedure

--Enabled Advanced options
Exec sp_configure 'show advanced', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO 

Changing Non Contained DB to Contained DB:

A non-contained DB can be set as contained but with few things to take care (mentioned below)

Step 1: To identify the NonContained objects

SELECT
CLASS_DESC,
FEATURE_NAME,
FEATURE_TYPE_NAME
FROM SYS.DM_DB_UNCONTAINED_ENTITIES

Step 2: To change the DB property to Partial containment

USE MASTER
GO
ALTER DATABASE NONCONTAINEDDB SET CONTAINMENT=PARTIAL;
GO

Step 3: To migrate users associated with Sql server logins

USE NONCONTAINEDDB
GO
EXEC SP_MIGRATE_USER_TO_CONTAINED @USERNAME = N'testuser',
@RENAME = N'testusercontained',
@DISABLE_LOGIN = N'DISABLE_LOGIN'

The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.

Step 4: We can repeat STEP 1 if there are any more non contained DB objects

How do we backup and restore contained DB?

The normal process of backup and restore will also apply in using contained DB as well. However, if we are restoring the backed up contained DB on any other instance we need to Enable "Contained Database Authentication" property on the SQL Server instance Level which is turned off by Default.

Else we get to see the following error –

Msg 12824, Level 16, State 1, Line 1 The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Using scripts:

--Enabled Advanced options
Exec sp_configure 'show advanced', 1
go
RECONFIGURE WITH OVERRIDE;
go

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;
go
RECONFIGURE WITH OVERRIDE;
go

Can we use CREATE\ALTER Database Statement on Contained DBs?

The normal ALTER command doesn’t work on contained DB. Instead, a new option called ‘CURRENT ‘has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.

ALTER DATABASE CURRENT 

Benefits of using contained DBs

There are both Pros and Cons in using contained DBs –

Pros

Database Movement

One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Ex: Orphan Users

Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of in the database. When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind. Then you must identify the data left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.

Initial Application Development and administration

In a contained database, all elements of the application will reside within the database itself, thereby limiting instance-level impacts on the application and instance-level concerns for the developer.

A contained database stores data within the database itself, the lack of a disconnect between the application and the information previously held at an instance level means that the application administrator can easily verify production environment settings that may affect the application, access the necessary permissions to perform required duties, have the ability to backup and restore the database, and so on.

Threats of using contained DBs

There are disadvantages on using contained DBs

Related to Users

The major disadvantage of using contained DBs is to do with users –  

Db_owner and Db_securityadmin roles- Users in a contained database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission if the SQL Server administrator. Granting users access to a contained database increases the potential attack surface area against the whole SQL Server instance. 

• Guest Account - Database owners and database users with the ALTER ANY USER permission can create contained database users. After connecting to a contained database on an instance of SQL Server, a contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account.  
• Keep away from duplication user and login ids - If a contained database user with password is created, using the same name as a SQL Server login, and if the SQL Server login connects specifying the contained database as the initial catalog, then the SQL Server login will be unable to connect.

For more we can refer to Books Online.

Where can we have contained DBs?

Few scenarios where we find the usage of contained DBs - 

Two SQL Server instances which are being used by different team and different set of SQL Logins created to each team respectively. 

• The same can be implemented using contained DBs and creating users and not logins (reducing the dependency on logins) 

If we need to create a copy of any of the existing DBs – the process would be to Take backup, restore and verify all the orphan users. 

• The same can be reduced to just backup and restore – no need to verify the users (if the same set of users need access to the newly created DB)
• In addition we can create users if any new needed with no login needed at instance level.

References:

Features WITHIN the application model (Contained)

Features OUTSIDE the application model (Not Contained)

Benefits of using contained DBs

Threats against contained DBs

Other references