Reattach a database that was previously in Standby

Ok. I did this without knowing that you
couldn't reattach a DB that was previously in stand by

Because this article exists, I assume others have found
this out too.

(I did not write this. I did not get permission. I am
merely keeping this handy to me since no one is probably
going to my site anyways and this saved me……)

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.

http://mikedefehr.com/2010/08/31/how-to-attach-a-database-in-standby/

Moving TempDB that goes bad/SQLCMD from Command Line

I had a situation whereby I ran the tsql to move the tempdb because the data and logs for it were on the same drive as the databases which caused us space issues. Unbeknownst to me--as i didn't check--the tempdb was set at triple what our new drive was.

The dilemna that I ran into is that I could not start MSSQLSERVER service since there was no tempdb. "Error code 1814: Could not create tempdb. You may not have enough disk space available. " after having altered the path while it was running

Also, with the param to start SQL without tempdb, "/f", i would get the message "Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'MyUser'. Reason: Server is in single user mode. Only one administrator can connect at this time.."

With that said, I had to put the DB in single-user-mode [and be able to connect to it] and work through the command line to accomplish the task of relinking tempdb back properly

I had to start the DB in Single user mode, allowing the SQLCMD program. I actually had to manually stop the SQL service and change the service user to my account.

Change the tempdb back to its original location

Start SQL back up

Go into tempdb's file properties

Lower the initial start size down to a reasonable amount for the drive

Change tempdb back to its new home

Ensure that the destination for the mdb and ldf has user access permissions. The service account has to be able to read/write in the new locations.

Put back the SQL service account and Restart SQL