What are the Pre-requisites to configure Log shipping ?
- SQL Server 2005 or later
- Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
- The servers involved in log shipping should have the same case sensitivity settings.
- The database must use the full recovery or bulk-logged recovery model
- A shared folder for copying T-Log backup files
- SQL Server Agent Service must be configured properly
In which recovery model Log Shipping can be configured?
Log Shipping works with Full and Bulk Logged recovery model.
Is it possible to configure Log shipping on the database server with different collation?
Is it possible to configure Log Shipping from lower version to upper version and Vice versa?
Yes, it is possible to configure Log Shipping from lower to upper version. But it is not possible vice versa.
What editions of SQL Server is log shipping available in?
- 2012 – Enterprise, Business Intelligence, Standard, and Web
- 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
- 2008 – Enterprise, Standard, Web, and Workgroup
- 2005 – Enterprise, Standard, and Workgroup
Log shipping in SQL Server 2000: Enterprise, Developer Edition.
Standard Edition – Simple Log Shipper tool, which you can find in the Microsoft SQL Server 2000 Resource Kit.
Is it possible to log ship database between SQL 2000 & SQL 2005?
No, thats impossible, In SQL 2005 transaction log architecture is changed compared to SQL 2000 and hence you won’t be able to restore log backups from SQL 2000 to SQL 2005 or vice versa.
Can we configure Log Shipping between the different domains?
Yes, we can configure Log Shipping on the server residing in different domains.
To setup a log-shipping you must have sysadmin rights on the server.
What is TUF file?
TUF stands for Transaction Undo file.
While restoring log back up on secondary server uncommitted transactions are come to .TUF file.
What will happen to Log Shipping if TUF file is corrupted or lost?
The log shipping will not work. We have to setup the Log Shipping again.
Can we take full backup of the log shipped database in secondary server?
No, we won’t be able to execute BACKUP command against a log shipped database in secondary server.
Can we take full backup of the log shipped database in primary server?
Yes it’s possible. We can take full backup of log shipped database and this won’t affect the log shipping.
Can we setup multiple secondary databases in Log Shipping?
Yes, we can setup multiple secondary databases in Log Shipping.
Can we shrink log shipped database log file?
Yes, we can shrink the log file, but we shouldn’t use WITH TRUNCATE option. If we use this option obviously log shipping will be disturbed.
If you create a Job on the Primary database server, will it automatically be created on the secondary server or not?
No, it will not be created on the secondary server.
If you create a user on the Primary database, will it automatically be created on the secondary or not?
Yes, it will be created automatically on the secondary database.
What are permissions required for Log shipping?
We must have sysadmin on each server instance to configure Log shipping.
What permissions are required for shared folders on Primary and secondary for the service accounts?
- For the backup job, read/write permissions to the backup directory are required to the following:
SQL Server service account on the primary server instance.
Proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
- For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
- For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
How to failover secondary server, when the Primary Server fails?
If the Primary Server will become un-available, do the following steps.
- a) Take the Tail of Log from Primary server if possible.
- b) Restore Tail of log into all Secondary Database
- c) Remove Log-shipping configuration from Primary Server
- d) Select any one of Secondary server and bring into online with Alter Database DBName set Online
- e) Right click on Primary Database and Generate script for Users and Logins.
- f) Then move the script to Secondary server to create Users and Logins
- g) Re-configure log shipping from New Server (Secondary server)
What are errors occurred in Log shipping?
There are two errors are occurred during Log shipping
1) 14420:- This error occurs when the Backup job fails
2) 14421:- This error occurs when the Restoring job fails
1. I’m getting the below error message in restoration job on secondary server, WHY?
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
Answer: Was your sql server or agent restarted Y’day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result !
You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine.
2. I’ve configured Log shipping successfully on standby mode, but in the restoration job I’m getting the below error. What I do to avoid this in future??
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn’ to secondary database ‘TEST’.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won’t allow SQL Server to restore the tlogs. To avoid this you need to check “Disconnect users in the database when restoring backups” options in log shipping configuration wizard.
- Suddenly I’m getting the error below, How can I rectify this???
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally
Answer: We had the same issue some time ago, this was related to a new file being created in a file group on the source. Don’t know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem.