We help IT Professionals succeed at work.

log shipping problem

I have MS SQL 2008 and RedGate backup tool. I use Redgate backup tool to do log shipping setup. I have the following error.

Setting up log shipping - Failed
================================

Backing up source database - Successful
---------------------------------------

This operation completed successfully.

Backing up TestDatabase (full database) on TEST instance to:
F:\Backup\MSSQL10.TEST\MSSQL\Data\SEED_TestDatabase_20111202150613.sqb
Database size       : 11.000 MB
Compressed data size: 181.500 KB
Compression rate    : 98.39%
Processed 184 pages for database 'TestDatabase', file 'TestDatabase' on file 1.
Processed 2 pages for database 'TestDatabase', file 'TestDatabase_log' on file 1.
BACKUP DATABASE successfully processed 186 pages in 0.052 seconds (27.925 MB/sec).
SQL Backup process ended.



Restoring database to destination server - Failed
-------------------------------------------------

This operation failed with errors.

Restoring TestDatabase (database) from:
\\MSSQL-TEST1\NetworkShare\SEED_TestDatabase_20111202150613.sqb
Error 890: RESTORE DATABASE permission denied for database:  (TestDatabase)
SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'CMI\john' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you
do not have permission.
SQL Backup exit code: 890
SQL error code: 15157



Creating backup job - Pending
-----------------------------

Operation pending.


Create restore job - Pending
----------------------------

Operation pending.


Refresh server information - Pending
------------------------------------

Operation pending.


I don't understand where the error comes from?
 the database principal 'CMI\john' means the source database or destination database?

 its corresponding server principal means the source database or destination database?

can someone provide help for this?
Comment
Watch Question

Commented:
You can deal with it in two ways:-
1. As our Expert MohammedU suggested you to update the regestry key from the link .
2. you can create the missing sql server login from the primary server to secondary. You can script the login from the primary server and create it into the secondary.

Author

Commented:
TempDBA:

I am kind of concerned about the option 1) and I am interested in option 2).

For option 2), do you mean I need to add a new user "Setuser" in source SQL server and destionation SQL server? I am using Windows Authentication mode, if so, I have to create user "Setuser" in Windows server level, right?

Commented:
The concept is that when the backup software goes to restore, it uses a Setuser which is going away, but the SETUSER statement cannot be used on a Windows Login.  So the registry entry illustrated in the first posters comments is the way you get by this.  It skips checks.

Because you have a Windows login CMI\john, SETUSER will not work, so you need to use the registry entry.

Author

Commented:
Hi,

According to the link

1. Using Regedit navigate to the following Registry Folder:
HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

2. Create a new DWORD type registry key called SkipChecks and give this new key a data value of 1.

I don't understand step 2 here.

Does it mean I create a MS Word file named SkipChecks and give a value 1 there and put it to where?

Author

Commented:
Also, I installed RedGate in source machine and the backup shared folder in destination machine, the destination machine has no redgate installed. Also, I just checked the source machine Regedit, the path is kind of different.

Here is my path
HKEY_LOCAL MACHINE\Software\Red Gate\download\SQL Backup\6,

I don't see this path

BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

Author

Commented:
Do you mean I need to create this portion BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

to the path of my registry?
Commented:
wasabi3689:
>> According to the link

>>1. Using Regedit navigate to the following Registry Folder:
>>HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

>>2. Create a new DWORD type registry key called SkipChecks and give this new key a data value of 1.

Step 2 is merely saying that there is a key name of SkipChecks and a value that is a DWORD which means Double Word, and it is a type of value in the registry.  You would indicate in REGEDIT that you want to create  a new key named SkipChecks and then add a value for this key that is of type DWORD and the value is 1.

Look at the graphics.  First you find the right registry folder or create it by right clicking on the BackupSettingsGlobal folder and choosing New.
Then after the folder is there.  You will right click on the folder (LOCAL) or <SQL Instance Name> and choose New -> DWORD value (32-bit)
then you will see where you can name it.  You name it SkipChecks and then double click on the SkipChecks and you can then put a value in.  Select Decimal and change the 0 to a 1.

Once you click OK, you are done and it is saved.
Regedit-Key.jpg
Regedit-value.jpg

Author

Commented:
good information