Backup and Restore Test

            There is a good saying that every DBA should live by: 
“A database is not backed up until it is restored”
               
Recoverability is the bread and butter of a Database Administrator.  You are pushing out all flavors of backups (full, differential, transaction log), but your backups are completely useless unless they can restore to a database with integrity.
I have written a Stored Procedure that automates the process of taking a backup file and restoring the database from it and doing a DBCC CHECKDB to check the logical and physical integrity of all database objects.
Before we dive into the Stored Procedure, there are a few things to know of the current version of this code as this blog post is written:
1.       Currently only full database backups are able to be tested (differentials and transaction log backups to come shortly)
2.       The Stored Procedure resembles more of a Stored Novel, but I decided that as a shipping object from me to you, it made sense to keep the database footprint as small as possible (whether you decide to create this Stored Procedure in master, your DBA database, or wherever you so choose).  Because of this, I did not separate differing logic into multiple Stored Procedures, but feel free to slice, dice, and have fun with the code
3.       This code was written and tested with SQL Server 2008 R2.  Even if you are on this version of SQL Server, test in a testing environment to ensure it works the way you want/expect (and, as always email me at sqlsalt@gmail.comwith issues, or use the Issues portion of GitHub to report bugs to me)
Now onto the fun part:  Because the code is so extensive, you may not have 30 minutes to hash through it all and my explanations.  So, here is the abbreviated pseudo code:
1.       Get the parameters from the backup file
2.       Restore a temporary database from the backup file
3.       Run a DBCC CHECKDB against the temporary database
4.       Report the results to the end-user
5.       Drop the temporary database
Now, for the more extensive explanation.  Take a deep breath; we’re diving into it…
This SP takes 3 parameters (the 3rd is optional):  the first parameter, @backup_filename, is the full path and filename of the backup file to be handled.  @restore_temp_path is going to be a full directory path of where the user will want to have temporary database files stored (by temporary, I mean temporary.  A RESTORE DATABASE shortly followed by a DROP DATABASE.  But I felt it best to give you the full control of everything this code touches).  @backup_set_file_numberis the backup set number on the device.  This will default to 1, as typically you’d just have a backup per device and this would be 1.  Flexibility reigns, so you can choose what you like as a user.
create procedure dbo.sp_backup_restore_test
      @backup_filename nvarchar(1000),
      @restore_temp_path nvarchar(1000),
      @backup_set_file_number int = 1
as
Construct the dynamic SQL commands to gather the necessary information from the backup device.
      select
            @exec_cmd_filelistonly = ‘restore filelistonly from disk = ”’ +
                  @backup_filename + ”’ with file = ‘ +
                  cast(@backup_set_file_number asnvarchar),
            @exec_cmd_headeronly = ‘restore headeronly from disk = ”’ +
                  @backup_filename + ”’ with file = ‘ +
                  cast(@backup_set_file_number as nvarchar)
Create a temporary table to house list of data and log files contained in the backup set.
      create table#restore_filelistonly_result
      (
            LogicalName nvarchar(128) null,
            PhysicalName nvarchar(260) null,
            — … abbreviated for simplicity
      )
      insert into#restore_filelistonly_result
      (
            LogicalName,
            PhysicalName,
            — … abbreviated for simplicity
      )
      exec(@exec_cmd_filelistonly)
Create another temp table to house the header information for the backup set.
      create table#restore_headeronly_result
      (
            BackupName nvarchar(128) null,
            BackupDescription nvarchar(255) null,
            — … abbreviated for simplicity
      )
     
      insert into#restore_headeronly_result
      (
            BackupName,
            BackupDescription,
            — … abbreviated for simplicity
      )
      exec(@exec_cmd_headeronly)
Retrieve the database name from the temp table that stores the result of RESTORE HEADERONLY.
      — get the database name
      select @database_name = DatabaseName
      from#restore_headeronly_result
Append our handy pre-defined suffix in case we are doing the restore test on the same instance as the original database (a workaround for database name conflicts).
      — set the database name as the same with a suffix
      select@database_name += @database_name_suffix
Aggregate to total hard disk space that the restore will take; this is in preparation to make sure that there is enough space on the drive to do the quick RESTORE/DROP.
      — get the drive space required to do a restore
      select @drive_space_required_mb = sum(SizeMB)
      from#restore_filelistonly_result
Now we want to retrieve and store the free space per drive by calling the sys.xp_fixeddrivesExtended Stored Procedure.
      create table#fixed_drives
      (
            drive_name char(3) not null,
            free_space_mb int not null
      )
      insert into#fixed_drives
      execmaster.sys.xp_fixeddrives
Retrieve the available space for the drive that was specified as the root drive for the temp directory parameter.  Also, do a check to make sure that the specified parameter is actually a legitimate and available drive.  The last check is to make sure there is sufficient space on the temporary directory’s drive to house the restore (with a little space buffer in there just for good measure:  Defaults to 1 GB).
      select
            @drive_space_available_mb =free_space_mb
      from #fixed_drives
      where drive_name = left(@restore_temp_path, 1)
     
      — check to make sure the temp path drive exists
      if @drive_space_available_mb is null
            begin
                  raiserror(‘Unrecognized temp path drive’, 16, 1)
                  return 1
            end
     
      — make sure there is enough space on the drive with a 1 GB buffer
      if
            @drive_space_available_mb < 
            @drive_space_required_mb +@drive_space_tempbuffer_mb
            begin
                  raiserror(‘Insufficient temp drive space’, 16,1)
                  return 1
            end
The following code does a few things:  First off, it creates the base of the RESTORE DATABASE command.  Then it uses a cursor to loop through all of the database files.  After all, we can’t assume there’ll only be one data file and one log file.
      select @restore_cmd =
            ‘restore database ‘ + @database_name +
            ‘ from disk = ”’ + @backup_filename +
            ”’ with file = ‘ +
            cast(@backup_set_file_number asnvarchar) + ‘, ‘
     
      — use a cursor to iterate over the database files
          in order to handle an unknown amount of database files
          for the tested backed up database  
      declare file_iterate cursor for
      select
            ‘move ”’ + LogicalName +
            ”’ to ”’ + @restore_temp_path +NewFileName + ”’,’
      from#restore_filelistonly_result
     
      open file_iterate
     
      fetch next
      from file_iterate
      into @move_file
     
      while @@fetch_status= 0
      begin
            select @restore_cmd += @move_file
           
            fetch next
            from file_iterate
            into @move_file
      end
     
      close file_iterate
      deallocate file_iterate
     
      — remove the trailing comma
      select @restore_cmd = left(@restore_cmd, len(@restore_cmd) 1)
As the comment states so well, call the RESTORE DATABASE dynamical SQL and run DBCC CHECKDB on the restored database.
      — restore the database and run DBCC CHECKDB
      exec(@restore_cmd)     
      dbcc checkdb(@database_name)
      with no_infomsgs
What the following code does is utilize the sys.xp_readerrorlog extended stored procedure to retrieve the result of the DBCC CHECKDB command.
      — read the error log and show the DBCC CHECKDB result
      select
            @dbcc_checkdb_logsearch =‘dbcc checkdb (‘ +@database_name + ‘)’
           
      create table#errorlog
      (
            LogDate datetime null,
            ProcessInfo nvarchar(128) null,
            Text nvarchar(2000) null
      )
     
      insert into#errorlog
      (
            LogDate,
            ProcessInfo,
            Text
      )
      exec master.sys.xp_readerrorlog 0, 1, @dbcc_checkdb_logsearch
Display the DBCC CHECKDBresult to the end-user, and then call a DROP DATABASE on our restored database.
      select top1 Text
      from #errorlog
      order byLogDate desc
     
      — drop the test restored database
      exec(‘drop database ‘ +@database_name)
That is the bulk of the code and the logic that was used when writing the T-SQL.  As for Usage, here are a few examples:
— test the backupset that is at position 4
exec dbo.sp_backup_restore_test
      @backup_filename = ‘C:\DatabaseFiles\BackupAndRestoreTest.bak’,
      @restore_temp_path = ‘C:\DatabaseFiles’,
      @backup_set_file_number = 4
go
— test the backupset at the first position
exec dbo.sp_backup_restore_test
      @backup_filename = ‘C:\DatabaseFiles\BackupAndRestoreTest.bak’,
      @restore_temp_path = ‘C:\DatabaseFiles\’
go
Code
                The code for this blog post can be found on my GitHub repository at the following location:
                Test your backups!  If there are any questions, comments, or issues please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Limit Instance Access by Time and Day with a Logon Trigger

                There are many situations in production when you may want to limit user access to SQL Server during off-hour times.  For instance, say a particular login should not be connecting to the database on weekends.  The obvious way to go about this is with a Logon Trigger.  In review, here is the definition of a Logon Trigger straight from MSDN:
                Logon Trigger – Logon Triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
                A Logon Trigger is really just a DDL Trigger, and two popular functions of a Logon Trigger are for auditing and preventing connections.  We are going to focus on the latter with this post in order to limit particular logins from connecting to the instance during certain times of the day or certain days altogether.  The T-SQL code will consist of three objects:  A table to contain the login and deny time parameters, a stored procedure to add the data to that table, and of course the Logon Trigger code.
                Login Deny Time Table – this is the table that will house the times/days per login that they will be denied access.  The Logon Trigger will use this table to reference if the connecting login should be permitted to successfully connect.
use master
go
if object_id(‘server_login_admission’)is not null
      drop tabledbo.server_login_admission
go
create table dbo.server_login_admission
(
      admission_id int identity(1, 1) not null primary key clustered,
      login_name nvarchar(256) not null,
      deny_day int not null
            check (deny_day between 1 and 7),
      deny_time_begin time null,
      deny_time_end time null,
      deny_full_day bit not null default 0
)
go
alter table dbo.server_login_admission
add constraint CK_TimeOrFullDay check
(
      (
            deny_time_begin is not null
            and deny_time_end is not null
      )
      or deny_full_day = 1
)
go
alter table dbo.server_login_admission
add constraint CK_DenyTimeNullHandling check
(
      (
            deny_time_begin is null
            and deny_time_end is null
      ) or
      (
            deny_time_begin is not null
            and deny_time_end is not null
      )
)
go
alter table dbo.server_login_admission
add constraint CK_DenyTimeRelativity check
(
      deny_time_begin <deny_time_end
      or
      (
            deny_time_begin is null
            and deny_time_end is null
      )
)
go
      Notice the multiple CHECK constraints.  These are in place to ensure proper and appropriate data manipulation.
                Add Login Deny Data Stored Procedure – this is the stored procedure that will be called to enter data into the aforementioned table.  It is worth noting that a parameter for this stored procedure is the weekday enumeration.  A quick review is that Sunday = 1, Monday = 2, so on and so forth, Saturday = 7.
use master
go
if object_id(‘dbo.sp_add_server_login_admission’) is not null
      drop proceduredbo.sp_add_server_login_admission
go
create procedure dbo.sp_add_server_login_admission
      @login_name nvarchar(256),
      @deny_day int,
      @deny_time_begin time = null,
      @deny_time_end time = null,
      @deny_full_day bit = 0
as
      set nocounton;
     
      — check to make sure the login actually exists
      if suser_id(@login_name) is null
            begin
                  raiserror
                  (
                        ‘Unknown login name’,
                        16,
                        1
                  )
                  return 1
            end
     
      — make sure the @deny_day is a valid day of the week
      if @deny_day not between 1 and 7
            begin
                  raiserror
                  (
                        ‘Invalid deny day’,
                        16,
                        1
                  )
                  return 1
            end
           
      if
      (
            @deny_time_begin is null
            and @deny_time_end is not null
      ) or
      (
            @deny_time_begin is not null
            and @deny_time_end is null
      )
            begin
                  raiserror
                  (
                        ‘Both deny time parameters must have a value,
                        or both must be null’,
                        16,
                        1
                  )
                  return 1
            end
           
      — ensure @deny_time and @deny_full_day aren’t null and 0
      if @deny_time_begin is null
      and @deny_full_day = 0
            begin
                  raiserror
                  (
                        ‘Deny time cannot be null
                        if login is not denied for a whole day’,
                        16,
                        1
                  )
                  return 1
            end
           
      insert intodbo.server_login_admission
      (
            login_name,
            deny_day,
            deny_time_begin,
            deny_time_end,
            deny_full_day
      )
      values
      (
            @login_name,
            @deny_day,
            @deny_time_begin,
            @deny_time_end,
            @deny_full_day
      )
     
go
      Logon Trigger – and last, but surely not least is the actual Logon Trigger.  As you can see, this code simply does a check on the server_login_admissiontable to see if the connecting login is not denied to connect at the current time of the current day.
use master
go
if exists
(
      select *
      from master.sys.server_triggers
      where name = ‘logon_trigger_deny_by_time’
)
      drop triggerlogon_trigger_deny_by_time
      on allserver
go
create trigger logon_trigger_deny_by_time
on all server
with execute as self
for logon
as
      declare
            @current_login nvarchar(256),
            @current_weekday int,
            @current_time time
           
      select
            @current_login = original_login(),
            @current_weekday = datepart(dw, getdate()),
            @current_time = cast(getdate() as time)
           
      if exists
      (
            select *
            from master.dbo.server_login_admission
            where login_name = @current_login
            and deny_day = @current_weekday
            and
            (
                  @current_time betweendeny_time_begin and deny_time_end
                  or deny_full_day = 1
            )
      )
            begin
                  rollback
            end
go
      Usage – here is some ways you can deny login access:
exec dbo.sp_add_server_login_admission
      @login_name = ‘SomeLogin1’,
      @deny_day = 2,          — deny user access on Mondays
      @deny_full_day = 1      — deny the user for the whole day
go
exec dbo.sp_add_server_login_admission
      @login_name = ‘SomeLogin2’,
      @deny_day = 7,          — deny user access during Saturday
      @deny_time_begin = ’17:00′,
      @deny_time_end = ’23:00′
go
                The above code (provided the Logon Trigger is created and enabled) denies SomeLogin1from connecting to the instance on Monday (throughout the whole day).  It also denies SomeLogin2 from connecting to the instance on Saturday from 5:00pm to 11:00pm.
                Change the code, make it fit your particular environment’s demands, have fun with it.  The above code should give you a great starting point in the adventure to appropriate security in regards to logins and access times.
Source Code:
                The above version-controlled T-SQL can be found on my SQLSalt GitHub repository:
                If you have any questions, problems, or comments please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Wait Stats Explained

                One of the most useful Dynamic Management Views (DMV) is sys.dm_os_wait_stats.  This DMV gives information about the waits that SQL Server runs into when executing processes.  sys.dm_os_wait_statsis a great start to diagnosing potential issues that could be causing performance problems, or worse…failure.  The amount of wait types that are returned by a SELECT * from sys.dm_os_wait_stats query is extremely extensive, if not daunting.  This DMV gives information such as delays from parallelism (CXPACKET), full-text operation synchronization (FULLTEXT GATHERER), all flavors of locks (LCK_XXX), log flush and transaction commits (WRITELOG), and many…many more.
                This is one of those DMVs that you surely need Books Online (BOL) to be handy, as many of these wait types are not easily understood right off the bat by their sometimes-cryptic name.  Sure, you can have MSDN open while you sift through the longer wait stats, but I have whipped up some T-SQL to ease the burden a little.
                The following code does two things:
  1. Creates the base lookup table with the wait stats names as well as BOL descriptions
  2. Creates the stored procedure to execute when you want to see the current wait statistics, as well as their BOL description correlated with them
  3. this stored procedure sorts them by wait time descending, so it’s a handy way to quickly see the egregious offenders
  4. there’s an optional parameter for the stored procedure so you can just get a sampling of the longer wait stats (or leave it empty/NULL to get all wait stats available)   
Base Lookup Table:
use master
go
if object_id(‘master.dbo.wait_stats_explained’) is not null
      drop tabledbo.wait_stats_explained
go
create table dbo.wait_stats_explained
(
      wait_type nvarchar(60) not null,
      wait_description nvarchar(1000) null
)
go
insert into master.dbo.wait_stats_explained
values
(‘ABR’, ‘Identified for …..’),
(‘ASSEMBLY_LOAD’, ‘Occurs during …..’),
(‘ASYNC_DISKPOOL_LOCK’, ‘Occurs when …..’),
(‘ASYNC_IO_COMPLETION’, ‘Occurs when …..’),
(‘ASYNC_NETWORK_IO’, ‘Occurs on network writes …..’)
— …. full definitions and total stats ommitted
—          due to extensive text
—          see BELOW for link to full scripts
Create Stored Procedure Script:
use master
go
if object_id(‘master.dbo.sp_wait_stats_explained’) is not null
      drop proceduredbo.sp_wait_stats_explained
go
create procedure dbo.sp_wait_stats_explained
      @top_amount int = null
as
      set nocounton;
      select top (coalesce(@top_amount, 999999))
            ws.wait_type,
            ws.waiting_tasks_count,
            ws.wait_time_ms,
            ws.max_wait_time_ms,
            ws.signal_wait_time_ms,
            e.wait_description
      from sys.dm_os_wait_stats ws
      left joindbo.wait_stats_explained e
      on ws.wait_type = e.wait_type
      order byws.wait_time_ms desc
go
Usage:
— omit parameters, result set contains all wait stats
exec dbo.sp_wait_stats_explained
go
— retrieve top 10 longest wait stats
exec dbo.sp_wait_stats_explained
      @top_amount = 10
go
               
                As you can see from the above code, it’s simply just a LEFT JOIN so the result set contains a description (if it is available for the particular wait stat) of what that wait stat is and a possible cause.
Source Code
                I am a firm believer in the version control (particularly distributed version control) of all types of code… and that includes Database Administration code.  The power of a DBA isn’t in mouse clicks through a GUI, but through T-SQL, PowerShell, and other forms of well-written code to maintain, secure, recover, and tune databases.
                I am currently using Git on my local machine, and I use GitHub for remote repositories to push my DBA code to.  Not only does this allow for the version control of my code, but it also permits me to share code with you, the viewers of this blog, or any other DBA that would like use the code (Git/GitHub possibilities are endless, much beyond the scope of this blog post).
                I have a repository for all of my DBA code, and this repository can be located here: SQLSalt.  As for the scripts for this blog post, they can be viewed/retrieved at the following locations:
Full Script to Create Base Table: WaitStatsExplained_1_CreateTable.sql
Full Script to Create sp_wait_stats_explained:  WaitStatsExplained_2_CreateProc.sql
                Welcome to the wonderful world of Git!  If you have any questions, recommendations, or comments please feel free to leave a comment or email me at sqlsalt@gmail.com.

A network-related or instance-specific error occurred…

Possibly one of the most common issues I see out there is the following error (yet always very informative):
A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
                Yes, we’ve all seen it before.  And it is an error that likes to rear its ugly head even to the most seasoned Database Administrator or Developer.  Nobody is immune to this problem, and with the correct troubleshooting you will be connected to that instance in no time.  The error text is actually one of the more descriptive errors that I see in SQL Server (in my opinion at least), as it gives an inkling as to what could be the problem, but there is so much more to it than those three sentences.
                So without further ado, here is a great walk-through for things to check when you receive the aforementioned error:
(This list is ordered from the most common causes and easiest solutions, followed by less likely ones)
What kind of instance is this?
                Yes, that is possibly one of the most important questions.  Everyday DBAs and Developers are trying to connect to an instance by only specifying the server name (or IP address) for the Data Sourceportion of the connection string, or directly through SQL Server Management Studio (SSMS).
Just a quick recap on a very basic aspect of SQL Server instances:  There are two types of instances (Default and Named Instance), and you are trying to connect to one or the other.
Default Instance – as the name suggests, it is the default instance for that server and is referenced solely by the server’s name or IP address.  A few other points about the Default Instance is that it is by default hitting port TCP 1433 (although this can be changed), and the instance name is MSSQLSERVER.
Example:  Data Source = MyServerName
Named Instance – whereas the Default Instance is just that, with a default name of MSSQLSERVER, a Named Instance is a SQL Server instance that has…yes…a name.  When trying to connect to a Named Instance, the SQL Server Browser service (running on port UDP 1434) supplies information on connecting to Named Instances.  Note, though, that a Named Instance is referenced by the server name followed by the instance name.
Example:  Data Source = MyServerName\MyInstanceName
                If you are attempting to connect to a Named Instance, only specifying the server name could result in the titled error.
Is SQL Server Browser started? (Named Instances only)
            Connecting to a Named Instance requires going through a SQL Server service: the SQL Server Browser service to be exact.  This service, as mentioned above, runs on port 1434.  If this service is not started, you simply won’t be able to connect to a Named Instance.
                You can check this by going to the SQL Server Configuration Manager (SSCM) and under SQL Server Services ensure that the SQL Server Browser service is started (also, typically set this service to automatically start for Named Instance access).
Is TCP/IP enabled?
                So now you’re on the server, and you open up SQL Server Management Studio.  You can connect to SQL Server no problem.  But for some reason, you can’t connect to SQL Server from any other machine.  What gives?  Most likely you need to enable the TCP/IP Protocol for the instance that you are trying to connect to.  SSMS connects through the Shared Memory Protocol if it is local to the instance, which would give reason for the initial findings.
                This is a big one after you install SQL Server Express edition.  By default, TCP/IP is disabled causing remote connection attempts to fail.
Is your firewall blocking the necessary ports?
            As written above, the Default Instance is connected through port TCP 1433, and the SQL Server Browser is connected through UDP 1434 and sends information about Named Instances and their corresponding ports.
                An application I like to test for port access is called PortQry.  You can use PortQry to the information of the necessary ports in order to connect to your SQL Server instance.
                As shown above, there are a few things to check out during the troubleshooting of a very popular error when trying to connect to SQL Server.  By going down through this checklist, you will be able to narrow down and correct this problem in no time.  For any questions, comments, or any other correspondence, feel free to leave a comment or email sqlsalt@gmail.com.