October 17, 2016 at 3:01 am
Dear all,
how do I know if the backup files are ready to restore without any corrupt ?
How to make sure if the restoration will success ?
October 17, 2016 at 3:24 am
The only way you can know is by actually restoring the databases. You can give yourself a headstart by using the CHECKSUM option in your backups, and doing RESTORE VERIFYONLY immediately after the backups. That doesn't guarantee that the backups won't be corrupt, though. You need to do regular test restores to have confidence that when you come to do it for real, your restores will be successful.
John
October 17, 2016 at 3:28 am
Firstly, if you do your backup with the checksum option, this will identify various forms of potential corruption. Restore with verifyonly will also identify a range of issues with your backups.
Having said that, realistically, when you've done a test restore and verified it with DBCC CheckDB, then you can be reasonably confident.
Your database may be fine, your storage holding your DB files may be fine, but dodgy storage on your remote backup location (NEVER backup locally, ever) or an iffy network card (been there) can mean your backups are no use whatsoever. They should be regularly tested
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 17, 2016 at 3:34 am
Short answer: you can't.
Long answer: there is no way to tell whether a backup will restore succesfully or not and that alone is no guarantee that the backup does not contain a corrupt database.
The only way to know for sure is to restore the database from the backup set. You can achieve almost the same result with RESTORE VERIFYONLY.
On the other hand, there is no guarantee that the a successful restore process means that the database is free from corruption, so you may want to check your database from corruption after restoring.
-- Gianluca Sartori
October 17, 2016 at 10:03 am
andrew gothard (10/17/2016)
Firstly, if you do your backup with the checksum option, this will identify various forms of potential corruption. Restore with verifyonly will also identify a range of issues with your backups.Having said that, realistically, when you've done a test restore and verified it with DBCC CheckDB, then you can be reasonably confident.
Your database may be fine, your storage holding your DB files may be fine, but dodgy storage on your remote backup location (NEVER backup locally, ever) or an iffy network card (been there) can mean your backups are no use whatsoever. They should be regularly tested
"... (NEVER backup locally, ever)..."
Can you expand on why this is a bad idea ?
Because the backup & database are both at risk of drive failure ?
October 17, 2016 at 11:19 am
homebrew01 (10/17/2016)
andrew gothard (10/17/2016)
Firstly, if you do your backup with the checksum option, this will identify various forms of potential corruption. Restore with verifyonly will also identify a range of issues with your backups.Having said that, realistically, when you've done a test restore and verified it with DBCC CheckDB, then you can be reasonably confident.
Your database may be fine, your storage holding your DB files may be fine, but dodgy storage on your remote backup location (NEVER backup locally, ever) or an iffy network card (been there) can mean your backups are no use whatsoever. They should be regularly tested
"... (NEVER backup locally, ever)..."
Can you expand on why this is a bad idea ?
Because the backup & database are both at risk of drive failure ?
That's pretty much the reason. If your backups and your database are on the same storage / server, what happens when the drive crashes, the server catches fire, someone spills a cup of coffee into the server, your PFY jams an overpowered cattle-prod into the server? Now your backups may be hosed, along with the database files themselves, so how do you recover?
You want to always get your backup files away from the server you're backing up, just in case. It doesn't matter if you're talking about a database server, web server, file server, or what.
October 18, 2016 at 10:10 am
I've got an article I wrote on backup testing. You can see all the options available[/url]. As everyone says though, the one way to be sure is to do a restore.
"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
October 18, 2016 at 7:53 pm
jasona.work (10/17/2016)
homebrew01 (10/17/2016)
andrew gothard (10/17/2016)
Firstly, if you do your backup with the checksum option, this will identify various forms of potential corruption. Restore with verifyonly will also identify a range of issues with your backups.Having said that, realistically, when you've done a test restore and verified it with DBCC CheckDB, then you can be reasonably confident.
Your database may be fine, your storage holding your DB files may be fine, but dodgy storage on your remote backup location (NEVER backup locally, ever) or an iffy network card (been there) can mean your backups are no use whatsoever. They should be regularly tested
"... (NEVER backup locally, ever)..."
Can you expand on why this is a bad idea ?
Because the backup & database are both at risk of drive failure ?
That's pretty much the reason. If your backups and your database are on the same storage / server, what happens when the drive crashes, the server catches fire, someone spills a cup of coffee into the server, your PFY jams an overpowered cattle-prod into the server? Now your backups may be hosed, along with the database files themselves, so how do you recover?
You want to always get your backup files away from the server you're backing up, just in case. It doesn't matter if you're talking about a database server, web server, file server, or what.
My company have a rule that all the data from the backup will be stored for 1 week and will be moved to another server. And also because of we have a mirroring server
October 18, 2016 at 8:00 pm
Deny Christian (10/18/2016)
jasona.work (10/17/2016)
homebrew01 (10/17/2016)
andrew gothard (10/17/2016)
Firstly, if you do your backup with the checksum option, this will identify various forms of potential corruption. Restore with verifyonly will also identify a range of issues with your backups.Having said that, realistically, when you've done a test restore and verified it with DBCC CheckDB, then you can be reasonably confident.
Your database may be fine, your storage holding your DB files may be fine, but dodgy storage on your remote backup location (NEVER backup locally, ever) or an iffy network card (been there) can mean your backups are no use whatsoever. They should be regularly tested
"... (NEVER backup locally, ever)..."
Can you expand on why this is a bad idea ?
Because the backup & database are both at risk of drive failure ?
That's pretty much the reason. If your backups and your database are on the same storage / server, what happens when the drive crashes, the server catches fire, someone spills a cup of coffee into the server, your PFY jams an overpowered cattle-prod into the server? Now your backups may be hosed, along with the database files themselves, so how do you recover?
You want to always get your backup files away from the server you're backing up, just in case. It doesn't matter if you're talking about a database server, web server, file server, or what.
My company have a rule that all the data from the backup will be stored for 1 week and will be moved to another server. And also because of we have a mirroring server
In physical relation to the original server, where is the mirror located?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 9:51 pm
[/quote]
That's pretty much the reason. If your backups and your database are on the same storage / server, what happens when the drive crashes, the server catches fire, someone spills a cup of coffee into the server, your PFY jams an overpowered cattle-prod into the server? Now your backups may be hosed, along with the database files themselves, so how do you recover?
You want to always get your backup files away from the server you're backing up, just in case. It doesn't matter if you're talking about a database server, web server, file server, or what.[/quote]
My company have a rule that all the data from the backup will be stored for 1 week and will be moved to another server. And also because of we have a mirroring server[/quote]
In physical relation to the original server, where is the mirror located?
[/quote]
My company using IBM DNS service. Located at different location
October 18, 2016 at 10:11 pm
I still confuse about Restoring:
Specify the source and location of backup sets to restore:
from database
from device
what's the different ?
October 18, 2016 at 11:37 pm
Deny Christian (10/18/2016)
I still confuse about Restoring:Specify the source and location of backup sets to restore:
from database
from device
what's the different ?
FROM DATABASE is typically used only when you want to restore to the exact same database that you backed up from.
FROM DEVICE is typically used when you want to restore from a different backup, like when you want to restore a production backup to a development database.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 11:55 pm
Jeff Moden (10/18/2016)
Deny Christian (10/18/2016)
I still confuse about Restoring:Specify the source and location of backup sets to restore:
from database
from device
what's the different ?
FROM DATABASE is typically used only when you want to restore to the exact same database that you backed up from.
FROM DEVICE is typically used when you want to restore from a different backup, like when you want to restore a production backup to a development database.
tahnk you sir for the explanation.
If the production server, it must keep online when restoring, while I restore the same database that i backed up from, so the rest means they must redo the job ?
October 19, 2016 at 12:49 am
Deny Christian (10/18/2016)
Jeff Moden (10/18/2016)
Deny Christian (10/18/2016)
I still confuse about Restoring:Specify the source and location of backup sets to restore:
from database
from device
what's the different ?
FROM DATABASE is typically used only when you want to restore to the exact same database that you backed up from.
FROM DEVICE is typically used when you want to restore from a different backup, like when you want to restore a production backup to a development database.
tahnk you sir for the explanation.
If the production server, it must keep online when restoring, while I restore the same database that i backed up from, so the rest means they must redo the job ?
Anything and everything after the restore point (date/time) that was running or did run will need to be redone. That's why it's important to keep your log files in really good shape and know when to do a tail log backup.
At this point in time, I'm going to suggest that you need to do some study on the subject of restores. Please start at the following link. Not being snarky here, either. It's one of the most important things that DBAs need to know how to do and they should study it and practice it until they can do it in their sleep. You should also search for RPO, RTO, and disaster planning.
https://www.google.com/?gws_rd=ssl#q=restore+database+sql+server
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2016 at 1:18 am
Jeff Moden (10/19/2016)
Deny Christian (10/18/2016)
Jeff Moden (10/18/2016)
Deny Christian (10/18/2016)
I still confuse about Restoring:Specify the source and location of backup sets to restore:
from database
from device
what's the different ?
FROM DATABASE is typically used only when you want to restore to the exact same database that you backed up from.
FROM DEVICE is typically used when you want to restore from a different backup, like when you want to restore a production backup to a development database.
tahnk you sir for the explanation.
If the production server, it must keep online when restoring, while I restore the same database that i backed up from, so the rest means they must redo the job ?
Anything and everything after the restore point (date/time) that was running or did run will need to be redone. That's why it's important to keep your log files in really good shape and know when to do a tail log backup.
At this point in time, I'm going to suggest that you need to do some study on the subject of restores. Please start at the following link. Not being snarky here, either. It's one of the most important things that DBAs need to know how to do and they should study it and practice it until they can do it in their sleep. You should also search for RPO, RTO, and disaster planning.
https://www.google.com/?gws_rd=ssl#q=restore+database+sql+server
noted sir, thanks a lot
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply