none
User did an append when doing a backup of SQL now trying to do a restore

    Question

  • User did a backup of SQL doing an append now getting the message:

    The media set has 2 media families.....

    The directory of the first media set was copied to an external drive.  The system was then wiped to load Win7 what can be done to restore the files?


    Bonnie Whalon

    Monday, April 07, 2014 1:45 PM

Answers

  • Again you are missing the point rights is required from account with which SQL Server services are running.Go to run type services.msc window will pop up in that locate SQL server Services and check whicih account is running SQL server.Add that account in security tab of folder where you are trying to take backup .Provide read/write privileges

    RK on folder select properties click security table add sql server service account ..give proper rights


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 09, 2014 9:20 PM

All replies

  • Hello,

    Use below command to find your file no

    restore filelistonly from disk='location'

    Then restore query would be

     restore database db_name from disk='backup location'
    with replace,file=x,recovery
    --X is file number in backup


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 07, 2014 2:20 PM
  • Will I be able to find this file if they wipe the system and reload it?

    Bonnie Whalon

    Monday, April 07, 2014 3:50 PM
  • Will I be able to find this file if they wipe the system and reload it?

    Bonnie Whalon


    I guess if system is wiped out/deleted backup files as well are deleted .You require physical file to restore from it without that you cannot restore  database.Did I understand you correctly

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 07, 2014 3:55 PM
  • They copied the main backup file but each time the restore is tried it states a file is missing from location 2.

    Apparently it was missed.


    Bonnie Whalon

    Monday, April 07, 2014 4:01 PM
  • They copied the main backup file but each time the restore is tried it states a file is missing from location 2.

    Apparently it was missed.


    Bonnie Whalon

    I guess than it is not possible.Can you post output of below

    restore filelistonly from disk='location'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, April 07, 2014 6:11 PM
  • Thank you for your help.  I'm guessing this is restoring from the command prompt.  I have only done a restore from the MS Studio.  What are the other commands that I need?  Is it this?

    restore database db_name from disk='backup location'
    with replace,file=x,recovery
    --X is file number in backup


    Bonnie Whalon

    Monday, April 07, 2014 7:09 PM
  • >> I'm guessing this is restoring from the command prompt.

    No this command is from SSMS.Just go to new query window and run this command .Query would be like

    restore filelistonly from disk='location'
    Above query is to check number of backup files in your backup dump .It is typo I will correct it.Location is your backup location D:\Program files\...(Something like this)

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Monday, April 07, 2014 9:19 PM
    Monday, April 07, 2014 9:18 PM
  • Thank you.  I'll try it and get back to you.


    Bonnie Whalon

    Tuesday, April 08, 2014 11:47 AM
  • Hello,

    The command I use was:

     

    restore filelistonly FROM DISK = N'C:\Freedom Ente\Client Data Backup\Sewickley-fsc-backup-20140403' The error message Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:\Freedom Ente\Client Data Backup\Sewickley-fsc-backup-20140403'. Operating system error 5(Access is denied.)

    .

    Msg 3013, Level 16, State 1, Line 1

    RESTORE FILELIST is terminating abnormally.

    I gave myself full rights to the directory.


    Bonnie Whalon


    • Edited by bwhalon Wednesday, April 09, 2014 7:28 PM
    Wednesday, April 09, 2014 7:26 PM
  • Hello,

    Access denied error means here that SQL server service account( Not account with which you have logged into windows box) does not have read/write permission on folder where this backup resides so it cannot check contents of its file.You need to provide this rights first.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 09, 2014 7:30 PM
  • Thank you.


    Bonnie Whalon

    Wednesday, April 09, 2014 8:00 PM
  • restore filelistonly FROM  DISK = N'C:\Sewickley-fsc-backup-20140403'
    
    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device 'C:\Sewickley-fsc-backup-20140403'. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    I gave my self rights to the directory and admin rights to the database.  Moved the directory to the root of C:\

    Any suggestions are appreciated!


    Bonnie Whalon

    Wednesday, April 09, 2014 9:12 PM
  • Again you are missing the point rights is required from account with which SQL Server services are running.Go to run type services.msc window will pop up in that locate SQL server Services and check whicih account is running SQL server.Add that account in security tab of folder where you are trying to take backup .Provide read/write privileges

    RK on folder select properties click security table add sql server service account ..give proper rights


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 09, 2014 9:20 PM
  • Our client does utility billing and the bills needed to be sent out.  They had hard copies of the accounts.  I suggested they update the accounts and move on.  I would still like to try and do the restore on a test system. 

    Have you ever did a restore using this method?

    Thank you,


    Bonnie Whalon

    Tuesday, April 22, 2014 12:45 PM