October 29, 2015 at 9:13 pm
Hi Team,
We have a situation in our project, our DB's are moved to a different SQL servers (2008 r2) handled by SQL Team.
We have a Job which creates DB snapshot of our Main DB - its size is about 450 GB. As per new sql team, snapshots are not allowed (not supported by them, not sure why..)
DB snapshot is used for reporting purposes.
As a solution, we are told to opt for backup-restore method. But since DB is 450 GB it would take time and we have some availability concerns and Job executions after which it can run.
My Queries:
> We will create a new FULL DB once. Differential Backup are already taken everyday at 1 AM - so can we restore only the Differential backups to the full DB everyday ??.
> Transnational Log backup is taken every hour. But do we need to restore Transactional log backup as well ?? , what if we restore nly differential backups , will it not work or are there any dire consequences of it?.
> Are DB snapshots bad ? . I am not sure why it's not recommended.
Any pointers or views or any other solutions if anyone has worked on this would be helpful.
Regards.
October 30, 2015 at 2:46 am
Snapshots create sparse files. So as data changes, the original data is pushed into the sparse file to ensure the snapshot is a snapshot of that time it was created.
Unsure why your SQL team wont support them, maybe that's a discussion you need to have with them and find out why they don't and if you have a valid business requirement to use them, maybe one for management to say that they will need to support them.
For your backup and restore options, you would need to restore the full backup every day along with the differential.
It all depends where you need the point in time to be 9am eg, restore last full with norecovery, restore last diff with no recovery, restore every log (except last) up to 9am with no recovery, restore the last log at 9am with recovery.
As you "recover" the DB, you can't simply just restore the next days diff over the top, you need to start the restore chain again, Full, Diff, Logs.
What might be best is some form of log shipping in read only mode and restore the logs every X hours that suits your needs
Replication of some sort, depending how up to date you need it, snapshot replication might be best if you only need it once a day, otherwise if you need it as close to real time will need to look at transactional replication
October 30, 2015 at 4:39 am
Is this a reporting database?
What do the users want? A once-a-day known-time-of-day restore? or a continuously-restoring-but-not-current?
My users think they want "current", but then they realise that "current" includes data in the process of being sorted out, entered from the post, and is less "consistent" than a backup taken in the middle of the night. I don't think they would like log restores (unless they were frequent and very current) as they wouldn't know what age the database was. e.g. they could rerun a report a minute later and get a different result because data had been added / changed.
Different situations needs different solutions of course. Even if viewing the "current" database there is still post sitting in someone in-tray waiting to be entered ...
For management reports "Yesterday's data" is often good enough, and a copy-database is an awful lot easier to provide and support than a "current" data copy-database.
Are you using compressed backups? (Used to be enterprise feature, but no longer since 2008). From my tests: 80% smaller files (and thus copy-transfer time), 40% saving in Backup (and Restore) time [less I/O]. There is a config setting that will make Compressed backups the default, so you might not have to change anything else.
November 1, 2015 at 6:55 pm
Hi Kristen,
PFB my inline comments.
Is this a reporting database?
[Yes this is will be used for Reporting]
What do the users want? A once-a-day known-time-of-day restore? or a continuously-restoring-but-not-current?
[One day old data is acceptable]
My users think they want "current", but then they realise that "current" includes data in the process of being sorted out, entered from the post, and is less "consistent" than a backup taken in the middle of the night. I don't think they would like log restores (unless they were frequent and very current) as they wouldn't know what age the database was. e.g. they could rerun a report a minute later and get a different result because data had been added / changed.
Different situations needs different solutions of course. Even if viewing the "current" database there is still post sitting in someone in-tray waiting to be entered ...
For management reports "Yesterday's data" is often good enough, and a copy-database is an awful lot easier to provide and support than a "current" data copy-database.
[Our DB size is 450 GB. Does copy DB copy the data as well or only the database objects.]
Are you using compressed backups? (Used to be enterprise feature, but no longer since 2008). From my tests: 80% smaller files (and thus copy-transfer time), 40% saving in Backup (and Restore) time [less I/O]. There is a config setting that will make Compressed backups the default, so you might not have to change anything else.
[No , not using compressed backups. We dont have control over the DB servers, there is a different team that handles this]
November 2, 2015 at 12:56 am
GonnaCatchIT (11/1/2015)
[Our DB size is 450 GB. Does copy DB copy the data as well or only the database objects.]
Sorry, poor use of terminology, by "copy-database" I meant a database restored from a backup, so it would be the full database including all data and objects.
[No , not using compressed backups. We dont have control over the DB servers, there is a different team that handles this]
I suggest you ask the other team to turn on compressed backups, so that the transfer time, and restore, is as fast as possible.
Sounds like just re-restoring the Full backup and then the latest Differential is what you need.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply