SQL Server – Database Restore Date

Background

Get a tally of last database restore dates.

 

SQL

Code


select 
		  [database]
			= tblRH.[destination_database_name]

		, [restoreType]
			= case tblRH.[restore_type]
					when 'D' then 'Database'
					when 'L' then 'Transaction'
					else 'N/A'
			  end	

		, [restoreDateAsDate]
			= tblRH.restore_date

		, [restoreDateAsString]
			= convert
				(
					  varchar(30)
					, tblRH.restore_date
					, 100
				)
			 
from   [msdb].[dbo].[restorehistory] tblRH

where tblRH.[restore_history_id]
		=
			(
				select max(tblRH_Inner.[restore_history_id])
				from   [msdb].[dbo].[restorehistory] tblRH_Inner
				where  tblRH.[destination_database_name]
						= tblRH_Inner.[destination_database_name]
				and    tblRH.[restore_type]
						= tblRH_Inner.[restore_type]
			)
		
order by
		tblRH.[destination_database_name] asc



 

Output

SQL Server – Database Restore – Errors – “C:\ClusterStorage\”

Background

Was reviewing SQL Server Agent logs earlier this week and found errors resulting from a database restore SSIS Package.

 

Error

Error Text

The error looks like the one below:


Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Data' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Log' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.


Error Tabulated

Message ID Sample Explanation
 Msg 5133 Directory lookup for the file “C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf” failed with the operating system error 3(The system cannot find the path specified.). Review the designated folder name and ensure it desired.

If so create and/or grant SQL Server Engine permission.

 Msg 3156  File cannot be restored. Use with move to Identify a valid location for the file Please add the move option to the restore command
 Msg 3199  Problems were identified while planning for the RESTORE statement  Flags errored restore operations
 Msg 3013  RESTORE DATABASE is terminating abnormally.  Restore failed abnormally

 

 

 

TroubleShooting

The error is of course the fact the on the originating SQL Instance the database files are located on C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA  ( Data Folder ) and C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data   ( Log Folder ).

 

Remediate

The fix is to create the folders ahead of time

Stay on Originating Folder

Create Folders

exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\'
exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\'

 

Restore Database


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'

 

Use Own Folders

Get File groups from DB Backup File

restore filelistonly
from disk = 'C:\TEMP\IND.bak'</pre>

Create Folders

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\'
exec master.dbo.xp_create_subdir 'F:\Microsoft\SQLServer\Logfiles\'

Use Move Filegroup


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'
with
	  move 'IND_Data' to 'E:\Microsoft\SQLServer\Datafiles\IND_Data.mdf'
	, move 'IND_Log' to  'F:\Microsoft\SQLServer\Logfiles\IND_Log.ldf'
	, replace
	, stats=1

 

SQL Server – Life after Database Restore – Inherited Security

Background

Once a database is restored there a few things one has to do.

With SQL based accounts, one of those things is to realign the database users.

With Windows Authenticated users, things should be OK if in same Windows Domain.

On the other hand, if different domain we have to grant access to targeted Logins, as well.

 

Inherited Security

Let us talk about what I will call “Inherited Security“.

Outline

  1. Restore Database
  2. Create Login
    • Normal login, not sysadmin
  3. Query Login as the non-privilege Login
    • Using “Execute As Login” query one of the tables in the restored database
    • Errored out indicating Login does not have access to restored database
  4. Grant Server role permission to created Account
    • Only applicable if SQL Version is v2014
    • The specific SQL Instance privilege we will like to grant is “Connect any database
    • Again, please keep in mind that “Connect any database” was instituted in v2014
      • Docs / S​QL / T-​SQ​L / statements / GRANT Server Permissions (Transact-SQL)
        Link
  5. On targeted database, allow guest access
    • On targeted database, grant connect access to guest
    • In SQL Server, the guest access is anyone who is registered in SQL Server, but does not have an account on the targeted database
    • Grant specific permissions to guest account

Code

Create Login



--Norton by Symantec // Password Generator
--https://identitysafe.norton.com/password-generator/#
--s19miewRoest

declare @loginName	varchar(30)
declare @password	varchar(30)

set @loginName = 'BISUser'
set @password = 's19miewRoest'

if suser_id(@loginName) is null
begin

	print 'Create Login ' + @loginName + ' ...'

	create login [BISUser]
		with password = 's19miewRoest'
		, check_policy=off
		, check_expiration= off

	print 'Create Login ' + @loginName
end
go

 

Query restored data under guise of normal Login

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

 

Error Image

 

Error Text

Msg 916, Level 14, State 1, Line 9
The server principal "BISUser" is not able to access the database "INDThirdParty" under the current security context.

Remediation – Grant Connect Any database

Code

Grant Connect Any database to Principal
Code

use [master]
GO
GRANT CONNECT ANY DATABASE TO [BISUser]
GO

Retried querying data and got new error message
Error

 

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.

 

Grant permission on targeted database to all users

On targeted database, tried granting appropriate permissions to all users

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'public'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [public]
go

Error

 


Msg 15405, Level 16, State 1, Line 36
Cannot use the special principal 'public'.
Msg 15405, Level 16, State 1, Line 41
Cannot use the special principal 'public'.

Conclusion

So we see that we need more.

 

Remediation – Allow Guest access

Code

Revoke “Connect Any Database” From Principal
Code

use [master]
GO

REVOKE CONNECT ANY DATABASE FROM [BISUser]
GO

On Targeted Database, Enable Guest Access
Code

use [INDThirdParty]
GO

GRANT CONNECT TO [Guest]
GO

Retried querying data and got the permission error message again
Error

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.
Grant permission on targeted database to guest

On targeted database, grant appropriate permissions to guest

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'guest'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [guest]
go

Retry query

On targeted database, grant appropriate permissions to guest

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

SQL Server Profiler

 

Explanation
  1. The NTUserName stays as the logged on user
  2. The LoginName changes as we transition in with “EXECUTE AS LOGIN” and out with “REVERT

Conclusion

We are good!

 

Dedicated

Dedicating to Matt Bowler & Dan,  a commenter on Matt’s Post

Restore database permissions
Posted On: 2013-Jan-10th
Link

 

 

SQL Server – Partial Database Restores – Benefits & Impact

Lineage

In our last couple of posts we focused our lenses on partial database restores.

Those posts are here:

  1. SQL Server – Database Restore – “Defunct State”
    here
  2. SQL Server – Database Restore – Skipping File Groups
    here

 

Current

In this post we will look a bit more at the benefits and costs of restoring just a portion of database.

 

Reviews

GUI

Database

File Groups

databaseProperties-FileGroups (cropped-up)

 

Files

databaseProperties-Files

 

 

Tables

dbo.ten95B

tableCount-Table1

 

Storage
  1. Table Name :- dbo.ten95B
  2. File Group :- PRIMARY
  3. Text filegroup :- fgLOB
  4. Data Space
    • 1370 MB
    • or 1.4 GB
  5. Row Count :- 4800

 

Query

Database

Database – Files

Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.

SQL

select 
		  tblSDF.[file_id]
		, tblSDF.[name]
		, tblSDF.[physical_name]
		, tblSDF.[type_desc]
		, tblSDF.[state_desc]
		, tblSDF.size
--		, tblSDF.*
from   sys.database_files tblSDF

Image

Database – Show File Stats

Let us issue “DBCC ShowFileStats” to gather information about our database files.

SQL
dbcc showfilestats

Image

Table

Let us try to read data from table

 

Query Table for Data – All Columns

dbo.ten95C
SQL

select top (1000) *
from [dbo].[ten95C]

Image
filegroupThatCanNotBeAccessed

 

Textual

Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed

 

Query Table for Data – Specific Columns

On the other hand, we are still able to query for specific columns.

When we restrict our column set to those filegroups that were included in our restore, things are good.

dbo.ten95C
SQL

select top (10)
 
   tblT.[ten95C_id]
 , tblT.[ten95C_view_cnt]
 , tblT.[ten95C_view_last]

from [dbo].[ten95C] tblT

Image

Summary

From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.

Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.

And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.

SQL Server – Database Restore – Skipping File Groups

Background

In our very last post we touched on stumbling on an interesting error message courtesy of attempting to do a partial database restore.

The post is here.

 

Database Restore

Full Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_INDEX_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_INDEX_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'

Partial Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
	  filegroup  = 'PRIMARY'
	 , filegroup  = 'DATA'
	 , filegroup  = 'FG_FS_01'
	 , filegroup  = 'FG_MOD_01'

 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'
	 , PARTIAL 

 

Reference

  1. Developer Network
    • Database Features > In-Memory OLTP (In-Memory Optimization)  > Backing Up a Database with Memory-Optimized Tables > Piecemeal Restore of Databases With Memory-Optimized Tables
      Link

 

Summary

When scripting a partial database restore, please do the following:

  1. Explicitly list the file groups that you are targeting
    • Where does this clause go
      • Right underneath the name of the target database
      • And, before the with clause
  2. Add the partial clause

 

SQL Server – Database Restore – “Defunct State”

Background

Courtesy of Microsoft, here is another Why take oneself too seriously moment.

Database Layout

Database FileGroups

Database Files

Scripting

Here is a little code snippet from a database restore script that we are developing.

Intent

  1. To potentially conserve storage we chose to
    • Skip restore of certain filegroups
      • In our sample below, we skipped restoring the INDEX Filegroup

SQL


RESTORE DATABASE [DBLABIM_R]
FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIM.bak'
WITH  REPLACE,  STATS = 1,
	 MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIM_R.mdf'
       , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIM_R_Data_01.ndf'
       , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIM_R_log.ldf'
       , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_R_FILESTREAM_01'
       , PARTIAL 

Error Message

Image

Textual


21 percent processed.
42 percent processed.
64 percent processed.
85 percent processed.
100 percent processed.
Processed 328 pages for database 'DBLABIM_R', file 'DBLABIM' on file 1.
Processed 4 pages for database 'DBLABIM_R', file 'DBLABIM_log' on file 1.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_Data_01' of restored database 'DBLABIM_R' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_INDEX_01' of restored database 'DBLABIM_R' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_FILESTREAM_01' of restored database 'DBLABIM_R' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
RESTORE DATABASE ... FILE=<name> successfully processed 332 pages in 0.041 seconds (63.155 MB/sec).

Catch Phrase

  1. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_Data_01’ of restored database ‘DBLABIM_R’ is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
  2. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_INDEX_01’ of restored database ‘DBLABIM_R’ is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
  3. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_FILESTREAM_01’ of restored database ‘DBLABIM_R’ is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.

No Error Message

We changed the originating database recovery state from SIMPLE to FULL, and we are good.

Image

Textual


21 percent processed.
43 percent processed.
64 percent processed.
86 percent processed.
100 percent processed.
Processed 328 pages for database 'DBLABIM_R', file 'DBLABIM' on file 1.
Processed 2 pages for database 'DBLABIM_R', file 'DBLABIM_log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 330 pages in 0.035 seconds (73.451 MB/sec).

Summary

Again, there is not too much to this.

It is Saint Patrick’s day and I got Guinnesses in the cooler.

Trying to understand and sort out Microsoft error messages is good for Mondays, as Sunday is my fun day.

And, that is only two days away.

SQL Server – Database Backup Compression

Introduction

Yearns ago, we deployed Quest LiteSpeed to get backup compression.

We had to use 3rd party software to get backup compression.

But, that was SQL Server v2005.

 

Built-In

Beginning with v2008 backup compression has been built in.

 

Metadata

msdb.dbo.backupset

Wanted to get a feeling as to savings, and here is what the code looks like.

 

code


declare @numberofDays int
declare @dateCurrent datetime
declare @dateGoBack datetime

set @dateCurrent = getdate()
set @numberofDays = 7
set @dateGoBack = dateadd(day, @numberofDays *-1, @dateCurrent)
 

select top 3000
		 
		  tblBS.[database_name]

		, [startTime]
			= convert(varchar(30), tblBS.[backup_start_date], 100)

		, [finishTime]
			= convert(varchar(30), tblBS.[backup_finish_date], 100)

		, [durationInMins]
			= datediff(minute
						,tblBS.[backup_start_date]
						, tblBS.[backup_finish_date]
					  ) 

		, [backupSizeInMB]
			 = ( tblBS.[backup_size] / ( 1024 * 1024 ) )

		, [compressBackupSizeInMB] 
			= ( tblBS.[compressed_backup_size] / ( 1024 * 1024 ) )

		, [ratio]
			= cast
				(
					( tblBS.[compressed_backup_size]
						/ tblBS.[backup_size]
                    ) 
					as decimal(10, 2)
				)

		, tblBS.[type]

		, tblBS.[flags]

		, [hasBackupChecksumUsingSpecificColumn]
			= case ( tblBS.[has_backup_checksums])
					when 0 then 'No'
					else 'Yes'
			  end
	
		, [hasBackupChecksumUsingColumnFlags]
			= case ( tblBS.[flags] & (2 ^16) ) 
					when 16 then 'Yes'
					else 'No'
			  end		
		--, tblBS.*

from   msdb.[dbo].backupset tblBS

where  tblBS.[type] in ( 'D' )

and    tblBS.[backup_start_date] >= @dateGoBack 

and     tblBS.[database_name] in ( 'xmaster', 'ppsivr')

order by
		  tblBS.[database_name] asc
		, tblBS.[backup_set_id] asc

 

 

Output

backupupset-20161210-1241am

 

Explanation

  1. With MS SQL Server
    • Default is set to Backup Compression
      • With backup compression our file size is 25%
    • Requested Backup Checksum
      • Getting backup checksum
  2. With 3rd party software
    • No space savings
    • No Checksum validation
  3. Because we are testing the 3rd party tool, we are logging two full backups each day
    • At 6:30 PM, SQL Server backup
    • At 8 PM, the 3rd party backup
  4. Time Taken
    • Backing up our 8 GB database is taken about 3 minutes with compression
    • Without compression it is taking 1 minute

 

msdb.dbo.backupset – Column – Flags

One of the more interesting columns in the msdb.dbo.backupset table is the Flags column.

Though it is now deprecated, it holds some useful nuggets.

BTW, the information it contains is now exposed in individual bit columns.

Nevertheless, here is what each bit represents

 Flag Bits Bit
 has_bulk_logged_data 0
 is_snapshot  1
 is_readonly  2
 is_single_user  3
 has_backup_checksums  4
 is_damaged
 begins_log_chain
 has_incomplete_metadata
 is_force_offline
 is_copy_only

 

 

Using bit arithmetic, has shown in the SQL code above, one is able to decipher the Flags column.

 

Checksum

Configuration

Trace Flag

  1. 3023
    • If you are using backup applications or utilities that do not expose these options, you can enable the CHECKSUM option by using Trace Flag 3023 on the instance of Microsoft SQL Server.
      If Trace Flag 3023 is turned on, the CHECKSUM option is automatically enabled for the BACKUP command.
      If you do this, you do not have to rewrite all the existing backup scripts.

Query


dbcc tracestatus with no_infomsgs

Instance

Starting with v2014, MSFT added an option through  sp_configure that can be used to setup the default checksum setting.

The option name is “backup checksum default”.

Query


exec sp_configure 'show advanced options', 1
reconfigure with override


exec sp_configure 'backup checksum default'

 

Instrumentation

To measure the impact of various backup offerings ( native versus third party) and options, we can use use the metrics listed below:

Perfmon

Courtesy of MSFT

  1. Windows I/O performance counters, such as the physical-disk counters
  2. The Device Throughput Bytes/sec counter of the SQLServer:Backup Device object
  3. The Backup/Restore Throughput/sec counter of the SQLServer:Databases object

 

 

References

Metadata

  1. Transact-SQL Reference (Database Engine) System Tables (Transact-SQL)  Backup and Restore Tables (Transact-SQL) backupset
    Link

 

MSDN

  1. Database Engine Features and Tasks>  Database Features  > BackUp and Restore of SQL Server Databases > Backup Compression
    Link

 

MSFT – Support

  1. How to enable the CHECKSUM option if backup utilities do not expose the option
    Link

 

Bloggers

  1. Erin Stellato
    • Trending Database Growth From Backups
      Link
  2. Xu James
    • trace flag for backup and restore operation
      Link
  3. Scott Caldwell, Remote DBA Experts
    • UNDOCUMENTED TRACE FLAGS: INSIDE THE RESTORE PROCESS
      Link
  4. Jugal Shah
    • T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio
      Link