Restoring SQL DB to Another Server

[GregE]

I am trying to restore an SQL DB to a completely different server (different physically server and different SQL server instance).

My command launches the GUI just fine, and shows me the databases:

tdpsql.exe /tsmoptfile=F:\TSM\TDPSql\dsm_SERVER_A.opt /sqlserver=SERVER_B\INSTANCE1 /configfile=tdpsql.cfg

When attempting restore, I get this error:

02/04/2010 16:12:42 Restore of DB1 failed.
02/04/2010 16:12:42 An exception occurred while executing a Transact-SQL statement or batch.
02/04/2010 16:12:42 Cannot create the full-text catalog in the directory "R:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ftCatalog_DB1" for the clustered server. Only directories on a disk in the cluster group of the server can be used.
02/04/2010 16:12:42 File 'sysft_ftCatalog_DB1' cannot be restored to 'R:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ftCatalog_DB1'. Use WITH MOVE to identify a valid location for the file.
02/04/2010 16:12:42 Problems were identified while planning for the RESTORE statement. Previous messages provide details.
02/04/2010 16:12:42 RESTORE DATABASE is terminating abnormally.
02/04/2010 16:12:42 Changed database context to 'master'. (HRESULT:0x80131501)

That directory path on "R:" is where the files came FROM. I specifed in the GUI to put them into "F:\MSSQL\Data\DB1\" on this new server. It's ignoring that.

I've had the SQL DBA ensure that I have sysadmin rights to SQL server to make sure my problem isn't due to permissions.

I've also found this technote, but did not change anything.
http://www-01.ibm.com/support/docview.wss?rs=667&uid=swg21395947

Anyone have this issue figured out a way around it?

 

[GregE]

TDPSQL is 5.5.3.1

I'm getting convinced that it is paying no attention to the /relocatedir information. It's trying to put files into a drive/path they came from, and not where I'm telling it to go.

Command line attempt:
tdpsqlc restore DB1 FULL /fromsqlserver=SERVER_A\INSTANCE1 /mountwait=yes /relocatedir=F:\MSSQL\Data\DB1,F:\MSSQL\Log\DB1,F:\MSSQL\Log\DB1 /tsmoptfile=F:\TSM\TDPSql\dsm_DB1.opt /tsmpassword=xxxxxxxx
/sqlserver=SERVER_B\INSTANCE1 /configfile=tdpsql.cfg

I've tried the GUI, and I've tried command line. Same result. Ignoring my relocation settings and giving the same error.

 

[GregE]

New info.

I was able to restore ONLY DBs that do not have a full-text index. (the only ones I had tried before my original post just happened to have full-text indexse). Those are the only files that try to go to the original drive path, so the entire restore fails for those particular DBs.

Now the question becomes, how can I restore DBs that have full-text indexes if the backup is storing this information and insisting on restoring them to that non-existent path??

For the /RECLOCATEDir option, the documentation clearly states:

*********

/RELOCATEDir=dbfiledir[ ,logiledir [ ,otherfiledir] ]
...The dbfiledir variable specifies the directory location of the SQL database you want to relocate. Note that if the logfiledir and/or otherfiledir variables are not specified, the logs and SQL Server full-text index files are restored to the directory specified by dbfiledir.

The logfiledir variable specifies the directory location of the SQL log files you want to relocate. Note that if the logfiledir variable is not specified, the SQL log files are restored to the directory specified by dbfiledir.

The otherfiledir variable specifies the directory location of the SQL Server full-text index files you want to relocate. Note that if the otherfiledir variable is not specified, the SQL Server full-text index files are restored to the directory specified by dbfiledir......

*********

So even if I don't specify "otherfiledir," it should still restore the full-text indexes, just to the same place as the DB. In my case, they're not restoring at all and preventing the entire DB from restoring.

 

[GregE]

Workaround found, and tested successfully. I spoke with IBM. Here is the info:

".......development indicated that the older 5.2.1.6 TDP SQL did not backup the text catalog as a separate object. Starting with the TDP SQL 5.5, the full text catalog was saved as a separate object to the TSM Server, thus allowing for more functionality during the restore processing. This older backup can not be relocated via TDP SQL GUI; all the 'Relocate' page options are applied to the backup objects only. The only means to relocate the text catalog from these older backups is the commandline syntax using the /Relocate and /To options......"

 

[moorecj79]

Thanks GregE - I had the same problem and your solution resolved it. Cheers!!