July 14, 2011 at 8:46 am
After restoring from backup, do you usually do any tests of the database other than dbcc?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 14, 2011 at 11:39 am
As a matter of fact, we have jobs that restore daily down to our QA server. We don't do every database, but we do the major ones.
These restores serve as double-duty. Not only do they verify our backups, but they serve as production copies of our data so the developers / dbas can troubleshoot production problems without needed actual access to production. In addition, since the "prod copy" is only a day behind, we seldom need to make emergency restores that interrupt our SDLC processes because the data is so fresh for the error checking.
We use SQL Agent jobs to do the restore. The first step is to drop the old ProdCopy, the second restores it, the third obfuscates private information, the fourth sets all email addresses to a Test email so the customers don't get emails, the fifth resets everyone's permissions (sp_update_users as well as role adds & new perms for the prod copy version), etc.
Does that help?
July 14, 2011 at 12:08 pm
I actually go 2 up from that.
I test the logs as well.
I do full backup and do test restore + checkdb. But then twice a day I do a full run with the latest log backups as well (with checkdb again). This steps allows for garanteed PIT restore which is more important than just 24 hours old restore here.
I just went 1 up from that lately. I keep an older backup that I don't delete and I keep all the logs (I'll try to keep 1 year if I can). Then I can do PIT restore 24 / 7 / for 1 year back. It's all scripted and takes 1-2 minutes per days worth of logs. So worst case scenario it would take maybe 5-6 hours to restore from day 0 backup to the latest PIT before our last backup.
July 14, 2011 at 12:11 pm
July 14, 2011 at 1:08 pm
Mostly I'm looking to know if dbcc is enough to know that the restore of the database is good. Do I need to do rowcount, sums, etc... of different tables to verify or is that a waste of time?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 14, 2011 at 1:28 pm
CheckDB reads all used pages. So if that works then you're good.
The only thing left is to check all the backups (fulls, diffs, logs).
And NO verify only is not enough.
July 14, 2011 at 1:31 pm
Ninja's_RGR'us (7/14/2011)
CheckDB reads all used pages. So if that works then you're good.The only thing left is to check all the backups (fulls, diffs, logs).
And NO verify only is not enough.
Thanks, data only comes into the database once a day and we're doing a FULL every night. Logs are done simply to truncate the log, also nightly. We're going to do a full restore of the database on a test server.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 14, 2011 at 1:39 pm
Then why waste the ressources on the logs? Just move to simple if you don't need PIT recovery.
July 14, 2011 at 1:45 pm
Ninja's_RGR'us (7/14/2011)
Then why waste the ressources on the logs? Just move to simple if you don't need PIT recovery.
Because it is likely that at some point in the near future we'll be going to a system where we update more than once a day and I was trying to plan ahead. Also, don't I have more control over the size of the transaction log this way?
Although we may be moving to something called EMC Networker to do our backups and I have no idea what that'll do.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 14, 2011 at 1:56 pm
Stefan Krzywicki (7/14/2011)
Ninja's_RGR'us (7/14/2011)
Then why waste the ressources on the logs? Just move to simple if you don't need PIT recovery.Because it is likely that at some point in the near future we'll be going to a system where we update more than once a day and I was trying to plan ahead. Also, don't I have more control over the size of the transaction log this way?
Not really. The log will grow during the transaction and stay there after the growth IIRC. The only difference is that you don't have to back it up regularly to keep it in check. Once the growth has happenned once it won't happen again untill you dump more data in.
July 14, 2011 at 2:04 pm
Ninja's_RGR'us (7/14/2011)
Stefan Krzywicki (7/14/2011)
Ninja's_RGR'us (7/14/2011)
Then why waste the ressources on the logs? Just move to simple if you don't need PIT recovery.Because it is likely that at some point in the near future we'll be going to a system where we update more than once a day and I was trying to plan ahead. Also, don't I have more control over the size of the transaction log this way?
Not really. The log will grow during the transaction and stay there after the growth IIRC. The only difference is that you don't have to back it up regularly to keep it in check. Once the growth has happenned once it won't happen again untill you dump more data in.
Right, but with Full, if I know I'm doing some unusual inserts and want to keep the log from growing I can truncate it by doing backups at specific points in-between inserts. With Simple, I'm at the mercy of the system.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 14, 2011 at 2:21 pm
Stefan Krzywicki (7/14/2011)
Ninja's_RGR'us (7/14/2011)
Stefan Krzywicki (7/14/2011)
Ninja's_RGR'us (7/14/2011)
Then why waste the ressources on the logs? Just move to simple if you don't need PIT recovery.Because it is likely that at some point in the near future we'll be going to a system where we update more than once a day and I was trying to plan ahead. Also, don't I have more control over the size of the transaction log this way?
Not really. The log will grow during the transaction and stay there after the growth IIRC. The only difference is that you don't have to back it up regularly to keep it in check. Once the growth has happenned once it won't happen again untill you dump more data in.
Right, but with Full, if I know I'm doing some unusual inserts and want to keep the log from growing I can truncate it by doing backups at specific points in-between inserts. With Simple, I'm at the mercy of the system.
I am probably making this too simple (due to lack of knowledge) but would issuing a CHECKPOINT in SIMPLE mode force a log truncation and be analogous to doing a tlog backup when in FULL?
From here http://technet.microsoft.com/en-us/library/ms189085.aspx
Transaction Log Truncation
If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.
Except when delayed for some reason, log truncation occurs automatically as follows:
- Under the simple recovery model, after a checkpoint.
- Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
</learning>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 14, 2011 at 2:21 pm
sure. Why don't you do what you want??
July 14, 2011 at 2:31 pm
yes you can force a checkpoint if you want but it runs by itself on a regular basis...
July 14, 2011 at 2:39 pm
Ninja's_RGR'us (7/14/2011)
sure. Why don't you do what you want??
Sorry, I'm not sure what you're referring to here.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply