locked
Error Number: 3259 in SQL Server Management studio An exception occurred while executing a Transact-SQL statement .The volume on device 'D:\DB Backups' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. RRS feed

  • Question

  • I'm trying to get restore from one database to another one.

    Well, by mistake,I have deleted some rows from main tabel so I made new table and I just trying to restore base on Most recent transactional log or back up file...

    So, I right click on new DB and click on Task-->Restore-->Database and  it popup new widow when I selected From database "dbname" ...it gave me list of most recent backup sets to restore...I choose from that list base on my requirement like most recent one.

    and when I pressed OK it will give me error:

    Error Number: 3259 in SQL Server Management studio An exception occurred while executing a Transact-SQL statement .The volume on device 'D:\DB Backups' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.

    or sometimes error is like this: Restore failed for server 'ServerName\XXXX'

    Additional Information: system.data.SqlClient.sqlError:the backup set holds a backup of a database other than the existing 'NewDB' database. (Microsoft.Sqlserver.Smo)

    How can I solve this error...and how can I get perticular dated table data...Well table is everyday adding data in it.

    Please tell me how can I get those data which I deleted...or how can I get all data from previous backup/transacton log ,I have to restore whole copy of data in another table before I did apply delete command...!?

    Please, send me solution...!

    Thursday, July 28, 2011 1:31 PM

Answers

  • Unfortunately it looks like  your backup file is corrupted.. What if you run

     

    RESTORE DATABASE dbname FROM DISK=N'D:\backupsql\DB.bak'

    WITH RECOVERY,

       MOVE 'DB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB1.mdf', 

       MOVE 'DB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_log1.ldf'

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 2, 2011 12:30 PM
  • As error message states there is no such file InforEAM.bak , is that possible? Do you have permission read/write on that disk/folder?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 2, 2011 12:56 PM
  • Hi,

    From the error message, it seems that the file is not exist or corrupted. Could you please double check that? Meanwhile, please try to restore the database using T-SQL using the following steps:

    1. Run RESTORE FILELISTONLY to list all files in the backup
    2. Run RESTORE DATABASE.. WITH MOVE to restore the database

    If the error persists, please provide the complete error message, the command you run, and @@VERSION information of your server.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by YaminiTic Wednesday, August 10, 2011 11:37 AM
    Wednesday, August 10, 2011 2:13 AM

All replies

  • Plz...if anybody help me out then it's very good for me.
    YaminiTic
    Thursday, July 28, 2011 6:06 PM
  • Could you please try and run the command : - restore headeronly from disk = '<backup file name>'  ? and check the database name in there.

    once you confirm thats the database backup you are looking for try : 

    Restore database <new database_name> from disk = '<file path>' 

     

    you could then get the data from the resotred backup and could transfer to the old database. 

     

     

     

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Sunday, July 31, 2011 1:58 PM
  •  

    I would make sure the following points

    Are you taking striped backup i.e taking the single database backup to the multiple disk drives?

    Are you taking your db backup to single backup file?

    The volume on device 'D:\DB Backups' is not part of a multiple family media set

    The above statement says that you are using multiple files to restore but you have taken backup to single file, Just make sure this point. 

    Please try to restore the db backup using T-SQL and post the same command. 

    See the SQL Server Errorlog file to find the backup location and to find when was the last full backup. 

    You can use the following msdb system tables to find the backup related information 

     

    select * from BackupMediaSet 
    select * from BackupFile
    select * from BackupMediaFamily
    select * from BackupSet
    

    Please tell me how can I get those data which I deleted...or how can I get all data from previous backup/transacton log ,I have to restore whole copy of data in another table before I did apply delete command...!?

    You should use the Point in time recovery to recover the deleted data from the table. 

    Make sure you have the following backup strategy You should have had configured log backup when your db in FULL recovery and not in SIMPLE and BULK-LOGGED. 

    Follow the below steps to recovery in point in time

    1. Restore you latest FULL backup with the different db name and NORECOVERY

    2. Latest differential with NOREOCVERY

    3. Start restoring all the transaction log backup files and use the STOP AT option by giving the data and time just before you delete the data from table 

    Example Point in time. 

    RESTORE DATABASE database_name FROM full_backup 
      WITH NORECOVERY;
    RESTORE DATABASE database_name FROM full_differential_backup 
      WITH NORECOVERY;
    RESTORE LOG database_name FROM log_backup 
      WITH STOPAT = time, RECOVERY;
    RESTORE LOG database_name FROM log_backup 
      WITH STOPAT = time, RECOVERY;
    

    http://www.mssqltips.com/tutorial.asp?tutorial=119

    http://blogs.msdn.com/b/dfurman/archive/2010/07/28/point-in-time-restore-and-knowing-where-to-stop.aspx

    Hope this solves your problem. 



     


    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Monday, August 1, 2011 6:56 AM
  • Thanks for reply,,,,

    now my requirement is I need to get the whole database resote for last month's 26th date...that means July 26th 2011,

    Well I just created new database n apply restore by clicking tTo a point in Time : 07/26/2011 6:45:44 PM

    and choose my original Databse s From DB...now It will give me list of backup Sets to restore so I choose the backup set which was taken on 26th July 2011 and press OK but it gives me eror.

    Error: Restore failed for Server 'Server_Name'. (Microsoft.Sqlserver.SmoExtended)

             Additional Information: System.Data.SqlClient.SqlError:Cannot open backup device

             '{XXXXXX-XXX-XXXX-XXXX-XXXXX{6'. Operating system error 2(The system cannot find the file specified.).(Microsoft.Sqlserver.Smo)

    What I need to do here....!?


    YaminiTic
    Tuesday, August 2, 2011 11:59 AM
  • Unfortunately it looks like  your backup file is corrupted.. What if you run

     

    RESTORE DATABASE dbname FROM DISK=N'D:\backupsql\DB.bak'

    WITH RECOVERY,

       MOVE 'DB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB1.mdf', 

       MOVE 'DB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_log1.ldf'

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 2, 2011 12:30 PM
  • When I Run this above script it's give me error like thie

    Msg 3201, Level 16, State 2, Line 1

    Cannot open backup device 'D:\DB Backups\InforEAM.bak'. Operating system error 2(The system cannot find the file specified.).

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    Now what I need to do?


    YaminiTic
    Tuesday, August 2, 2011 12:52 PM
  • When I Run this above script it's give me error like thie

    Msg 3201, Level 16, State 2, Line 1

    Cannot open backup device 'D:\DB Backups\InforEAM.bak'. Operating system error 2(The system cannot find the file specified.).

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    Now what I need to do?


    YaminiTic
    Tuesday, August 2, 2011 12:53 PM
  • As error message states there is no such file InforEAM.bak , is that possible? Do you have permission read/write on that disk/folder?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 2, 2011 12:56 PM
  • Hello Uri,

    Well,in my server D: drive is setup for back device/drive.

    I can't open C: drive's SQL folder,,,..but there is nothing in Data or Backup folder of C:\Program Files\Microsoft SQL Server.

    So same like above I don't hv to disturb current database in which 200 records are deleted from one table in last month.

    So ,I created one newDB and tried to get those 200 records back by using Restore...But it gives me error like this:

    Restore Failed for Server 'Server_Name'(Microsoft.Sqlserver.Smo)

    Additional Information:System.Data.SqlClient.SqlError: Can not open back up device '{XXXX-XX-XXXX-XXXx}6' Operating System error 2 (The system cannot find the file specified).(Microsoft.Sqlserver.Smo)

    So I think now you have idea about this error. I tried by using above option also but not have success yet.

     


    YaminiTic
    Tuesday, August 9, 2011 7:27 PM
  • Hi,

    From the error message, it seems that the file is not exist or corrupted. Could you please double check that? Meanwhile, please try to restore the database using T-SQL using the following steps:

    1. Run RESTORE FILELISTONLY to list all files in the backup
    2. Run RESTORE DATABASE.. WITH MOVE to restore the database

    If the error persists, please provide the complete error message, the command you run, and @@VERSION information of your server.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by YaminiTic Wednesday, August 10, 2011 11:37 AM
    Wednesday, August 10, 2011 2:13 AM