Comments
Sort by recent activity
There are pros and cons to each approach, so it depends what you want to get out of it. I'll post a few thoughts to get the ball rolling, and hopefully others will add their own opinions.
If you keep them as three seperate backups and restores and offset by a few minutes:
+ Will be supported by tools such as SQL Backup, because you are using the standard log shipping configuration.
+ Very easy to follow if the delays are much more than the expected duration - will work well unless the expected duration is much higher than anticipated (where collisions will appear again)
- Requires more attention if backups and restores vary wildly in expected duration, since ultimately will result in the same collision problem.
If you combine them all into one job with 3 steps:
+ All the steps will work one after the other, so the only collision that can occur is if the whole combined job exceeds 15 minutes.
- Any changes to the jobs will need to be done via Management Studio, as it is unlikely to be supported in the SQL Backup user interface.
- Care needs to be taken when setting up the job - if the first step fails, you probably want the second step to continue - which is usually not the default.
Hope those thoughts help,
Jason / comments
There are pros and cons to each approach, so it depends what you want to get out of it. I'll post a few thoughts to get the ball rolling, and hopefully others will add their own opinions.
If you k...
In the first model:
The 4 backups would pass with a warning because the COPYTO has failed (warning code 141, 142, 143).
The 4 follow-on restores would pass, citing that "No log files found to be restored", and issue a warning code (220).
When the network returns and the backup successfully copies, the next restore will fail, citing that the log sequence is missing logs. If you are using the MAILTO_ONERROR you will then get an email telling you a problem has occured. You can then resolve the problem and bring the log shipping back online.
Once the initial problem has been resolved, the log shipping should be back up to date almost instantly.
--
In the second model:
The 4 backups would pass because the COPYTO is to a local location.
The 4 restores would also pass, citing that "No log files found to be restored", and issue a warning code (220).
Once the network returns, the restore will be able to copy the files across, but because of the performance penalty it'll have a knock-on delayed effect until it catches up, depending on the timing issues this could leave a window (minutes? hours?) where the standby server is more than 15 minutes out of date.
The problem is, you'd never know this was happening if you used the MAILTO_ONERROR keyword, you'd only find out by trawling the MAILTO emails, or scanning the user interface after the event... the first can easily be missed, and the second will only happen after the event.
Hope that helps,
Jason / comments
In the first model:
The 4 backups would pass with a warning because the COPYTO has failed (warning code 141, 142, 143).
The 4 follow-on restores would pass, citing that "No log files found to be re...
When performing the backups itself, you should always back up to a local drive.
The reason for this is two-fold:
* Firstly, backing up across a network introduces additional points of failure (the network, switches etc) and places that corruption could occur.
* Secondly, performance across the network will result in a very slow backup, the performance being a tiny fraction of that experienced when backing up to the local drive and copying afterwards.
The major caveat with this is that when performing the backup, where possible it should not be to the same local drive - in the event a disk problem occurs, there is less chance of everything being lost in one go.
When using the Log Shipping Wizard, after the backup has been performed the files are moved (using the COPYTO keyword) to the network share - in the version 5 user interface this is step 4. If you choose to have the network share on the remote machine, you get the following:
* Log is backed up on a local disk;
* Log is copied (using COPYTO) from the local disk, to the shared directory (which could be on the remote server);
* Log is restored from the shared directory (which if on the remote server is a 'local' restore, which gives higher performance);
* Log is then moved to the processed directory (if both locations are on the remote server, it doesn't need to cross the WAN again).
In the above, the file is only copied across the WAN once, during the COPYTO phase (point 2)... the MOVETO at the end could move the file from one place on the remote server to another, meaning that it does not need to go across the WAN again.
I hope that helps with your questions - if you need any more details or more explaination, feel free to ask.
Jason / comments
When performing the backups itself, you should always back up to a local drive.
The reason for this is two-fold:
* Firstly, backing up across a network introduces additional points of failure (the ...
The message 'The indicated service is not an installed service' occurs because you are using SQL Server Express edition - which does not offer the SQL Server Agent.
Since SQL Backup uses the SQL Server Agent for scheduling, unfortunately this means you cannot schedule backup jobs via the standard graphical user interface tools.
The ideal solution is to use a version of SQL Server the supports the SQL Server Agent (anything but express).
However, if you cannot justify the upgrade, there is a "workaround" of sorts, where you can generate the Command Line statement using the backup wizard, insert it into a batch file, and then use "Scheduled Tasks" to run the batch file on the schedule you want.
It's far from ideal, but should be a reasonable substitute for the Agent, although it isn't clear how reliable or stable the Scheduled Tasks functionality is, so be wary if considering it in a production environment.
Hope that helps,
Jason / comments
The message 'The indicated service is not an installed service' occurs because you are using SQL Server Express edition - which does not offer the SQL Server Agent.
Since SQL Backup uses the SQL Se...
Hi,
I'm afraid that it is indeed a problem with SQL Backup 5.0. We have already fixed the problem in the forthcoming 5.1 release (which should be released sometime next week), but in the meantime you can do one of the following to resolve the problem temporarily:
* Substitute ERASEFILES with ERASEFILES_ATSTART - the files will be deleted at the start of the backup rather than at the end, and will work correctly with the VERIFY keyword. In the user interface check the 'Delete prior to start of backup' box.
* Alternatively, If you are using the extended stored procedures directly or the command line, you can use the RESTORE VERIFYONLY syntax (described in the SQL Backup 5 help documentation) to perform a verification step after the backup.
Thankyou for raising the issue on the forums, and we apologise for any inconvenience it may have caused.
Thanks,
Jason / comments
Hi,
I'm afraid that it is indeed a problem with SQL Backup 5.0. We have already fixed the problem in the forthcoming 5.1 release (which should be released sometime next week), but in the meantime ...
Hi,
Thanks for your feedback. Unfortunately the issue you mention was not resolved in the 5.1 release, and indeed is a different issue to the MAXTRANSFERSIZE issue that Brian mentioned.
I have forwared this to our developers, and hopefully they will include it in a subsequent release. In the meantime any verification issues will be flagged as warnings, so you should see warning icons on the activity history indicating the problem.
Hopefully this answers your question... if you have any follow-up questions or issues, feel free to ask.
Thanks,
Jason / comments
Hi,
Thanks for your feedback. Unfortunately the issue you mention was not resolved in the 5.1 release, and indeed is a different issue to the MAXTRANSFERSIZE issue that Brian mentioned.
I have for...
Additionally, the error message will occur if any of the following are true for the files in the directory "C:\Documents and Settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backup\Server Data":
* Your account has the necessary security permissions to write to the file, but the file has the read-only attribute applied.
* The file is not writeable by your user account, due to restricted security permissions.
Removing the read-only attribute or ensuring the user account has the necessary read/write permissions to the directory should also resolve the problem.
Jason / comments
Additionally, the error message will occur if any of the following are true for the files in the directory "C:\Documents and Settings\<user name>\Local Settings\Application Data\Red Gate\SQL Backup...
The purpose of the msdb..backupmediafamily and msdb..backupset tables is to list the backup 'devices' and 'media' used when performing the backup.
Each file (in SQL Server or SQL Backup) or thread (in SQL Backup) makes use of its own backup device via SQL Server to extract the data from the SQL Server instance.
Therefore, using multiple threads or files will result in multiple entries in the msdb..backupmediafamily table (and will give a number higher than one in the "last_family_number" column of the msdb..backupset table)... that's quite safe and is the expected behaviour.
More gory details on the SQL Server backup and restore tables can be found on MSDN at http://msdn2.microsoft.com/en-us/library/ms188062.aspx
Hope that helps,
Jason / comments
The purpose of the msdb..backupmediafamily and msdb..backupset tables is to list the backup 'devices' and 'media' used when performing the backup.
Each file (in SQL Server or SQL Backup) or thread ...
Thanks for following up on my previous questions... and apologies for not responding sooner.
We have looked into the issue further and have identified that there is a problem in SQL Backup 5.0 and 5.1 with the log shipping wizard.
When the source and destination file locations are named the same (which can be common if both servers have the same SQLDataRoot value), it is possible for the wizard to assign an empty location to MOVE to, which will cause the log shipping wizard to fail when restoring the full backup.
This will be patched in the next release of SQL Backup, but in the meantime the following workaround can be applied to avoid this issue:
Either:
* Use the "Create a new database to restore to" option.
Or:
* If the data and log files do not share the same folder locations or filenames in the same folder (e.g. "d:\mssql\database_data.mdf" on one machine, "c:\mssql_data\database_data.mdf" on the other), then you can use the "Overwrite existing database" option and should not have any issues.
If you have any further questions regarding the issue, feel free to follow up either on the forum or via email.
Thanks,
Jason / comments
Thanks for following up on my previous questions... and apologies for not responding sooner.
We have looked into the issue further and have identified that there is a problem in SQL Backup 5.0 and ...