none
No Data after restoring multiple files database !

    Domanda

  • Dear All,

    I have a database with multiple files. I have taken a backup of the database and tried to restore it in the test server. When ever i was trying to do that from the GUI it was giving me problems. After that i restore it through some SQL queries i got from the net. Now, I can see the tables, i can see the views, but there is no data at all !!!I can see the headers only!

    I tried again to restore the .bak file via GUI and it's giving me the below message:

    ADDITIONAL INFORMATION:
    System.Data.SqlClient.SqlError: File 'f:\sqldata\MSSQL\data\Test_Data.ndf' is claimed by 'Detail_Index'(4) and 'Detail_Data'(3). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

    Than i have detach the database, moved MDF, LDF, and all NDF files to another drive and tried to reattach it from there but still can't see any data.

    Anyone can help?!

    Thanks
    martedì 24 aprile 2012 06:05

Risposte

  • Hi,

    If you are using GUI for restore, change the file locations to the new one, if using t-sql then use the MOVE in the RESTORE command.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Proposto come risposta Iric WenModerator mercoledì 25 aprile 2012 07:36
    • Proposta come risposta annullata BuRaiR giovedì 26 aprile 2012 04:27
    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    martedì 24 aprile 2012 10:49
  • Hi BuRaiR,

    Another reason for this error is there is another data file with same names are already present in the target folder where you are restoring the database. try restoring with different file name.

    RESTORE DATABASE [DatabaseName]
    FROM DISK = N'C:\DatabaseBackupFile.bak'
    WITH FILE = 1, MOVE N'DataFileName' TO N'C:\MSSQL_DATA\NewDataFileName.mdf',
    MOVE N'LogFileName' TO N'C:\MSSQL_DATA\NewLogFileName.ldf',
    NOUNLOAD, STATS = 10
    GO
    
    


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    • Contrassegno come risposta annullato BuRaiR giovedì 26 aprile 2012 05:38
    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    mercoledì 25 aprile 2012 07:44

Tutte le risposte

  • It seems like you have several backups in the backup file you restore from and you restored some old backup, and when you took that old backup there were o data in the tables in question. Investigate the backup files using RESTORE HEADERONLY, as in:

    RESTORE HEADERONLY FROM DISK = 'C:\backufile.bak'

    And then use the FILE option for the RESTORE command to restore the desired backup in that backup file.


    Tibor Karaszi, SQL Server MVP | web | blog

    martedì 24 aprile 2012 07:19
  • RESTORE HEADERONLY FROM DISK = 'C:\backufile.bak'

    And then use the FILE option for the RESTORE command to restore the desired backup in that backup file.

    Hi Tibor,

    The backup file is new, not old backup. I have tried to restore the headers only and it's successful. Than i tried to restore the files but still giving me error. I realized that when i try to restore the files, go to option, in the "Restore As" its still pointing to the old location

    'f:\sqldata\MSSQL\data\Test_Data.ndf'

    shouldn't it point to the new location?

    martedì 24 aprile 2012 09:51
  • Hi,

    If you are using GUI for restore, change the file locations to the new one, if using t-sql then use the MOVE in the RESTORE command.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Proposto come risposta Iric WenModerator mercoledì 25 aprile 2012 07:36
    • Proposta come risposta annullata BuRaiR giovedì 26 aprile 2012 04:27
    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    martedì 24 aprile 2012 10:49
  • Agree (with Janos' comments above).

    Tibor Karaszi, SQL Server MVP | web | blog

    martedì 24 aprile 2012 11:15
  • Hi BuRaiR,

    Another reason for this error is there is another data file with same names are already present in the target folder where you are restoring the database. try restoring with different file name.

    RESTORE DATABASE [DatabaseName]
    FROM DISK = N'C:\DatabaseBackupFile.bak'
    WITH FILE = 1, MOVE N'DataFileName' TO N'C:\MSSQL_DATA\NewDataFileName.mdf',
    MOVE N'LogFileName' TO N'C:\MSSQL_DATA\NewLogFileName.ldf',
    NOUNLOAD, STATS = 10
    GO
    
    


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    • Contrassegno come risposta annullato BuRaiR giovedì 26 aprile 2012 05:38
    • Contrassegnato come risposta BuRaiR giovedì 26 aprile 2012 05:38
    mercoledì 25 aprile 2012 07:44
  • Hi,

    If you are using GUI for restore, change the file locations to the new one, if using t-sql then use the MOVE in the RESTORE command.

    I hope it helps.

    Janos


    Hi Janos;

    Thanks for your answer, still facing the same issue. As i have stated in my question up; I can see the headers only, but there is no data at all. I have tried your suggestion by moving the files and then restoring from GUI, it restored successfully just like the previous times but still no data. Why the data it's not coming ?!

    Burair

    giovedì 26 aprile 2012 04:38
  • RESTORE DATABASE [DatabaseName] FROM DISK = N'C:\DatabaseBackupFile.bak' WITH FILE = 1, MOVE N'DataFileName' TO N'C:\MSSQL_DATA\NewDataFileName.mdf', MOVE N'LogFileName' TO N'C:\MSSQL_DATA\NewLogFileName.ldf', NOUNLOAD, STATS = 10 GO

    Hi Iric;

    Thanks for your reply, i have tried the command and i got the below error message. I will try to search for  solution in the net, if u have any more suggestions, please let me know.

    Thank you.

    Msg 3159, Level 16, State 1, Line 1
    The tail of the log for the database "Test" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    giovedì 26 aprile 2012 05:21
  • Hi All,

    I tried this query from Pinal Dave website and it's working now;

    ALTER DATABASE AdventureWorks
    SET SINGLE_USER WITH
    ROLLBACK
    IMMEDIATE

    RESTORE DATABASE AdventureWorks
    FROM DISK = 'C\:BackupAdventureworks.bak'
    WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
    MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
    REPLACE

    Thank you all for trying to help me out. Really appreciate it.

    Burair

    giovedì 26 aprile 2012 05:37