Modify the SQL Server Agent job parameters for the job that performs transaction log backups on the primary database.ĭisable the SQL Server Agent job from creating transaction log backups. Modify the schedule that is created when the SQL Server Agent job is created. After the job has been created, you can modify this schedule by clicking Edit Job. Before the backup job has been created, You can modify this schedule by clicking Schedule. When first creating the job, you can modify the name by typing in the box.ĭisplays the current schedule for backing up the transaction logs of the primary database. Specify the amount of time you want log shipping to wait before raising an alert that no transaction log backups have occurred.ĭisplays the name of the SQL Server Agent job that is used to create the transaction log backups for log shipping. Specify the length of time you want transaction log backups to remain in your backup directory before being deleted. The SQL Server service account of the primary server instance must have Read and Write permissions on this folder.
If the SQL Server service account is running under the local system account on the primary server, you must create the backup folder on the primary server and specify the local path to that folder here. If you specify a local path here, the BACKUP command will use this path to create the transaction log backups otherwise, if no local path is specified, the BACKUP command will use the network path specified in the Network path to the backup folder box. If the backup folder is not located on the primary server, you can leave this blank. Type the local drive letter and path to the backup folder if the backup folder is located on the primary server. If the backup folder is located on the primary server, type the local path to the folder By default, this is the SQLServerAgent service account of the secondary server instance, but an administrator can choose another proxy account for the job. You must grant read permissions on this network share to the proxy account under which the copy job will run at the secondary server instance. The local folder where your transaction log backups are saved must be shared so that the log shipping copy jobs can copy these files to the secondary server. Type the network share to your backup folder in this box.
…but this isn’t the recommended approach as it reduces security somewhat.Use this dialog box to configure and modify the transaction log backup settings for a log shipping configuration.įor an explanation of log shipping concepts, see About Log Shipping (SQL Server). Finally, reboot the machine… and job done ?Īpparently, you can disable the authentication loopback check instead, by updating the following registry entry with a value of 1: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\DisableLoopbackCheck.
NOTE: For Windows 2003 machines, you’ll need to download and install Windows Support Tools first. Register the Kerberos Service Principal Names (SPNs) for the alias(es), by using the setspn tool like below:įor example, my machine name here is SQLVM1, and I want to use the DNS alias (FQDN): setspn -A host/SQLVM1-DR SQLVM1.NOTE: You need to type each host name/alias on a separate line here. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0Īdd a new entry called BackConnectionHostNames with a Multi-String Valueįor the value, enter the non-FQDN alias that is to be used on the computer.
Edit the following registry key to allow this machine to use file sharing with itself via the alias:.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parametersĪdd a new entry called DisableStrictNameChecking with a DWORD Value and set to it to 1 Edit the following registry key to allow other machines to use file sharing via the alias:.The Secondary server (SQLVM2) was able to see this location, but the Primary server couldn’t, I just kept getting prompted for credentials, even after filling in the correct credentials each time, and having sufficient permissions! It seems this is the default behaviour in Windows, and in the end I had to make the following changes to get this working… So for example, when specifying the database backup location, I was trying to use a DNS record (called SQLVM1-DR in this case), that referenced the very server (SQLVM1) that I was working from: The big difference in this environment, was that this implementation had DNS records in place to route the DR (Disaster Recovery) traffic via it’s own dedicated link. I stumbled across this problem the other day whilst trying to setup a new Log Shipping configuration in SQL Server.