restore with 70 + moves will not run

cferdig

I have been trying to get this script to run but will not. Is there a limit to the number of moves? Is this script not right? I have used the unc path all the time for other restores without issue as well.
If I remove a few of the moves it will start to work but then my db is in a restoring state. Desperatly need to get this going. Any Help Please

Version4.5.0.740

Just errors:
exitcode 0
sqlerrorcode 0


EXEC master..sqlbackup N'-SQL "RESTORE DATABASE [Leadman] FROM DISK = ''\\titan\sql_backups\SQL2\Leadman\FULL_20080202.bk1'',
DISK = ''\\titan\sql_backups\SQL2\Leadman\FULL_20080202.bk2'',
DISK = ''\\titan\sql_backups\SQL2\Leadman\FULL_20080202.bk3''
WITH STANDBY = ''c:\undo.ldf'',
MOVE ''Leadman'' TO ''J:\SQLData\Leadman\Leadman.MDF'',
MOVE ''Index01a'' TO ''J:\SQLData\Leadman\Index01a.ndf'',
MOVE ''Index02a'' TO ''J:\SQLData\Leadman\Index02a.ndf'',
MOVE ''Index02b'' TO ''J:\SQLData\Leadman\Index02b.ndf'',
MOVE ''Index03a'' TO ''H:\SQLdata\Leadman\Index03a.ndf'',
MOVE ''Index03b'' TO ''H:\SQLdata\Leadman\Index03b.ndf'',
MOVE ''Index03c'' TO ''H:\SQLdata\Leadman\Index03c.ndf'',
MOVE ''MSTRDTA01a'' TO ''J:\SQLData\Leadman\MSTRDTA01a.ndf'',
MOVE ''TRANDTA_200402a'' TO ''J:\SQLdata\Leadman\TRANDTA_200402a.ndf'',
MOVE ''TRANDTA_200403a'' TO ''J:\SQLdata\Leadman\TRANDTA_200403a.ndf'',
MOVE ''TRANDTA_200404a'' TO ''J:\SQLdata\Leadman\TRANDTA_200404a.ndf'',
MOVE ''TRANDTA_200405a'' TO ''J:\SQLdata\Leadman\TRANDTA_200405a.ndf'',
MOVE ''TRANDTA_200406a'' TO ''J:\SQLdata\Leadman\TRANDTA_200406a.ndf'',
MOVE ''TRANDTA_200407a'' TO ''J:\SQLdata\Leadman\TRANDTA_200407a.ndf'',
MOVE ''TRANDTA_200408a'' TO ''J:\SQLdata\Leadman\TRANDTA_200408a.ndf'',
MOVE ''TRANDTA_200409a'' TO ''J:\SQLdata\Leadman\TRANDTA_200409a.ndf'',
MOVE ''TRANDTA_200410a'' TO ''J:\SQLdata\Leadman\TRANDTA_200410a.ndf'',
MOVE ''TRANDTA_200411a'' TO ''J:\SQLdata\Leadman\TRANDTA_200411a.ndf'',
MOVE ''TRANDTA_200412a'' TO ''J:\SQLdata\Leadman\TRANDTA_200412a.ndf'',
MOVE ''TRANDTA_200501a'' TO ''J:\SQLdata\Leadman\TRANDTA_200501a.ndf'',
MOVE ''TRANDTA_200502a'' TO ''J:\SQLdata\Leadman\TRANDTA_200502a.ndf'',
MOVE ''TRANDTA_200503a'' TO ''J:\SQLdata\Leadman\TRANDTA_200503a.ndf'',
MOVE ''TRANDTA_200504a'' TO ''J:\SQLdata\Leadman\TRANDTA_200504a.ndf'',
MOVE ''TRANDTA_200505a'' TO ''J:\SQLdata\Leadman\TRANDTA_200505a.ndf'',
MOVE ''TRANDTA_200506a'' TO ''J:\SQLdata\Leadman\TRANDTA_200506a.ndf'',
MOVE ''TRANDTA_200507a'' TO ''J:\SQLdata\Leadman\TRANDTA_200507a.ndf'',
MOVE ''TRANDTA_200508a'' TO ''J:\SQLdata\Leadman\TRANDTA_200508a.ndf'',
MOVE ''TRANDTA_200509a'' TO ''J:\SQLdata\Leadman\TRANDTA_200509a.ndf'',
MOVE ''TRANDTA_200510a'' TO ''J:\SQLdata\Leadman\TRANDTA_200510a.ndf'',
MOVE ''TRANDTA_200511a'' TO ''J:\SQLdata\Leadman\TRANDTA_200511a.ndf'',
MOVE ''TRANDTA_200512a'' TO ''J:\SQLdata\Leadman\TRANDTA_200512a.ndf'',
MOVE ''TRANDTA_200601a'' TO ''J:\SQLdata\Leadman\TRANDTA_200601a.ndf'',
MOVE ''TRANDTA_200602a'' TO ''J:\SQLdata\Leadman\TRANDTA_200602a.ndf'',
MOVE ''TRANDTA_200603a'' TO ''J:\SQLdata\Leadman\TRANDTA_200603a.ndf'',
MOVE ''TRANDTA_200604a'' TO ''J:\SQLdata\Leadman\TRANDTA_200604a.ndf'',
MOVE ''TRANDTA_200605a'' TO ''J:\SQLdata\Leadman\TRANDTA_200605a.ndf'',
MOVE ''TRANDTA_200606a'' TO ''J:\SQLdata\Leadman\TRANDTA_200606a.ndf'',
MOVE ''TRANDTA_200607a'' TO ''J:\SQLdata\Leadman\TRANDTA_200607a.ndf'',
MOVE ''TRANDTA_200608a'' TO ''J:\SQLdata\Leadman\TRANDTA_200608a.ndf'',
MOVE ''TRANDTA_200609a'' TO ''J:\SQLdata\Leadman\TRANDTA_200609a.ndf'',
MOVE ''TRANDTA_200610a'' TO ''J:\SQLdata\Leadman\TRANDTA_200610a.ndf'',
MOVE ''TRANDTA_200611a'' TO ''J:\SQLdata\Leadman\TRANDTA_200611a.ndf'',
MOVE ''TRANDTA_200612a'' TO ''J:\SQLdata\Leadman\TRANDTA_200612a.ndf'',
MOVE ''TRANDTA_200701a'' TO ''J:\SQLdata\Leadman\TRANDTA_200701a.ndf'',
MOVE ''TRANDTA_200702a'' TO ''J:\SQLdata\Leadman\TRANDTA_200702a.ndf'',
MOVE ''TRANDTA_200703a'' TO ''J:\SQLdata\Leadman\TRANDTA_200703a.ndf'',
MOVE ''TRANDTA_200704a'' TO ''J:\SQLdata\Leadman\TRANDTA_200704a.ndf'',
MOVE ''TRANDTA_200705a'' TO ''J:\SQLdata\Leadman\TRANDTA_200705a.ndf'',
MOVE ''TRANDTA_200706a'' TO ''J:\SQLdata\Leadman\TRANDTA_200706a.ndf'',
MOVE ''TRANDTA_200707a'' TO ''J:\SQLdata\Leadman\TRANDTA_200707a.ndf'',
MOVE ''TRANDTA_200708a'' TO ''J:\SQLdata\Leadman\TRANDTA_200708a.ndf'',
MOVE ''TRANDTA_200709a'' TO ''J:\SQLdata\Leadman\TRANDTA_200709a.ndf'',
MOVE ''TRANDTA_200710a'' TO ''J:\SQLdata\Leadman\TRANDTA_200710a.ndf'',
MOVE ''TRANDTA_200711a'' TO ''J:\SQLdata\Leadman\TRANDTA_200711a.ndf'',
MOVE ''TRANDTA_200712a'' TO ''J:\SQLdata\Leadman\TRANDTA_200712a.ndf'',
MOVE ''TRANDTA_200801a'' TO ''J:\SQLdata\Leadman\TRANDTA_200801a.ndf'',
MOVE ''TRANDTA_200802a'' TO ''J:\SQLdata\Leadman\TRANDTA_200802a.ndf'',
MOVE ''TRANDTA_200803a'' TO ''J:\SQLdata\Leadman\TRANDTA_200803a.ndf'',
MOVE ''TRANDTA_200804a'' TO ''J:\SQLdata\Leadman\TRANDTA_200804a.ndf'',
MOVE ''TRANDTA_200805a'' TO ''J:\SQLdata\Leadman\TRANDTA_200805a.ndf'',
MOVE ''TRANDTA_200806a'' TO ''J:\SQLdata\Leadman\TRANDTA_200806a.ndf'',
MOVE ''TRANDTA_200807a'' TO ''J:\SQLdata\Leadman\TRANDTA_200807a.ndf'',
MOVE ''TRANDTA_200808a'' TO ''J:\SQLdata\Leadman\TRANDTA_200808a.ndf'',
MOVE ''TRANDTA_200809a'' TO ''J:\SQLdata\Leadman\TRANDTA_200809a.ndf'',
MOVE ''TRANDTA_200810a'' TO ''J:\SQLdata\Leadman\TRANDTA_200810a.ndf'',
MOVE ''TRANDTA_200811a'' TO ''J:\SQLdata\Leadman\TRANDTA_200811a.ndf'',
MOVE ''TRANDTA_200812a'' TO ''J:\SQLdata\Leadman\TRANDTA_200812a.ndf'',
MOVE ''TRANDTA_200901a'' TO ''J:\SQLdata\Leadman\TRANDTA_200901a.ndf'',
MOVE ''TRANDTA_200902a'' TO ''J:\SQLdata\Leadman\TRANDTA_200902a.ndf'',
MOVE ''TRANDTA_200903a'' TO ''J:\SQLdata\Leadman\TRANDTA_200903a.ndf'',
MOVE ''TRANDTA_200904a'' TO ''J:\SQLdata\Leadman\TRANDTA_200904a.ndf'',
MOVE ''TRANDTA_200905a'' TO ''J:\SQLdata\Leadman\TRANDTA_200905a.ndf'',
MOVE ''TRANDTA_200906a'' TO ''J:\SQLdata\Leadman\TRANDTA_200906a.ndf'',
MOVE ''TRANDTA_200907a'' TO ''J:\SQLdata\Leadman\TRANDTA_200907a.ndf'',
MOVE ''TRANDTA_200908a'' TO ''J:\SQLdata\Leadman\TRANDTA_200908a.ndf'',
MOVE ''TRANDTA_200909a'' TO ''J:\SQLdata\Leadman\TRANDTA_200909a.ndf'',
MOVE ''TRANDTA_200910a'' TO ''J:\SQLdata\Leadman\TRANDTA_200910a.ndf'',
MOVE ''TRANDTA_200911a'' TO ''J:\SQLdata\Leadman\TRANDTA_200911a.ndf'',
MOVE ''TRANDTA_200912a'' TO ''J:\SQLdata\Leadman\TRANDTA_200912a.ndf'',
MOVE ''TRANDTA01a'' TO ''J:\SQLdata\Leadman\TRANDTA01a.ndf'',
MOVE ''Leadman_Log'' TO ''O:\TransactionLogs\Leadman\Leadman_Log.ldf'', REPLACE"'

Jason Cook

Hi there,
Unfortunately there is a limitation in version 4.x where the maximum length of the command allowed is approximately 4,000 characters (yours is approximately 6,000).

The quickest solution (if this is a one-off case) would be to download to SQL Backup 5 trial, and use that to perform the command (which will work since SQL Backup 5 allows commands up to 65,000 characters in length). You can uninstall and downgrade back to version 4 afterwards.

Alternatively, you could shorten the "move to" locations so that the command drops below the 4,000 character limit. The command should then execute successfully.

Hope that helps,
Jason

petey

Jason is correct, there is a limitation of ~4000 characters for the sqlbackup parameter for version 4. Even if you truncate the command down to < 4000 characters, the command may still fail as the overly long command causes some internal functions to fail.

As suggested, using a version 5 trial would address the issue. If this is not feasible, here is another workaround using the -USE parameter.

- set up a SQL Backup setting in the registry that contains the restore command. SQL Backup settings are stored in HKLM\Software\Red Gate\SQL Backup\BackupSettings\. The naming convention is <settingname>:::<servername>. E.g. bigrestore:::server01. The setting data is a string value. Enter the restore command as the data e.g.

-sql "RESTORE DATABASE Leadman FROM DISK = ..."

- this then allows you to pass long commands to the SQL Backup engine by using the -USE parameter. However, as mentioned earlier, the server components still cannot handle them correctly. Because of this, you need to download a patched SQL Backup command line interface app from http://www.yohz.com/downloads/SQLBackupC.zip. Using the command line interface has a lower change impact than replacing the extended stored procedure and service components.

- replace the existing command line interface with the downloaded one. Run it using the -USE parameter to perform your restore e.g.

sqlbackupc -USE bigrestore