It’s been awhile since I’ve written, but with summer coming to a close, I’m feeling the itch – and what better to start the season with than a problem that does not seem to have a clear solution:
Let’s say you want to move the physical files in a log shipping secondary that is in Standby mode. You might think that a quick detach, move the files, reattach and it’s coffee time, right? Well, no… You’ll start by doing the detach, which SQL server will let you do, you’ll get everything where you want it, then for the big finish you’ll run attach – and you’ll probably be quite surprised to get:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “<your MDF file>”. Operating system error 5: “5(Access is denied.)”.
And you might be quite befuddled – and rightly so – you’ll see that the permissions on your files have been reset, so you correct them, but now you get:
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
And there doesn’t really seem to be anything you can do about that – panic might set in as you realize your DR database now appears to be a large, unusable pile of bits and you need to start log shipping all over again from your 3TB backup.
Note that if your database was in NORECOVERY mode (not standby), the UI would not give you the option to detach, but sp_detach_db will work just fine – as will the create for attach, but recovery will run and you will no longer be able to restore logs – you’ll definitely have to start with a full backup if you do this.
All is not lost, however. The database can be “hacked back in” by creating a placeholder database that is in the same standby state, then switching the files while the DB is offline. This is not exactly outlined in BOL, and it’s called “hacking in” because this is a hack – this is likely not a supported process, but I’ve seen some very well respected industry experts suggest it on an as-necessary basis – see Paul Randal’s post on it here. Note that Paul recommends that the dummy database files be roughly the same size as the database you are hacking in – I’ve seen it work without this, but Paul knows much more than I do… The steps are as follows:
1) You should have at least 3 files: the database file (MDF), the log file (LDF) and the undo/standby file (BAK by default). Since you’ve already detached them, you’ll have to find them by whatever means you have at your disposal. If you can’t find them, then perhaps you’re the wrong person to be doing this… Place them where you want them to be, but rename them.
2) Restore a database (any user database) with standby – the only requirement (Edit: see Ben’s comment below about file ids) is that it have the same number and type of files as your main database (again, Paul suggest that they be of similar size as well) – the logical names don’t even have to match – you’ll need to name the database itself what you want your attached database to be named, and place the log, data and undo files wherever you want them to be when you’re done. It doesn’t matter what the contents of this database are as they will be overwritten – if you don’t have any small backups handy, either take a copy-only backup of a small database or create an empty database and take a backup of it
3) Other than the contents of course, this database should now look exactly like you want your attached database to look – it should be in standby mode with all the files in the right place and named the right names
4) Take the database offline – note that this works for standby databases – you don’t have to affect the availability of the rest of your server – but for NORECOVERY databases, you’ll have to bring down the service
5) Rename the files and put your files from step 1 in their place. Check the file permissions as they may have been reset in the detach
6) Bring the database online
7) Assuming everything works, delete the renamed files from the placeholder database
That should do it. I’ve tested it a number of ways on SQL 2008, and in a limited fashion on SQL 2005. I highly recommend doing this with some scratch databases to familiarize yourself with the process before trying it on production.
Note that if all you want to do is get a copy of the database so that you can do something else with it and you don’t actually want to move it, you can simply set it offline, make copies of the files, bring it back online and hack the copies into another location.
Hope this helps.
I had a problem with bringing the database back online after copying over the database files. The main error message I got was 5171 – “mdf is not a primary database file”.
While the placeholder files have to have the same name and path, they don’t need to be similar size in SQL 2005+, they can be small. But the placeholder database files need to have the same FileID’s as the source database you’re copying over. This comes into play if you’ve ever shrunk a db or file by adding files, spread data across multiple drive letters, etc. Since the FileID’s can’t be assigned, in the placeholder database, you will need to run ALTER DATABASE dbname ADD FILE (filespec) and ALTER DATABASE dbname REMOVE FILE name until each placeholder database file has the same FileID as the source database you are copying over.
Thanks, Ben – I have not personally run into this situation, but it certainly seems reasonable
I had this issue and all the solutions online was kind of misleading to my issue. I have the solution here.
operating system error 5 5(access is denied) Solution
The solution was to Run SSMS as Administrator.
It is UNBELIEVABLE that microsoft keeps removing functionality from prior versions. This worked in earlier versions. Now that I am trying to move my log shipping standby (5 1/2 TERABYTES thank you) to a new awesome server, then I will catch it up and turn it into the primary, they take away this functionality. Of course, this database is set up properly across numerous files, indexes on separate Luns, full text on 2 separate luns, table split across Luns, and 3 log files, this “hack” is going to be a messy one. A backup restore is 6 days to accomplish, so out of the question. MICROSOFT, wake up. I have supported and pushed for SQL Server for many years, and you kick me in the nuts every “upgrade”. Almost makes me wish I had just relented and went with ORACLE.
I had a problem running restoring a log after running through these steps. I was able to successfully reattach the DB but then I got this
SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
SQL error 3624: SQL error 3624: A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC
CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
SQL error 3624: SQL error 3624: Location: warmstan.cpp:633
Expression: m_recoveryUnit->IsInRestore ()
SPID: 56
Process ID: 8940
SQL Backup exit code: 790
SQL error code: 3624
Thankfully, I had copied the .mdf & .ldf file over before I performed the detach and then reattached with these copies (instead of the detached files). After I did this, the restore log started working again. Perhaps there were permission issues with the files.
In any case, heart attack avoided. Thank you for posting this blog.
You may be a seasoned DBA right now at 2AM wondering if this is possible because everything you’ve tried tells you that it is not. Well, it is possible. But, it can get much much more sticky and complicated than this post lets on and you might want to give up. Here’s some tips, because it makes me dizzy to remember everything I needed to do to make this work, but here it is:
If you are trying to automate this (I read your mind), you need to be aware that SQL Server creates a file with TUF extension that magically disappears when you recover your hacked in database. So, you can’t repeat this process you are smart and backup the tuf file. I suggest when you restore the log in standby, that you use a location that you will periodically backup the TUF file.
Also, the notes about file ids is especially relevant with many many files and filegroups. I had that situation and I can attest that you need to make sure every single file and file group is exactly the same in the hacked source files. While it is true the content in the database is irrelevant, you will have a 5hit-5how trying to make it work. It will barf and die on you for a thousand different reasons. Just make sure location, filename, filesize, filegroup, everything, is exactly intact. It may help to note that inside the database itself, a standby database thinks it’s still on the primary host! That means that even after you restore a database in standby to different directories than the primary had, if you look at sp_helpfile, you’ll note that it still thinks it is installed on those directories. That kinda helps explain why the file groups, names, ids have to match or it will barf.
I have successfully automated it where I log ship a 1TB database, offline it at 2AM, hack it into a separate database, bring it back online, and continue log restores. The freshly hacked in copy is brought online with recovery and I am able to olap-ify it. Happens every day with mostly no issues except when log chain breaks 🙂
woops, forgot to mention how to get those fileids in the right order. You guessed it, when you create the dummy database that will be hacked-in to, you must have the files created in the proper order. Simple! 😉
One more thing. Because the dummy database needs correct ids and it is very difficult to get it right. You should create a create database template for your dummy database so the file ids are right. 🙂
But how do you ensure fileid order? Ahah, in the script you need to create the database appending ALTER DATABASE ADD FILE/FILEGROUP operations in order of file ids. That’s fun isn’t it?
избавиться страха
[…]Nice blog here! Also your website a lot up fast![…]
тревоги постоянной
[…]you made running a blog glance[…]
внутреннего тревоги
[…]you made running a blog glance[…]
советы избавиться
[…]Great weblog right here! Additionally your website rather a lot up very fast![…]