I have a requirement whereby testers on ServerA need a copy of the production database from ServerB which is refreshed on a two-day basis. The source database is nearly 400Gb and is in simple recovery mode. Full backup is made on a weekly basis with differential on the remaining days.
At the moment I'm doing the full+diff restore every other day to refresh the test database but it's taking quite a while given the size of the database and it got me wondering: Is there a way to snapshot (for example) from a database in the RESTORING state so that I can keep applying differentials to my RESTORING database but also have a secondary copy which the testers can access?
Can't readily think of a way but thought I'd throw it out there. Thanks.
Nope.
A restoring database is in the process of moving all the data pages and then recovering any transactions. They just can't be accessed. At all.
To solve this, I'd look outside SQL Server. Does your SAN or storage system offer some type of snapshot? That may be a way to get around this. They can be extremely fast. However, they also come with a cost. Monetary & set up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2021 at 1:00 pm
Thanks Grant. Yeah, that's what I thought re: a restoring DB.
I'll see if there are any infrastructure-level options available to us.
Thanks again.
January 26, 2021 at 1:15 pm
As far as I know, you cannot access a "RESTORING" database for reads or writes, and this makes sense.
While a database is in the RESTORING state, SQL is waiting for it to be "restored". So lets say user A is the user doing the restore. They just finished the FULL restore and are moving onto a differential or log restore. BUT before they start, a user executes a large READ on the database that locks the table. Your restore of the differential/log now needs to wait for that SELECT to finish before it can update data. Now a worse case, what happens on a deadlock? how would you handle that? What if in that differential the table the user was SELECTing from was dropped?
Now not allowing writes is easier to describe when you think about differential backups - if the data changed before the differential was applied, the differential might not be able to be applied to the row.
As long as a database is in the RESTORING state, all you can do is stop restoring and put it into read/write mode OR continue restoring from backups.
Now as for creating a snapshot of a database in the RESTORING state, I don't think that is possible. Once the database has FINISHED being restored, you can snapshot it, but I don't think that will help with performance of the restores.
Now, when you say "quite a while given the size of the database" to do a restore, how long is "a while"? When I restore my 400 GB database along with a few others from a full plus a lot of logs (full is nightly at midnight, logs are hourly on the hour), I can do a restore of it at 3:00 PM in about 15 minutes which includes running some post-refresh scripts for data sanitizing. And that isn't even on the fastest disk that IT can offer me. If we were to go up to the fastest disk, I expect that time to be closer to 5 mintues.
Is it possible to automate the restores to happen in the middle of the night and then the testers would be able to use it when they get in?
also, I would never use Simple recovery model for a production level database unless I was Ok with some data loss. For example, a data warehouse where I could re-create all of the data from the source tables. Any data that changed between your differential backups could be lost. Our company has decided that 1 hour of data loss is acceptable, so we do hourly log backups. And with nightly Full backups, our restore times on the largest system is "fast enough" for the company. Basically, the company accepts the risk and the downtime that will come from our current backup plan. One HUGE advantage of the log backup is that you get point in time restores. This is not possible with full + differential backup strategy. What I mean by point in time is if I want to restore data up to 3:17:02 PM, I can do that with a log backup. With full + differential backup strategy, the best you can do is up to the most recent backup.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 26, 2021 at 3:04 pm
How large is the log file? The disk area for a log file must be preformatted, whereas data files do not (assuming you have IFI activated, which is standard now).
Do you restore over an existing db or to new file names? It might be faster to restore over an existing db with an equal log size to prevent that preformatting from having to occur. Just a thought, not sure if it will effect your restore time, just thought it might be worth mentioning.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 26, 2021 at 4:19 pm
Thanks for the feedback. Yes, the initial restore was greatly slower as the files were initialised but the regular refresh is obviously using pre-existing files so there's no speed improvements to be gained there.
Thanks anyway
January 26, 2021 at 4:40 pm
Do you pre-restore the full backup each time to speed up the next restore? (Assuming you have the disk space available -- and if not, you can get the space :-)).
That is, you have two dbs, the maindb db and maindb_restore (or whatever name).
When the new full backup comes in, you restore it to maindb_restore. Once it's ready, you rename the original db to maindb_old (or whatever name) and rename the maindb_restore to maindb.
Then you immediately restore the full backup to maindb_old WITH NORECOVERY. When a new diff comes in, you apply that diff to maindb_old WITH RECOVERY (*). Once it's complete, you rename the original db to maindb_whatever and rename the maindb_old to maindb. Then immediately restore the full again to the "spare" db to do the same thing again when the next diff comes in. That means the recovery time for any new diff is just the time to apply the diff, a relatively fast process.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply