What are the challenges you face when working across database platforms? Take the survey
Options

restore with 70 + moves will not run

cferdigcferdig Posts: 2
edited February 6, 2008 4:58AM in SQL Backup Previous Versions
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"'

Comments

  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.