Log Shipping and Best Practices

paulnxwebpaulnxweb Posts: 21
edited August 2, 2006 2:18PM in SQL Backup Previous Versions
I can not find a best practices document concerning log shipping and backup or any white papers how redgate interacts with SQL Server to guess what I should do.

I plan to use log shipping to remote server across the internet using a shared drive with the encryption option. I have prototyped this setup using the log shipping wizard with log dumps and restores every 5 minutes on a small test database. It works nicely so far. My real world database is 10GB and growing at 1gb a month so the questions are

1) How do I tell the wizard to split the initial database backup into 10 files, prior to the log shipping configuration phase (the initial transfers thru the internet is brutal if this is one single file)
2) How do I reset the log shipping from the beginning phase each week. I feel unconfortable running the logshipping continously for months and months. I feel more comfortable restarting from the beginning of backing the database and restoring to the remote server then starting log shipping every 5 minutes.

3) What else do I need to do to insure the safety of my database? Hence the need for the best practices document. I have no clue if I can run redgate backup and log shipping at same time without it clobbering something.
My current environment prior to use of Redgate backup is to dump the entire database to a back up device nightly and then dump/backup the transaction log every 5 minutes (I can withstand a 5 minutes loss of data). So I want the same piece of mind that I get using my current implementation but I want to replace this using the redgate log shipping and get the extra advantage of having the backup done to a remote standby server.

I'm no database expert but some advice will be helpful! I can't imagine that I am the only person having these same questions.


  • peteypetey Posts: 2,358 New member
    Perhaps an explanation of how SQL Backup interacts with SQL Server would help.

    When you perform a backup using SQL Server (native backup), you can choose to either backup to a disk or tape device e.g. BACKUP DATABASE pubs TO DISK = ...

    When SQL Backup performs a backup, it also uses the same BACKUP command as a native backup, with the difference that the backup is directed to one or more virtual devices e.g. BACKUP DATABASE pubs TO VIRTUAL DEVICE = ... A virtual device is an area in memory created by SQL Backup. So SQL Server writes the backup data into that memory area, SQL Backup compresses the data, and writes it out to disk. SQL Backup is basically an additional 'layer' between SQL Server and the disk device, providing compression and encryption functions.

    It's thus subjected to the same backup 'rules' and principles as a native backup e.g. transaction log backups cannot be made for databases using a simple recovery model, for any one database only one backup can be running at any one time etc. By extension, any log shipping best practices should apply equally to log shipping using SQL Backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    1. It is not possible to split the initial full database backup. What you would need to do is backup and restore the database manually using multiple files, and skip the creation of the new database in the Log Shipping Wizard.

    2. There isn't an option to 'reset' the log shipping process. Are you aware that a full database backup does not break the transaction log sequence? E.g.

    - full backup X
    - transaction log backup A
    - transaction log backup B
    - transaction log backup C
    - full backup Y
    - transaction log backup D
    - transaction log backup E

    If you wanted to restore the database, you can either restore X, followed by A, B, C, D, E, or restore Y, followed by D and E. So you can still make a full database backup periodically (because you are afraid of the log shipping failing, or to minimise restore time), and still allow the log shipping process to restore the transaction logs.

    However, you need to note that during a full backup (native or using SQL Backup), any transaction log backups performed by SQL Backup will fail, and be reported as an error. To avoid this, you need to ensure that you do not schedule any SQL Backup transaction log backups while a full backup may be running.

    3. You could run RESTORE VERIFYONLY on the backup files. If you are using SQL Server 2005, you should consider performing backups using the CHECKSUM option. Running RESTORE VERIFYONLY then will perform more integrity checks on the backup files. See the SQL Server documentation for details.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • thanks for the helpful info, looks like I will need to use the command line interface and custom code/scripts
Sign In or Register to comment.