October 13, 2011 at 1:14 pm
Hello, we have a production SQL Server 2008 R2 STD database stored at a remote site. The users would like a copy of this database created on a local server in read only mode. They'd like it updated weekly. The DB is too large to send from the remote site each week. I have already created the database locally and restored it from a backup. So the DBs are currently identical. Can I use transaction log backups from the remote site to restore my local copy and thus keep it in sync with the remote DB? There aren't a lot of changes so the transaction logs should be small. I don't understand the steps I'd need to take to restore with the transaction backups. Any advice is much appreciated.
October 13, 2011 at 9:59 pm
You can write a script that copies transaction log backup continuously from production to test and restore them once a week.
Important point is you should not lose a even a single log backup, that will break the log chain 🙂
October 14, 2011 at 12:28 am
Important point is you should not lose a even a single log backup, that will break the log chain 🙂
To avoid this I would suggest you to go for differential backups. I backup / restore and you are done. 🙂
Another advantage of Differential Backup is:
'If one record is updated 5 times in a week, you might have it captured in 5 different transactions logs. But Differential backups will have the most recent copy of changes after FULL backup. So it would be comparatively small in size.'
Using Differential Backups
October 14, 2011 at 8:20 am
Regarding: To avoid this I would suggest you to go for differential backups. I backup / restore and you are done.
So the remote site sends me the original full backup (the "base") and each week they'll send me the differential backup, which I'll use both to restore the read only local DB. That'll work for the 1st week. But the remote site will be taking a full backup each week as part of their normal backup and recovery procedure. In order for me to restore with the differential backup in subsequent weeks, I'll need the new full backup (the "base") each week, will I not? The whole idea is to avoid needing to download the huge full backup file each week.
October 14, 2011 at 8:35 am
The DB is too large to send from the remote site each week.
What happens if you use SQL 2008 compression when taking the back up. Does that reduce the database size sufficiently to enable you to copy that from the remote location?
October 14, 2011 at 8:44 am
darkfour (10/14/2011)
The DB is too large to send from the remote site each week
What happens if you use SQL 2008 compression when taking the back up. Does that reduce the database size sufficiently to enable you to copy that from the remote location?
This is in the planning stages. I've been told the DB will be about 20 GB but really have no way of knowing if compression will make a difference. So I'm going on the assumption it'll be a large file and thus can't be sent to us each week.
October 16, 2011 at 2:32 pm
In my experience, compression with 2008 R2 will make a big difference. I am seeing a 50-80% reduction in backup size.
October 17, 2011 at 1:18 am
gary.dunn (10/13/2011)
Hello, we have a production SQL Server 2008 R2 STD database stored at a remote site. The users would like a copy of this database created on a local server in read only mode. They'd like it updated weekly. The DB is too large to send from the remote site each week. I have already created the database locally and restored it from a backup. So the DBs are currently identical. Can I use transaction log backups from the remote site to restore my local copy and thus keep it in sync with the remote DB? There aren't a lot of changes so the transaction logs should be small. I don't understand the steps I'd need to take to restore with the transaction backups. Any advice is much appreciated.
Cant you just create a DB snapshot ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
October 17, 2011 at 2:13 am
But the remote site will be taking a full backup each week as part of their normal backup and recovery procedure.
It was not mentioned in the opening post.
There aren't a lot of changes
So you may decrease the frequency of FULL backups.
I've been told the DB will be about 20 GB
I don't consider it's too large database. The estimated FULL backup size would be 4-5 GBs (even less if you compress it). The DIFFERENTIAL backup should also be small.
October 17, 2011 at 2:20 am
I am seeing a 50-80% reduction in backup size.
Few Facts about Backup Compression:
The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained. Major factors include:
The type of data.Character data compresses more than other types of data.
The consistency of the data among rows on a page.Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.
Whether the data is encrypted.Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.
Whether the database is compressed.If the database is compressed, compressing backups might not reduce their size by much, if at all.
October 17, 2011 at 2:24 am
Cant you just create a DB snapshot ?
By the Database Snapshot definition, it's not applicable here.
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.
October 17, 2011 at 3:25 am
Ok maybe snapshots were not such a good idea.
How about setting up LogShipping (if latency is accepatable) or snapshot replication ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
October 17, 2011 at 3:55 am
How about setting up LogShipping (if latency is accepatable) or snapshot replication ?
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Thus it’s as good as FULL backup.
Log shipping consists of three operations:
1.Back up the transaction log at the primary server instance.
2.Copy the transaction log file to the secondary server instance.
3.Restore the log backup on the secondary server instance.
OP doesn’t want a warm standby database where we prefer to use Log Shipping. Also you need to configure database & create jobs for Log Shipping. Creating jobs (SQL Server Agent requires to be up & running) is pain area for Log Shipping.
October 17, 2011 at 11:17 am
You say that there will not be many changes... Why not use transactional replication?
Jared
Jared
CE - Microsoft
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply