AWS-RDS :- Backing up SQL Database

Background

Backing up and restoring databases is one of the common tasks performed by DBAs.

Because of the regularity of the tasks, the DBA often schedules them to periodically occur and walks away.

Outside of occasionally alerts due to storage issues, life is good and fancy free.

 

AWS/RDS

Snapshots

In the Amazon RDS World, snapshots can be configured to auto-occur and life remains drama free.

 

S3 Storage

To facilitate husbandry of databases in-house and general safe-keeping, AWS now supports actual database backups.

Backup

Outline

The steps are:

  1. S3
    • Provision S3
  2. AWS Identity and Access Management IAM Role
    • Setup IAM Role
      • Setup Trust Policy for Native Backup and Restore
      • Setup Permissions Policy for Native Backup and Restore
  3. RDS Instance
    • RDS Instance Option Group
      • Option Groups
        • SQLSERVER_BACKUP_RESTORE

 

Steps

Step – S3

For the sake of brevity we will not touch upon provisioning a new S3.

We will instead focus on switching roles to a predefined role that has access to the S3 bucket.

Need to know
  1. S3 bucket
    • It is customal to have various S3 buckets in an organization
    • Determine the one pre-assigned to the group
    • Understand the folder structure
Permissions

In our original setup, my personal account did not have permission to the S3 bucket.

Permissions was granted to specific roles, and not to individual user’s personal accounts.

And, so if at first you fail to see S3 folders or the ones that you are looking for, please do not be too dissuaded,  as permissions might have been granted to specific roles instead.

 

Step – Identity & Access Management ( IAM )

Just as for S3, Identity and Access Management ( IAM ), is another big area and so we will skip the details here.

There are two areas that are important and have to be satisfied to allow RDS to access S3 buckets.  Those two areas are:

  1. Trust Policy
  2. Permission Policy
Trust Policy
Syntax

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Explanation
  1. Designate that principal rds.amazonaws.com is allowed access to the S3 Service

 

Permission Policy
Syntax
{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}


Explanation
  1. Bucket
    • s3:ListBucket
      • List Bucket
    • s3:GetBucketLocation
      • Get S3 Bucket Location
  2. Bucket and sub-folders
    • s3:GetObjectMetaData
      • Get Object Metadata
    • s3:GetObject
      • Get Object
    • s3:PutObject
      • Put Object
    • s3:ListMultipartUploadParts
      • List Multipart upload parts
    • s3:AbortMultipartUpload
      • Abort Multipart Upload

Step – RDS Instance Option Group – Option Group – SQLSERVER_BACKUP_RESTORE

As said earlier, the option to perform database backup and restore is new to RDS.

The specific option’s name is SQLSERVER_BACKUP_RESTORE.

Need to know
  1. Review the SQL Server Instance
    • Review the Option group
      • Console
        • Sample
          • Region :- US-East-2
            • https://us-east-2.console.aws.amazon.com/rds/home?region=us-east-2#option-groups:

 

Step – Backup

After all that, we are finally at the point where we can backup the database

Metadata

The Stored Procedure that we will using is dbo.rds_backup_database.

Let us review how it should be invoked.

To do so we will use the sp_help command.

Syntax

exec msdb.dbo.sp_help 'dbo.rds_backup_database'

Output

Actual
Syntax

exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
        , @overwrite_S3_backup_file=@overwrite

Sample

declare @dbName sysname
declare @s3arn  sysname
declare @overwriteS3BackupFile int

set @dbName = 'datahub';
set @s3arn = 'arn:aws:s3:::sqlServer/backup/datahub/datahub_20171207_0448PM.bak'
set @overwriteS3BackupFile = 1

-- exec  msdb.dbo.sp_help 'rds_backup_database'
exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
	, @overwrite_S3_backup_file = @overwriteS3BackupFile



Output

Review
Syntax

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName

Sample

declare @dbName sysname

set @dbName = 'datahub';

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName


 

Output

 

References

  1. Amazon / AWS /RDS
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Importing and Exporting SQL Server Databases
      • Importing and Exporting SQL Server Databases
        Link
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Options for the Microsoft SQL Server Database Engine
      • Options for the Microsoft SQL Server Database Engine
        Link

SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

SQL Server – Backup – When offline filegroups exist

Background

A few months we performed a partial restore.

When we tried to backup the partially restored database we ran into potholes.

Issues

Sample SQL and Error Messages

  1. SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’ TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
    • Error Message :- “Msg 3004, Level 16, State 1, Line 1
      The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.”
    • Corrective Measures
      • SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’, READ_WRITE_FILEGROUPS TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
  2. SQL :- “backup database [QA_rbpivr1] TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’
    • Error Message :- Msg 3007, Level 16, State 1, Line 1
      The backup of the file or filegroup “rbpivrLOB_01” is not permitted because it is not online. Container state: “Vacant” (6). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.”
    • Corrective Measures
      • SQL :- “backup database [QA_rbpivr1] FILEGROUP =’PRIMARY’, FILEGROUP =’DATA’, FILEGROUP =’INDEX’, FILEGROUP =’fg_rbpivr1_memory’
        TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’ WITH INIT, FORMAT , STATS=1″

 

Remediation

To backup we have to explicitly specify each filegroup we will like backed up.

And, in doing so, omit the filegroups that are offline.

 

Code

Sample


backup database [QA_rbpivr1]    
FILEGROUP ='PRIMARY'
    , FILEGROUP ='DATA'
    , FILEGROUP ='INDEX'
    , FILEGROUP ='fg_rbpivr1_memory'  
 TO DISK  = 'Z:\SQLBackups\QA_rbpivr1.bak' 
 WITH INIT, FORMAT  , STATS=1

Stored Procedure

 


use [master]
go

if object_id('[dbo].[sp_DBBackupCustomized]') is null
begin

    exec('create procedure [dbo].[sp_DBBackupCustomized] as ')

end
go

alter procedure [dbo].[sp_DBBackupCustomized]
(
      @dbname			sysname
    , @filenameBackup	sysname = null --= 'NUL'
    , @stat				smallint = 1
    , @scriptOnly		bit = 0
    , @debugGUI			bit = 0
)
as
begin

    set nocount on;

    declare @dbid int
    declare @tblFile TABLE
    (

          [id]			int not null identity(1,1)
        , [name]		sysname not null

        , [dataSpaceID] int null

        , [filegroup]	sysname null
                            default ''

        , [type]		int not null
        , [type_desc]	sysname not null

        , [state]		int not null
        , [state_desc]	sysname not null

        , [include]	as 
                        case
                            when (
                                        ( [type] in (0, 2) )
                                    and ( [state] = 0) 
                                 )
                                    then 1
                            else 0
                        end
    )

    declare @tblFileFromDB TABLE
    (
          [id]			int not null identity(1,1)	
        , [name]		sysname
        , [fileid]		int
        , [filename]	sysname 
        , [filegroup]	sysname null
        , [size]		sysname
        , [maxsize]		sysname
        , [growth]		sysname
        , [usage]		sysname
    )

    declare @tblFileGroupFromDB TABLE
    (
          [id]				int not null identity(1,1)
        , [name]			sysname
        , [dataSpaceID]		int
        , [type]			sysname
        , [type_desc]		sysname
    )

    declare @tblFileGroupIncluded TABLE
    (
          [id]		  int not null identity(1,1)
        , [filegroup] sysname not null
    )

    declare @id int
    declare @idMax int

    declare  @cmd nvarchar(255)

    declare  @FORMAT_FILEGROUP_QUERY		nvarchar(4000);
    declare  @FORMAT_BACKUP_CMD			nvarchar(4000);
    declare  @cmdBackup			nvarchar(4000);
    declare  @statAsVarchar     varchar(10)

    declare @fileGroupName		nvarchar(200)
    declare @fileGroupSingle	nvarchar(200)
    declare @fileGroupBuffer	nvarchar(4000)

    declare @fileGroupBufferNotEmpty bit

    declare @CHAR_NEWLINE		varchar(10)
    declare @CHAR_QUOTES_SINGLE	varchar(10)
    declare @CHAR_COMMA			varchar(10)

    DECLARE @pathBackupDefault NVARCHAR(4000) 

    set @CHAR_NEWLINE = char(13) + char(10)
    set @CHAR_QUOTES_SINGLE = ''''
    set @CHAR_COMMA	  = ' , '
        
    set @fileGroupBufferNotEmpty = 0

    set @FORMAT_BACKUP_CMD=
            'backup database [%s] '
                + ' %s ' -- FILEGROUP
                + ' TO DISK '
                + ' = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_NEWLINE
                + ' WITH INIT, FORMAT '
                + ' , STATS=%s'
                
    set @FORMAT_FILEGROUP_QUERY
            = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    set @dbid = db_id(@dbname);

    if (@filenameBackup is null)
    begin

       EXEC master.dbo.xp_instance_regread 
                      N'HKEY_LOCAL_MACHINE'
                    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                    , N'BackupDirectory'
                    , @pathBackupDefault OUTPUT
                    , 'no_output' 

        set @filenameBackup = @pathBackupDefault + '\' + @dbname + '.bak'

    end

    insert into @tblFile
    (

          [name]
        , [dataSpaceID]

        , [type]
        , [type_desc]

        , [state]
        , [state_desc]

    )
    select 
              tblSMF.[name]
            , tblSMF.[data_space_id]

            , tblSMF.[type]
            , tblSMF.[type_desc]

            , tblSMF.[state]	
            , tblSMF.[state_desc]

    from   sys.master_files tblSMF

    where  (

                    --( tblSMF.[state] = 0 )
                    ( tblSMF.[database_id] = @dbid )

           )	

    --has_dbaccess(@dbname)
    --print N' '
    --select @cmd = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    exec master.sys.xp_sprintf
              @cmd output
            , @FORMAT_FILEGROUP_QUERY
            , @dbname

    insert into @tblFileGroupFromDB
    (
          [name]		
        , [dataSpaceID]		
        , [type]	
        , [type_desc]	
    )
    exec (@cmd)

    update tblF
    
    set [filegroup] = isNull(tblFGFDB.[name], '')

    from   @tblFile tblF

    inner join @tblFileGroupFromDB tblFGFDB

            on tblF.[dataSpaceID] = tblFGFDB.[dataSpaceID]

    if (@debugGUI=1)
    begin

        select 
                 [src] = '@tblFile'
                , tblF.*

        from   @tblFile tblF

    end

    set @statAsVarchar = cast(@stat as varchar(10));

    set @id = 1
    set @idMax = ( select max([id]) from @tblFile)

    set @fileGroupBuffer = '';
    set @fileGroupBufferNotEmpty = 0;

    while (@id <= @idMax)
    begin

        set @fileGroupName = null

        select @fileGroupName = tblF.[filegroup]
        from   @tblFile tblF
        where  tblF.id = @id
        and    tblF.[include] = 1
        and    not exists
                    (
                        select 1
                        from   @tblFileGroupIncluded tblFGI
                        where  tblFGI.[filegroup] = tblF.[filegroup]
                    )

        if (@fileGroupName is not null)
        begin

            set @fileGroupSingle = 'FILEGROUP =' 
                                    + @CHAR_QUOTES_SINGLE 
                                    + @fileGroupName 
                                    + @CHAR_QUOTES_SINGLE;
            
            if  (@fileGroupBufferNotEmpty = 1)
            begin

                set @fileGroupBuffer = isNull(@fileGroupBuffer, '') 
                                            + @CHAR_NEWLINE
                                            + @CHAR_COMMA

            end
            else
            begin

                set @fileGroupBuffer = '  ' 
                                        + isNull(@fileGroupBuffer, '') 
                                        + @CHAR_NEWLINE

            end

            set @fileGroupBuffer = @fileGroupBuffer + @fileGroupSingle;

            set @fileGroupBufferNotEmpty = 1

            insert @tblFileGroupIncluded
            (
                [filegroup]
            )
            values
            (
                @fileGroupName
            )

        end

        set @id = @id + 1

    end

    exec master.dbo.xp_sprintf
              @cmdBackup output
            , @FORMAT_BACKUP_CMD
            , @dbname
            , @fileGroupBuffer
            , @filenameBackup
            , @statAsVarchar

    print '@cmdBackup ' + @CHAR_NEWLINE + @cmdBackup

    if (@scriptOnly =0)
    begin

        exec(@cmdBackup)

    end

end

go




Guillaume Fourrat – VSS backup of AlwaysOn Secondaries

Background

Catching up a bit on other people’s posting and wanted to share this one from Guillaume Fourrat.

Guillaume titles it “VSS backup of AlwaysOn Secondaries” and it is here.

 

VSS backup of AlwaysOn Secondaries

 

Hi Everyone,

Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases.

Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our VSS Writer SQLWriter would return FAILED_AT_PREPARE_SNAPSHOT (0x800423f4 – VSS_E_WRITERERROR_NONRETRYABLE).

A copy-only VSS backup (VSS_BT_COPY) would work.

The rationale being the following: a FULL backup is actually updating the target DB (reset of differential bitmap mainly), which is not possible when the DB is read only. Furthermore, because of the failover possibilities introduced by AlwaysOn, the favored option was to use Native SQL Servers backup that could rely on SQL Server variable backup location (http://msdn.microsoft.com/en-us/library/hh245119.aspx) if needed, and be ‘alwayson –aware’.

So that could be the end of the story: against an AlwaysOn Secondary DB, either use Copy_only VSS backups or use native backups.

But of course that wouldn’t make for a very interesting blog post…

Enters HyperV…

Consider the following scenario:

Large Windows HyperV Servers, hosting many HyperV virtual Servers, some of them SQL Servers in Always On architecture.

In short: a Private Cloud.

In this context, the IT usually takes care of the infrastructure at host level, and lets users deal with whatever happens within the VMs. One of the key tasks of IT is to manage backups (eg. for disaster recovery at datacenter level, or to provide restore of single VMs).

And the mainstream way to do that is to take VSS backups of the Host Disk Volumes. Microsoft System Center DPM will do exactly that.

But VSS backups are all about taking backups that are consistent: in ‘standalone’ SQL Server context you may already know all the logic SQLWriter implements to make sure that IO against the Databases that are backed up are frozen during the snapshot operation. So, back to our HyperV context, collecting a point-in-time image of a VHD without bothering with what happens within the VM would be defeating that very purpose right?

So what happens is the following: the VSS backup is propagated to Guest VMs thru HyperV integration services. That propagation hardcodes the backup type to VSS_BT_FULL, and therefore all guest VMs are initiating a VSS backup/snapshot in their own context. The purpose is to make sure that all applications are quiesced within all running VMs at the time we take the snapshot at the host level. This will enable us to generate a consistent backup of running VMs.

But let’s now put this in the context where one of the VMs is running an AlwaysOn secondary DB: you guessed it, it’s not going to work:

clip_image002

The important thing to know here is that the error returned by SQLWriter in VM3 will actually bubble up all the way to the initial VSS backup command at Host level, and will make it fail as a whole.

So we ended up in a situation where the IT infrastructure folks would see their Host backups failing from time to time for an unknown reason, depending on whether one or more of the VM present on the Host Drive being backup up had a secondary AlwaysOn DB! It could be that the AlwaysOn AG spans different HyperV hosts and therefore that the presence of a Secondary DB on a given Host is not something static over time.

Because of the complexity of the whole call chain, and because infrastructure IT operators may not have any visibility (or understanding) of the VM content, you can imagine what kind of troubleshooting challenges this would offer… And even when the situation is understood, well, what do we do? If host level backup must be somehow manually synchronized to the applicative state of Guest VMs, the Private Cloud scenario becomes quite more complicated all of a sudden.

This is the reason why SQL Server 2012 SP2 ships a code change for SQLWriter that will implement the following:

clip_image004

As you can see, SQLWriter now detects this specific situation and changes the backup type to VSS_BT_COPYONLY. This will only happen for VSS_BT_FULL backups against AlwaysOn secondary Dbs. VSS_BT_FULL backups against primary DB will happen without change.

In this case, the VSS backup will now successfully complete in VM3 and the host-level backup success state will no longer be tied to guest VM’s AlwaysOn activity. Private Cloud scenario unlocked!

Important note: the fact that VSS backup of AlwaysOn secondaries now works does not make it the preferred solution to backup SQL Server AlwaysOn architectures. The main purpose of the SP2 change is to avoid a situation where a single SQL Server in a VM fails a complete host-level backup operation that encompassing dozens of VMs.

The resulting backup for the VM hosting SQL should be considered a Disaster Recovery one, where AlwaysOn will be removed at once at restore time, not as a way to rebuild a subset of the nodes for an AlwaysOn Availability group. And for regular databases within the VM, that backup is as good any regular VSS one.

Finally, SQL Server 2012 SP2 only contains a partial fix for this issue. Servers running case-sensitive sort orders will require SQL Server 2012 SP2 Cumulative Update 2.

HTH,

Guillaume Fourrat
SQL Server Escalation Engineer
Microsoft France

Commentary

I really like the post for the reasons listed below:

  1. It is not something about a stupid query that can not be written a different way as it is embedded in the code or generated via an ORM Tool ( Entity Framework, Hibernate )
    • That is, it can be changed and optimized
    • Furthermore, we are not handicapped by what the developer thinks is fanciful and should be upheld by all means
  2. It affects a whole lot of parties
    • 3rd Party tools vendors that utilize VSS to backup SQL Server databases
    • Corporations that do not know or care what VSS is or does, they do not have a DBA and could care less that sql server datafiles can not just be backed up like other files
  3. The Application logs an error
    • The application actually logs the error and it is the hope that support staff will capture & review the errors periodically
  4. MSFT can and has provided a fix
    • MSFT has provided a fix and that means they are aware and tracking it
    • The patch will be brought into the main line code and all future versions will benefit going forward
    • If it breaks in subsequent version, we have every right to open a ticket thru Corporate Support or as a Connect Item
  5. And, above all else this is an actual technical problem
    • It is not a process problem
    • It is not a people problem
    • It is not one handicapped by long institutional or inter-department slight or grievance

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

 

Ola Hallengren – DatabaseBackup – Missing Indexes

Background

I am really high on Ola Hallengren’s Database Utility Scripts and I have wanted to get this down for a while now.

 

Performance

 

Missing Indexes

Missing Indexes can be a drag on system performance.

Unfortunately, there are a couple of missing indexes on the msdb.dbo.backupset table.

 

Code

Programmable

dbo.DatabaseBackup

Code Snippet

 


	DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
	DECLARE @CurrentDatabaseName		sysname
	DECLARE @CurrentDatabaseID int

	DECLARE @Version numeric(18,10)
	DECLARE @AmazonRDS bit

	DECLARE @CurrentDifferentialBaseIsSnapshot bit

	SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.'
                          + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))
                          - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

	SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

	SET @CurrentDatabaseName = db_name()
	SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



	SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
    FROM sys.master_files
    WHERE database_id = @CurrentDatabaseID
    AND [type] = 0
    AND [file_id] = 1

    -- Workaround for a bug in SQL Server 2005
    IF @Version >= 9 AND @Version < 10
    AND EXISTS
    (
           SELECT * FROM sys.master_files 
           WHERE database_id = @CurrentDatabaseID 
           AND [type] = 0 AND [file_id] = 1 
           AND differential_base_lsn IS NOT NULL 
           AND differential_base_guid IS NOT NULL 
           AND differential_base_time IS NULL
    )
    BEGIN
    
          SET @CurrentDifferentialBaseLSN = NULL


    END

    SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
    FROM   msdb.dbo.backupset
    WHERE  database_name = @CurrentDatabaseName
    AND    [type] = 'D'
    AND    checkpoint_lsn = @CurrentDifferentialBaseLSN

 

Missing Index

checkpoint_lsn_20161203_0857pm

 

Index Creation Statement

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [INDX_CheckpointLSN_Type_DatabaseName]
ON [dbo].[backupset] 
(
	  [checkpoint_lsn]
	, [type]
	, [database_name]
)

GO


 

 

Code Snippet – INDX_DBA_DatabaseName_IsDamaged
Query Plan

queryplan_20170216_1226pm

Script
/*
Missing Index Details from sql01.master )
The Query Processor estimates that implementing the following index could improve the query cost by 92.1703%.
*/

/*
USE [msdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[backupset] ([database_name],[is_damaged])
INCLUDE ([database_backup_lsn],[backup_finish_date],[type])
GO
*/

use [msdb]
go

if object_id('[dbo].[backupset]') is not null
begin

	print 'Reviewing [dbo].[backupset] .... '

	/*
		Query:

			declare @CurrentDatabaseName    sysname
			declare @CurrentLatestBackup	datetime
			declare @CurrentDifferentialBaseLSN numeric(20, 1)

			set @CurrentDatabaseName = 'msdb'
			set @CurrentDifferentialBaseLSN = 1

			BEGIN        

				SELECT @CurrentLatestBackup = MAX(backup_finish_date)        
				FROM   msdb.dbo.backupset        
				WHERE (
							   [type] IN('D','I')        
							OR database_backup_lsn < @CurrentDifferentialBaseLSN
					  )        
				AND is_damaged = 0        
				AND database_name = @CurrentDatabaseName

			END

	*/

	/*

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] ([database_name],[is_damaged])
		INCLUDE ([database_backup_lsn],[backup_finish_date],[type])

	*/

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id('[dbo].[backupset]')
		and    tblSI.[name] = 'INDX_DBA_DatabaseName_IsDamaged'
	)
	begin

		print 'Creating Index INDX_DBA_DatabaseName_IsDamaged ...'

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] 
		(
			  [database_name]
			, [is_damaged]
		)
		INCLUDE 
		(
			   [database_backup_lsn]
			 , [backup_finish_date]
			 , [type]
		)
		with
		(
			FILLFACTOR=80
		)
		print 'Created Index INDX_DBA_DatabaseName_IsDamaged'

	end

	print 'Reviewed [dbo].[backupset]'

end

go


Ola Hallengren – Database Maintenance Scripts – Transaction Log Backup not occuring

Background

Made the change discussed here.

Again, the change was to temporary alter our Recovery Mode to SIMPLE and back. In between, shrunk the transaction log files.

 

Issue

Ran the Ola Hallengren’s transaction Log backup step, but nothing is being backed up.

BTW, the Scripts are available here.

 

Trouble Shooting

Let us review the scripts and see where we are failing.

Stored Procedure – dbo.DatabaseBackup

sys.database_recovery_status – Get last_log_backup_lsn

Check sys.database_recovery_status and get last_log_backup_lsn


    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
    BEGIN
      SELECT @CurrentLogLSN = last_log_backup_lsn
      FROM sys.database_recovery_status
      WHERE database_id = @CurrentDatabaseID
    END

Backup Conditions

skipwhenlogbackupandrecoveryissimpleorcurrentloglsn

Explanation

  1. Skip when LogBackup
    • Recovery Is Simple
    • Or CurrentLogLSN is null

 

sys.database_recovery_status

Script


SELECT 
		  tblSDRS.[database_id] 
		, [databaseName] = db_name(tblSDRS.[database_id])
		, tblSDRS.last_log_backup_lsn

FROM sys.database_recovery_status tblSDRS


Output

sys-database_recovery_status

 

Database Backup – Directly

If we try to backup the database directly by issuing our own “backup log <database-name>” statement. i.e.

Code


BACKUP LOG [eiadev] 
TO  DISK = N'Z:\Backups\eiadev_20161027_1208PM.bak' WITH NOFORMAT, NOINIT
,  NAME = N'enr_Log Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Output

GUI

nocurrentbackup

Textual

Msg 4214, Level 16, State 1, Line 7
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 7
BACKUP LOG is terminating abnormally.

Remediation

Take full backups right away or wait till our next scheduled full backup.

Once that occurs, our transaction backups will run to successful completion.

Summary

So basically because we changed the Recovery Mode from FULL to SIMPLE ( and now back to FULL), we can not issue a transaction log backup statement until we take a FULL Backup.

To avoid that error, Olla’s script proactively checks the sys.database_recovery_status dmv.