none
Restore erro: "SqlException: The operation did not proceed far enough to allow RESTART"

    Question

  • Hi all,

    I'm trying to restore a backup that I made on a new server I created to the one I made the back on. I'm getting this error on most of my content databases during the restore:

    Object StateService_0e609739aea044189d82651f721ce4fd failed in event OnRestore. For more information, see the spbackup.log or sprestore.log file located in the backup directory.
    SqlException: The operation did not proceed far enough to allow RESTART. Reissue the statement without the RESTART qualifier.
    RESTORE DATABASE is terminating abnormally.

    ==================================================================

    in the sprestore.log:

    BEGIN RESTORE DATABASE [WSS_Content_intranet01] FROM DISK=@db_location WITH STATS=5, FILE=1, MOVE @db_OldName TO @db_NewFile, MOVE @db_OldLogName TO @db_NewLogFile, NOREWIND, NOUNLOAD, RESTART, RECOVERY
    END

     @db_location=\\spserver-test\backups\spbr0003\000000A0.bak, @db_OldName=WSS_Content_intranet01, @db_NewFile=D:\SQL Databases\WSS_Content_intranet01.mdf, @db_OldLogName=WSS_Content_intranet01_log, @db_NewLogFile=D:\SQL Logs\WSS_Content_intranet01_log.ldf, @db_name=WSS_Content_intranet01
    [6/25/2010 10:38:49 AM] Verbose: [WSS_Content_intranet01] SQL command timeout is set to 1.00 hours.
    [6/25/2010 10:38:49 AM] Warning: [WSS_Content_intranet01] The operation did not proceed far enough to allow RESTART. Reissue the statement without the RESTART qualifier.
    RESTORE DATABASE is terminating abnormally.
    [6/25/2010 10:38:49 AM] Debug: [WSS_Content_intranet01]    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.SharePoint.Administration.Backup.SPSqlBackupRestoreHelper.RunCommand(SqlCommand sqlCommand, SPBackupRestoreInformation args, Boolean throwOnRestart, Boolean& restart, SPSqlBackupRestoreConnection connection)
    [6/25/2010 10:38:49 AM] Warning: [WSS_Content_intranet01] SQL command failed and needs to be restarted. The command will be re-started a total of three times before throwing an exception.
    [6/25/2010 10:41:04 AM] Verbose: [WSS_Content_intranet01] Retrying to connect to SQL.
    [6/25/2010 10:41:04 AM] Verbose: [WSS_Content_intranet01] SQL command started at: 6/25/2010 10:41:04 AM. This command may take a while to complete and without notification.
    [6/25/2010 10:41:04 AM] Verbose: [WSS_Content_intranet01] SQL Server Command:
    IF EXISTS ( SELECT * FROM master..sysdatabases WHERE has_dbaccess(name)=1 AND name=@db_name )

    =====================================================================

    I've seen another post which discussed that you needed to use all the same service accounts that were in the original system. I have ensured this and I still get the error. Also, just for testing, I have given "Everyone" full access to the backup folder where I'm trying to restore from.


    Has anyone come across this?

    Thanks,

     

    kevin

    • Moved by Serge Luca [MVP]MVP Saturday, July 03, 2010 5:40 AM (From:SharePoint 2010 - General Questions and Answers)
    Monday, June 28, 2010 6:41 PM

Answers

  • Dear Ruess,

     

    I don't have all the details of you failure but I have a theory and a suggestion. My theory is that the credential running SQL is local to the SQL box or one of the system accounts and thus unable to reach the share, other possibility is network errors. These solutions work for most of the cases though:

     

    Option 1:

    1. Copy your backup locally to the SQL Server. Create a share. Make sure the service running SQL is able to access the share. If you have SQL and SharePoint in the same box (in a standalone installation for example) you won't even need a share. Make sure that you can access the share from the SQL box and from SharePoint box. Run the restore again giving the share as source (or the folder name, eg. C:\backup, if in the same box). See if it fails. If it fails try option 2.

     

    Option 2:

    1. Using SQL Management Studio Restore the database backup (it should be "\\spserver-test\backups\spbr0003\000000A0.bak" in your case) and call it the same name you used in the other farm (you can choose other name if you want as well)

    2. In cmd create a empty site collection in the new farm: stsadm -createsite -url  http://myserver/sites/mysitecollection -owneremail yourname@yorcompany.com -ownerlogin domain\youruser

    3. In Central Administration | Application Management | Site Collection List select the newly created site collection and take note of the database name

    4. Detach the dummy content database: stsadm -deletecontentdb  -url  http://myserver/sites/mysitecollection -databasename <name-from-step-3>

    5. Attach the backup database: stsadm -o addcontentdb -url  http://myserver/sites/mysitecollection -databasename <name-you-chose-in-step-1>

     

    I hope you can use option 1 and avoid all the trouble in Option 2.

    • Proposed as answer by Tracyp - MSFT Wednesday, July 28, 2010 2:15 PM
    • Marked as answer by ruess Thursday, February 03, 2011 9:37 PM
    Thursday, July 01, 2010 7:33 PM

All replies

  • Dear Ruess,

     

    I don't have all the details of you failure but I have a theory and a suggestion. My theory is that the credential running SQL is local to the SQL box or one of the system accounts and thus unable to reach the share, other possibility is network errors. These solutions work for most of the cases though:

     

    Option 1:

    1. Copy your backup locally to the SQL Server. Create a share. Make sure the service running SQL is able to access the share. If you have SQL and SharePoint in the same box (in a standalone installation for example) you won't even need a share. Make sure that you can access the share from the SQL box and from SharePoint box. Run the restore again giving the share as source (or the folder name, eg. C:\backup, if in the same box). See if it fails. If it fails try option 2.

     

    Option 2:

    1. Using SQL Management Studio Restore the database backup (it should be "\\spserver-test\backups\spbr0003\000000A0.bak" in your case) and call it the same name you used in the other farm (you can choose other name if you want as well)

    2. In cmd create a empty site collection in the new farm: stsadm -createsite -url  http://myserver/sites/mysitecollection -owneremail yourname@yorcompany.com -ownerlogin domain\youruser

    3. In Central Administration | Application Management | Site Collection List select the newly created site collection and take note of the database name

    4. Detach the dummy content database: stsadm -deletecontentdb  -url  http://myserver/sites/mysitecollection -databasename <name-from-step-3>

    5. Attach the backup database: stsadm -o addcontentdb -url  http://myserver/sites/mysitecollection -databasename <name-you-chose-in-step-1>

     

    I hope you can use option 1 and avoid all the trouble in Option 2.

    • Proposed as answer by Tracyp - MSFT Wednesday, July 28, 2010 2:15 PM
    • Marked as answer by ruess Thursday, February 03, 2011 9:37 PM
    Thursday, July 01, 2010 7:33 PM
  • Tracyp,

     

    Thanks so much for your reply. I will try and let you know the results!

    Friday, July 02, 2010 11:55 PM
  • Hi, 

    I am having the same issue. In my case, I have a standalone installation. I can access the restore folder. Just need to verify:

    How can I "Make sure that you can access the share from the SQL box and from SharePoint box. "?

    Tried step -2 but it resulted in exception, bad format of 000000A0.bak

    any help? 


    http://farhanfaiz.wordpress.com
    Monday, January 31, 2011 8:01 AM
  • Thanks, it solves my problem.
    Monday, March 19, 2012 10:25 AM
  • Tracyp,

    Thx, you saved my day, option 1 definetly works, but in my case I had one more problem, and it showed in logs the same as problem with permissions:

    SqlException: The operation did not proceed far enough to allow RESTART. Reissue the statement without the RESTART qualifier. 
    RESTORE DATABASE is terminating abnormally.

    Somewhere in the logs was also:

     Warning: [WSS_Content] Directory lookup for the file "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\WSS_Content.mdf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).

    And of course I didn't update file path from the other server :/, that's why I had SqlException, but still it should be also an ERROR, when the path does not exist the database can't be attached to sqlserver and our beloved SqlException with RESTART and RESTORE problems happens.

    Tuesday, May 15, 2012 8:46 AM
  • Hi

    This post saved me as well, but I had one more (embarrassing) error.

    While setting up a VM just to test restore procedures, I could restore all my web apps - but one... obviously the most important one.

    Now SharePoint did not really explain this error properly but it turned out that I had insufficient disk space for the content db. Silly, I know, but I am putting this out there if someone else ever runs into it 

    Thursday, August 02, 2012 7:10 AM
  • YES!  Option 1 worked for my environment. Migrating from a standalone SP 2013 system to a 3-tier environment.
    Monday, July 01, 2013 3:35 PM