July 1, 2019 at 9:08 am
I'm setting up monthly automated restore tests on our database estate.
we ordered a small server with a huge amount of storage so that the most recent backup in any given period could be restored to a single location and we produce a report of any failures.
Our target server for the restores is SQL 2017
one of the databases from a SQL 2016 server is throwing me errors on the restore and I can't figure it out. All of the other databases on that server restore correctly.
the database goes into suspect mode (with no usefull information in the logs)
I've tried setting into emergency mode and then run a dbcc checkdb.. I get the following message
Msg 946, Level 14, State 1, Line 4
Cannot open database 'profile_Restored' version 852. Upgrade the database to the latest version.
but i'm restoring 2016 to 2017.. and the other databases work.
The only thing I can think of is that this database has 3 small (80mb) memory optimised tables.....
Any other ideas?
MVDBA
July 1, 2019 at 1:34 pm
just a normal backup database->restore database scenario
I'm going to try dropping the allocated memory on the target server and see if the issue is "not enough free memory to create an in memory filegroup"
SQL is set to dynamically manage memory, so i'm guessing it's not releasing it for the "pre-sized" filegroup. (which i can't change)
MVDBA
July 1, 2019 at 2:31 pm
Have you restored it manually to see if anything crops up that you didn't expect?
July 1, 2019 at 2:51 pm
yeah... that was my first approach
It actually turns out that it was a memory issue. (it was quite a big database, so it took quite a while to go through multiple restores)
With Memory optimised filegroups, the filegroup is not created in the SQL buffer cache. it also doesn't tell sql to release memory for the filegroup.
I shaved a few GB off the allocated mem on the server that it was being restored to and we saw exactly what was going on.
When we specified the hardware for the DR server we didn't anticipate actually using it in anger...., so we just put 8GB of ram in and a shed load of High speed disks (just so that we could restore 200 databases every month)
I'm actually quite pleased that we found this (hey DR testing is cool!!!), purely on the basis that we found that our developers are using technology without knowing if we can recover (or fail over) - our reserve site is cloud based and would have hit the same issue.
If we had to recover this database, then we would have lost our digital product and 30 acres of print presses for 6-8 hours... not good when your clients are a certain international fast food chain, who are the biggest in the world.
MVDBA
July 1, 2019 at 3:13 pm
Thanks for the update, and nice catch. This would make an interesting article, the story about the restore not working and why, as well as how you caught it. If you're interested in writing it up.
July 2, 2019 at 8:54 am
happy to. Give me a day or 2 and it will be in your inbox ready for you to edit. 🙂
MVDBA
July 4, 2019 at 3:47 am
These are the steps to restore the database from suspect mode:
Turn off the flag and set the database to EMERGENCY
EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY
DBCC CHECKDB (‘database_name’)
Set the database into single user mode
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Run DBCC CHECKDB (Take a complete backup before executing it)
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
Set the database into the mutli-user mode
ALTER DATABASE database_name SET MULTI_USER
For Error 946:
Internal database version for SQL 2016 is 852 and for SQL Server 2017 is 869.
SQL Database Recovery Expert 🙂
July 4, 2019 at 9:09 am
Hi prettsons
that's cool info for anyone facing the same issue. I did all of those things but at checkdb stage I got the error message I mentioned, as I posted..
in this case it was an issue with memory optimised tables, not your standard "suspect database" scenario
MVDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply