December 21, 2005 at 4:42 pm
Hi everyone, we have a transactional db server (server 1) that does a large number of operations. I run a full backup of databases on this server every night. It seems like the backup slows down the server considerably. We replicate the databases from server 1 to an analysis server (server 2) every night. Can I run the full backup against server 2 and the log backups to server 1 and use them together to restore the databases in case of a crash? Both db servers have the same level of patches, etc. Thanks for your help.
December 21, 2005 at 4:59 pm
yes.
It's only in your RESTORE statement that you say that you're done (finished with th restore) or there's more (log backups) still to come (in your restoration process).
See RESTORE, and look at NORECOVERY/RECOVERY options.
December 21, 2005 at 5:00 pm
But, both Database and Log backups have to be from the same server/database.
December 21, 2005 at 6:29 pm
Thank you John, your second post answered my question. Just for my own understanding, can you explain why they have to be from the same server/database?
December 22, 2005 at 9:13 am
I think it's to better ensure data consistency. Even though the databases on the two servers have the same name, they are different databases with no guarantee that they are identical.
Greg
Greg
December 22, 2005 at 9:26 am
Hi Greg,
Data consistency is why I am concerned. But I am not sure how this will cause data loss. For example:
1. We replicate the databases from server 1 to server 2 at 8 am.
2. Server 2 does no transactions at all, while server 1 is constantly performing transactions after the replication completes.
3. We make a full backup of database 1 on server 2.
4. We make a log backup of database 1 on server 1.
I would like to know, if we restore the full backup and then apply the log backups...how this could compromise the database. Please, please help me understand this. Thank you!
December 22, 2005 at 1:22 pm
We do this manually by restoring the database and logs in standby mode.
That way, you could still run your analysis (as long as it doesn't do anything in the database except read)
It can also be done automatically with log-shipping.
If you need to bring the backup online, just recover the database and go.
More details can be found in BOL under "standby" and "log-shipping".
All of the servers should be on the same patch level.
It might work with different patch levels, but I don't really want to try it.
December 23, 2005 at 9:02 am
Shagols,
I understand your situation and I'm sure you're careful enough about it that there would be no data inconsistency. It seems that MS is trying to protect people who aren't as careful from themselves.
I'll bet if SQL Server allowed backups of different databases to be restored into the same database, they'd have customers complaining that their data was all messed up because they didn't realize that allowing updates on the analysis server would cause a problem.
Greg
Greg
December 23, 2005 at 9:17 am
When you do a backup, SQL Server adds a LSN (I think it stands for Logical Sequence Number). When you do a restore, these numbers have to match or be in sequence (I'm not sure exactly how it works). But regardless, the TLog backups from one server won't have LSNs that match or 'go with' the LSN for the full backup from the other server.
-SQLBill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply