Point In Time Recovery

It is nice to see new features you would have liked in prior versions make it into the current version.  In SQL Server 2012, Point-In-Time recoveries were made available via SQL Server Management Studio (SSMS).  Point-In-Time recoveries had been available via T-SQL previously; however, there is now a way to visual this process.  This post demonstrates how to do this with the 2014 version of SSMS.

Full Recovery Mode

Point-In-Time recoveries require the database to be in full recovery as we are going to be using the log backup to finalize the restore.  In this scenario, I take a full backup nightly and then transactional log backups regularly throughout the day.  We will need the full backup file and ALL the transaction logs taken to the point in time to which we want to recover.  In my case it is just one; however, there could be many–it just depends on your backup strategy and the time of the failure/error.

This post won’t go over the backups themselves, and I just realized I don’t have a post on those.  I will have to link once I set it up.

Restoring A Database

Screen Shot 07-28-14 at 10.06 PMIf the database doesn’t exist, already you would right-click on Databases, then choose Restore Database …


Screen Shot 07-28-14 at 10.13 PMIf we are restoring a copy of the database on the same instance, we can choose Database as the source and then choose the files we wish to restore from the backup history.  In this example, I choose the source of device and choose both the backup file and the transaction log backup, which you can see highlighted in the box.  Clicking on the Timeline button on the restore to line allows us to specify the time to which we want to restore.

Screen Shot 07-28-14 at 10.14 PMI only need to choose Specific date and time and I can see the options available to me.  I can either modify the time field or I can use what looks like a progress bar to slide to the time I want.  As I have chosen the database name on the previous window, all the logic needed to restore my database has been updated and the database will restore to the time I put in the window.

If the database already exists on the server, the steps are similar; however, we will have to break up the restore into two steps.   First, we restore the database and set the recovery to NoRecovery.  We only have the option to choose Tasks->Restore->Transaction Logs when the database is in recovery mode.

There’s Always T-SQL

And we also have the option of restoring the database via T-SQL and while there are lots of options, these seem to work well for me.  This is also done in two steps.  First the Full Restore–and I KNOW you don’t have your databases on the C:\ drive, so please forgive my poor example.  I’ve got kids to feed and can’t afford big servers with lots of disks.

Restore Database RestoreTest
WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MSSQL\DATA\RestoreTest_Data.mdf'
, MOVE 'AdventureWorks2012_Log' TO 'C:\MSSQL\DATA\RestoreTest_log.ldf'

Then the transaction log.  The secret sauce is the STOPAT option.  That’s it.  I hope you won’t have to use it everyday; however, when that error or issue comes your way, with this process you will able to get back to the point you wanted.

FROM DISK = 'C:\Temp\AW.trn'
STOPAT = N'2014-07-28T21:48:47'

Unable To Login to SQL Server Error 18456

I installed SQL Server on a machine the other day, I connected from my desktop, finished my normal server setup routine and life went on. Later, I connected to the machine via Remote Desktop, fired up SSMS and got this error message.

With an accompanying error in the log
Login failed for user ‘MyAccount’. [CLIENT: ]
Error: 18456, Severity: 14, State: 11.

Very strange, I thought to myself. Turns out I had added my account to the local administrator’s group and not created a Login on the SQL Server. (It also appears I missed a step on my server checklist, as I normally remove the Builtin\Administrators group, but who is keeping score?)

I create the Login, and specified the group, and I was good to go.

EXEC sp_addsrvrolemember [Domain\Account], ‘sysadmin';

Failure to calculate the default value

I was installing SQL Server SP1 on a one node cluster (long story) and I got this error “There was a failure to calculate the default value of setting PatchResult.” It turns out when I launched SQLServer2012SP1-KB2674319-x64-ENU.exe, it put the temporary folder–you know xcdswer-wersdf-weradf on a drive in the cluster. The patch restarted the sql server service rendering the drive from which the service pack was trying to install unavailable. I copied the temp folder to the local c:\ drive and I was able to install with no problems.

Finding Active Heap Tables

As part of a review of a system, I look for tables without clustered indexes; however, invariably I will provide the list of tables to a development team and they will say “These are old tables we don’t use anymore”, so I am forced to provide additional statistics about the number of times a heap table is used in the database.  Based on a check found in Brent Ozar’s sp_blitz script, I pull usage information about the heap tables.  If there is no usage, they are not reported.

EXEC dbo.sp_MSforeachdb 'USE [?]; 
      SELECT DB_NAME(), t.name as TableName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_scan
      FROM [?].sys.indexes i 
		INNER JOIN [?].sys.objects o ON i.object_id = o.object_id 
		INNER JOIN [?].sys.tables t ON o.object_id = t.object_id
        INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id 
        INNER JOIN sys.databases sd ON sd.name = ''?'' 
        LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id 
      WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL 
      AND sd.name <> ''tempdb'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';  

Happy Hunting

Installing SQL 2005 Service Packs

Now hopefully no one out there will need this post–especially because SQL Server discontinued support in 2011; however, because I learn something new everyday I feel compelled to put his post up. I came across a server that had automatic updates on and was trying to install SP2 for SQL Server 2005. It would error out and SQL Server would be shutdown. I was trying to install the update for SQL Server and the Analysis Server services.  The error in the log was
Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.
When I tried installing the service pack manually, this error displayed in the GUI.
Service Pack 4 for SQL Server Database Services 2005 ENU (KB2463332)'
could not be installed. Error code 1603.

The resolution was to delete some registry keys, which is still gives me the Hebe-geebees, and these were the keys.

SQL Server Entries

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\AGTGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\FTSGroup

Analysis Services Entry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\ASGroup

After I did this the installation went as expected. Now I just have to upgrade these bad boys.

Remotely Changing your Password

As I often work remotely from client sites and connect via VPN and Remote Desktop services, I am not in the position to run ctrl + alt + delete and change the password.  In order the change the password via a Remote Desktop session, you enter ctrl + alt + End and this will bring up the change password prompt.

Happy Trails.  :)

Creating a Linked Server to Oracle

I was recently asked to set up a linked server to Oracle and this is something I have done in the past.  No problem I thought.  To connect to Oracle you need the Oracle Data Access components and I has asked the Oracle DBA if they were installed.  He said yes and that everything was configured on his end.  I referenced David Browne’s blog for a quick review and ensured the Oracle directory was in the PATH variable.

I then proceeded to create the linked server got the following error–ORA-12514:TNS:listener does not currently know of service requested in connect descriptor

Oracle_HomeThere ended up being two issues–the first being, the TNSNames.ora file did not contain the entry for the database I was trying to connect to.  I guess the Oracle DBA didn’t think that was part of his setup.  Secondly, for some reason, the Oracle_Home variable was not set–it was blank.  I am not sure how that is possible; however, once I put the correct path in, I was golden and off and running.

Dropping a publisher when the distributor is down

Working on a new server, I came across a replication process that had been forgotten about and the pieces were disconnected.  The distributor server/process had been installed on another server and when I went to remove the publisher from the database, I got the below error.

DECLARE @publication AS sysname;
SET @publication = N'SmokeTest';

-- Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication;

OLE DB provider “SQLNCLI10″ for linked server “repl_distributor” returned message “Login timeout expired”.OLE DB provider “SQLNCLI10″ for linked server “repl_distributor” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.

The linked server was pointing to a server that no longer existed and I thought I was stuck.  I tried lots of options, but ultimately came across this blog that demonstrated how to forcefully remove the publisher when I couldn’t connect to the distributor.

Spoiler: The trick is to use the @ignore_distributor=1 parameter and you should be good to go.

DECLARE @publication AS sysname;
SET @publication = N'SmokeTest';

-- Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication<code>

You can’t shrink a logfile when . . .

I was asked to look at a database server for the first time.  One of the tasks I was given was to shrink the log file of a database as the previous owner could not.  The DB was only 10 MB, but the log file was 50GB.  No problem, I say to myself–like taking candy from a baby.  This baby; however, was trained in the art of MakeMeCrazy.

Most of the advice out there prompts you to find the root cause of the growth as the log is likely to grow again so shrinking the log file is not a fix, just a band-aid.  I should have pondered this question longer.  This server was running SQL 2008, so the truncate_only option wasn’t available so I changed the recovery model to simple and was then going to shrink the log–only the database was already is simple mode.  I tried shrinking the log and the command executed but did not change the size of the file.

Strange–why would 10 MB DB need a 50 GB log file?  Can you think of a reason?  I admit I was dumbfounded and actually had some strange ideas pop into my head.


The reason??  It was setup as a publisher at one point and had been forgotten about.  I removed the publisher and bam–the log shrunk right down.

Importing or Exporting to Access

I had a client request to export some data to Access 2010 for their downstream use.  I have done this in the past and thought it a no-brainer; however, there is always a little trick–hence the post.  :)

Because I had already installed Office 2010, I assumed the Microsoft Access Database Engine for 2010 would get me what I needed.  Because I was using a new machine, I had to install the 2007 Office System Driver: Data Connectivity Components.  After I did that, I could see the Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I put in the location of the access db, and I was off and running.AccessEngineProvider