THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Argenis Fernandez

Hack-Attaching a SQL Server database with NORECOVERY

Let me just start this post with a huge disclaimer/warning: what I'm about to show you in this post is NOT SUPPORTED by Microsoft at all. It's a hack, and it might not work whatsoever in your environment.

But I think it's pretty cool.

You might have probably heard about hack-attaching a database. It's a very good thing to know, and a powerful tool when you need it. That's not supported by Microsoft either (technically speaking), but I've seen folks use it. I may or may not have used it more than once :)

Whenever you attach a database to SQL Server, crash recovery is executed against it - and it is always brought online automatically. Requests from the community - and vendors, even - to Microsoft to implement a means to attach a database with NORECOVERY (such that you can later apply transaction log backups to it) have gone nowhere - closed as "Won't fix" in Connect.  Some folks have blogged on their approach to how to do this, but I have found myself with mostly suspect or plain unusable databases while trying them.

In this post I will detail the steps that worked for me to make this possible - and even initializing an AlwaysOn Availability Groups replica leveraging a hack-attached database with NORECOVERY.

In a Pure Storage-hosted post I used StackOverflow's publicly-accessible database to discuss dedupe and compression in our arrays - in this post I use that database again.

To get started, I logged on to CSLAB-SQL00, and I took a VSS snapshot of the volume where the data and log files for this database live. Please note that you don't really need VSS for certain storage arrays, like Pure Storage FlashArrays, which always generates crash-consistent snaps and copies. Since a Pure Storage array is all I have access to, this is what I will use for the purposes of this blog post, but I wanted to use VSS to keep the process generic and applicable to other storage arrays.

VSS_Snap_CSLAB-SQL00-StackOverflow

Please note that the failed COM call shown above can be safely ignored, per this Microsoft KB article.

Using the FlashArray GUI, I create a volume from the snapshot:

Copy-Snapshot-To-Volume

The GUI asks for a name for the new volume, so I picked one:

Copy-Snapshot-Provide-Name

Now let's present the new volume to CSLAB-SQL01, a different server attached to the same array. In this case I could also leverage the array's replication capabilities to have a snapshot, possibly of a protection group if your data and log files live on a separate volume, replicated to another array.

Presenting a volume to a host using the GUI couldn't be easier. Click on the gear icon, choose Connect Hosts and select the host you want. Then click Confirm, and off you go.

Connect-Host-Select-Host

I now log on to CSLAB-SQL01, and issue a Rescan Disks on Disk Management. Keep in mind that all of this could be scripted using Pure's Powershell SDK. This guy pops up on the list:

New-Windows-Volume-Offline

And now I use some DISKPART goodness to bring the volume online, and with a new disk signature, which is needed here only because CSLAB-SQL00 and CSLAB-SQL01 are nodes of the same Windows Server Failover Cluster (WSFC), and clear out some attributes:

Diskpart-online-disk

If you're wondering where I got that GUID from, I executed [GUID]::NewGuid() on another Powershell window. You could also execute SELECT NEWID() on any SQL Server instance to get a new GUID.

I mounted the new volume on X:\SqlData2 then granted NT SERVICE\MSSQLSERVER full control on the files.

icacls

To confirm that SQL Server can access the files, I issued a convenient DBCC CHECKPRIMARYFILE:

DBCC-CHECKPRIMARYFILE

Much like in the traditional hack-attach approach, we create an empty database on the target instance. This is going to be on the X:\SqlData mount point/volume. You will need to make sure that file names and database file IDs match, otherwise you might not get things to work.

If you have Instant File Initialization (IFI) enabled, and you use a FlashArray volume to host this empty database, it will take very little space -- and it will only take a few seconds to create. In my test machine, this took about 650Kb of actual disk space and less than 2 seconds to complete.

I then took a full backup of the empty database (to FILE='nul', of course) and a tail of the log backup, to bring the empty database to Restoring status:

Create-empty-database

Now, we stop SQL Server and replace the volume mounted on X:\SqlData with the one that was mounted on X:\SqlData2, and start SQL Server again.

If you try to bring the database to STANDBY/Read-only, you should get something similar to this:

Standby-fail

But CONTINUE_AFTER_ERROR comes to the rescue:

Standby-success

Now pay attention to the message that got cropped on my screenshot above: if you use Service Broker with your original database, you will have to enable it later.

We now have a database in STANDBY. Could we possibly restore some transaction log backups on top of it? You bet! I went to the source database, and created a dummy table with 1000 rows, then took a transaction log backup:

New-table-tlog-backup

I copied the transaction log backup file to the target host (CSLAB-SQL01). This is what I got when I tried to restore it -- same error as when I put the database in STANDBY, basically:

Restore-tlog-fail

Yep, CONTINUE_AFTER_ERROR to the rescue, again:

Restore-tlog-success-new2

The database is now in NORECOVERY! To confirm I have a good database, I put the database in STANDBY again, then proceed to query the dummy table:

1000-rows-new

1000 rows there, so it looks like all is well. But is my database COMPLETELY clean?

Clean-Checkdb

A clean CHECKDB -- music to my ears.

Now you might be wondering: can I use this hack-attached database with NORECOVERY to join this database to an availability group? Yes. Can I make it a mirroring partner? Yup! How about a log shipping secondary? That too. Can this help you in case of "Oops, I dropped a table in production!"? You bet.

Some of you be thinking "Hey, can't I just use a VSS requester app like Commvault, Netbackup or Catalogic to do this?" Of course you can.  This post is targeting those of you who don't have that choice.

To recap: I went from a SAN snapshot to point-in-time recovery in a couple minutes. I think that's pretty cool. But remember, this stuff isn't supported!

 Thanks for reading, 

-A

Published Tuesday, January 24, 2017 8:27 AM by Argenis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bill said:

This is not "supported" in the sense that SQL Server engine developers cannot (thus will not, thus "won't "fix") to write code will allows this to happen. That prohibition is in part due to dependencies that the engine developers will not know. One example is the Service Broker error, and another example might be the Logreader. Another reason is that ye be playing with dragons (on a test server) and yet methinks many DBAs are no more than a knave. On the other hand, Microsoft Support might _need_ to play with these dragons (for example, if a MS bug is hit and the backups are also  toasted).

It may be interesting to peek at DBCC DBTABLE() and sys.database_recovery_status between steps.

January 30, 2017 9:32 AM
 

Argenis said:

@Bill good points.

January 30, 2017 5:33 PM
 

Alex Friedman said:

Neat!

January 31, 2017 2:43 AM
 

Pei Zhu said:

Great stuff. Could you explain content in sqldata_snap_diskshadow.txt?

January 31, 2017 2:17 PM
 

Argenis said:

@Pei, that's the diskshadow list of commands. You can see them on the output "-> RESET", etc.

January 31, 2017 2:34 PM
 

Sandeep said:

Good stuff, I tried in SQL Server 2012 and I was not able to get the DB in standby mode (even with CONTINUE_AFTER_ERROR I get the previous error)  Which version of SQL Server are you using? Also, can you please share the output of

select * from sys.database_recovery_status where database_id = <database_id_of_StackOverflow>after you start the SQL Server Service (after replacing the volume)

Thanks

May 22, 2017 4:02 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement