Log Shipping Error

NewDBAGirl

I am receiving the below error when the destination server tries to restore the backup from the source server. I'm not sure of what the problem is because I went to the destination server and was able to do the restore by hand from the source server. I'm wondering if it is a security problem, but then the software said that both servers has the correct permissions. Any suggestions?

Error Message:

This operation failed with errors.

Restoring DBName (database) from:
\\ServerName\redGateBackupsForLogShipping\SEED_DBName_20070502152651.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3176: SQL error 3176: File '' is claimed by 'DBName_Log'(2) and 'DBName_Data'(1). The WITH MOVE clause can be used to relocate one or more files.
SQL Backup exit code: 1100
SQL error code: 3176

James Moore

Hi there,

Could you run SQL Profiler against your destination server while trying to do the restore at the end of the log shipping wizard and let me know the actual restore command used? You can email it to me at james<dot>moore<at>red-gate<dot>com.

Many thanks,

- James

NewDBAGirl

Thanks so much for your help. I would have never thought to turn on the SQL Profiler.

Okay, this time it gave me a more descriptive error. It could not find the instance for the destination DB. From the profiler, I see that it is looking for an old server name that no longer exists. The server died on us. We were using an older version of SQL Backup to transmit data to that server. This now is very puzzling because I don't know where it is getting the old server name from.

Perhaps the program is confused I have been communicating with these 2 new servers and surely the name is really changed.

Here is the error that I am getting now:SQL Backup process ended.



Restoring database to destination server - Failed

---

This operation failed with errors.

Restoring DBName (database) from:
\\SERVERNAME\redGateBackUpsForLogShipping\SEED_DBName_20070503121132.sqb
VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had
elapsed.)
Also check that the database is not currently in use.
SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3101: Exclusive access could not be obtained because the database is in use.
SQL Backup exit code: 1010
SQL error code: 3101

NewDBAGirl

I am still struggling with getting the log shipping to work and I am running out of time. One thing that I noticed today is that I can do log shipping and over write a DB that was created by SQL Backup, but it still won't work when trying to over write a DB that we created. (Once while setting up the log shipping, I told SQL Backup to create a new DB on the destination server and it worked.) I have looked at the properties and permissions on both databases and they are the same.

Could you give me some ideas as to what may be the cause since I have narrowed it down to the problem being with a database that I already had :?:

I appreciate your time with helping me with this

James Moore

Hi,

Could you possibly run the following and let me have a copy of the results for the databases involved:

SELECT * FROM master..sysaltfiles

Could you do this on both servers involved as well. If you can either post the results or email them to me at james<dot>moore <at> red-gate <dot>com and we will try and get this issue resolved for you.

For some reason we are not picking up on the logical file names of the database in this case which is causing the initial FULL restore to fail, the UI uses sysaltfiles to get this information from the Servers.

Additionally if you need more time to complete your trial please contact sales@red-gate.com and they should be able to provide you with a trial extension.

Thanks,

- James

Jason Cook

If you are comfortable using the 'regedit' application, could you please check the "SQLDataRoot" value of the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup

If you are using a 64-bit server to run SQL Server, this will be under:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Setup

--

Normally, this will be set to the folder SQL Server was installed at, for example "C:\Program Files\Microsoft SQL Server\MSSQL" or "C:\Program Files (x86)\Microsoft SQL Server\MSSQL".

If this folder is incorrect or missing, it causes SQL Backup to exhibit the behaviour you have described.

If the key is missing:
* Right click the "Setup" folder in the left hand pane
* Select "New >" and String Value
* Set the value to "SQLDataRoot", minus the double quotes
* Double Click on the value, and set it to your SQL Server directory, for example "C:\Program Files\Microsoft SQL Server\MSSQL" for a 32-bit server.

If this doesn't resolve the problem or you have any other questions, feel free to follow up either on the forum, or by contacting James or myself via email. If this does resolve the problem please let us know so that we know that it is a working solution and will aim to fix the problem in a subsequent release.

Thanks,
Jason

NewDBAGirl

Greetings:

Yes, both servers have the \MSSQL Value under the registry.

I am still waiting for a response on this problem.

Your help is greatly appreciated.

Jason Cook

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