Sql-server – SQL Server error “Missing family sequence number 2”

restoresql serversql-server-2008-r2ssms

I'm trying to restore a local database on my server, but I'm having problems with the backup. I did a file .bak but every time I try to load it on the server it doesn't let me.

Here's a screenshot of the error message I receive:

enter image description here

I read that I can use RESTORE LABELONLY to restore my file but still not working, every time I run it I get something like this:

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.JOSEMA\MSSQL\Backu\INNOVA-T.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
RESTORE LABELONLY is terminating abnormally.

Any help would be great. Thank you.

Best Answer

The error indicates that your .bak file is only a part of the backup. When the backup was written it was written across three files. If you can write a backup to multiple files on multiple drives, it will often complete faster.

According to this StackOverflow Q & A, RESTORE LABELONLY should help you confirm that the backup spans multiple files, but it won't let you actually restore the backup - you'd need all three files for that.

If you can locate all three files (and are sure they're from the same run of the backup), you should be able to restore the backup - you'll need to specify all three files in the RESTORE command (or through the SSMS restore dialog).

IF you can't, but you still have access to the original server, then you can take a new backup.

NOTE: If that original server has a DBA, work with him. You want to make sure that you taking your backup won't cause issues with any replication, log shipping, or ability to restore to a point in time.

If you're effectively the DBA, then:

  • Get into SQL Server Management Studio (SSMS), connect to the server, and expand Databases.
  • Right-click on the original database, and choose Tasks -> Backup... A dialog window will open.

  • On the first page of the dialog:

    • Make sure the database you want to back up is selected as the Source database.
    • Make sure the Backup Type is "Full".
    • Check on "Copy Only Backup".
    • Make sure backup component is set to "Database"
    • At the bottom of the window: if there are any existing entries in the "Destination" list, remove them.
    • Click "Add" to add a new backup file. Make sure that you know where the file will be (generally, you've only got the option to write to the DB server's local disk using this interface; if you need to write to a folder on a shared server, you can try pasting in the full UNC path to the shared server and entering the new file name in the file selection dialog. If that doesn't work, specify a local drive and a unique file name). Also, make sure the file name is unique - you don't want to overwrite any existing files on the server.
    • When you get back to the original Back Up dialog window, change to the "Options" page (left side of the window).
    • Under "Overwrite Media", select "Back up to a new media set, and erase all existing backup sets".
      • Yes, this sounds scary - however, this is primarily used to reinitialize a backup tape so any old data is erased; as long as you're writing to a new file name, everything should be fine.
      • Enter a new for the media set - something like " - one time backup" is sufficient.
    • You can set some of the options under "Reliability"; I'd just leave them all off.
    • You can accept the default for "Set backup compression" as well; I'd probably set tit to "Compress backup", but that's not critical. (If you're not running SQL Server 2008 or later, then this is either not there at all, or not relevant).
    • DO NOT click "OK", instead, click the little arrow by "Script" at the top of the dialog window, and choose "Script Action to New Query Window". Once you can see the script, click "Cancel".
  • Double-check the script - there should only be one file included in it.
  • If you wanted to write the file to a different location, you can change the file path in the script. Again, the server needs to be able to access the location you specify, so the account used to run the SQL Server service (and/or the SQL Server Agent service) needs to have access to the path where you want the file written.
  • Now, run the script.

Assuming all goes well, you can now use your new backup file to perform the restore to the other location. Again, the SQL Server you're restoring to will need to have access to the backup file, so you may want to copy it to that server.

Related Question