none
RESTORE SQL SERVER 2008

    Question

  • Hello I am new in SQL SERVER 2008, and

    how can I do RESTORE DATABASE using my file mdf?

     

     
    Tuesday, July 17, 2012 2:40 AM

Answers

  • Hello Irisul yes I got it, thanks!!!!

    This database is AdventureWorks2008R2, It's a database model for study that's right.

    After this I couldn't access the tables althought existing the tables in database.

     I have this error now: "Msg 208, Level 16, State 1, Line 1 Invalid object name 'ProductInventory."

    But I can see the tables with this comand: SELECT * FROM sys.tables

    What's your opinion about it Irisul?

     


    rodrigobrs111

    • Marked as answer by rodrigobrs1 Tuesday, August 14, 2012 7:17 PM
    Monday, July 23, 2012 11:28 PM

All replies

  • You use restore from a backup, the .mdf file is not a backup but is the actual database data file. Instead for database data files (.mdf) and database log files (.ldf) you use the database attach method to attach them to SQL Server (note that you can potentially have secondary database data files (.ndf) if they exist). Anyway to attach the .mdf file as a database in SQL Server follow the steps outlined in the article below, you can do this via SQL Server Management Studio...

    http://msdn.microsoft.com/en-us/library/ms190209(v=sql.100).aspx

    (Note: to elaborate a database restore is achieved from a database backup file(s), so once you have attached the database above via the .mdf/ldf files you can then take a database backup from with SQL Server Management Studio which usually has the file extension .bak, it is from this .bak database backup that you can then perform a database restore).

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem


    Tuesday, July 17, 2012 3:12 AM
  • If you need to restore the mdf only without ldf run this query:

    USE [master]
    GO
    EXEC sp_attach_single_file_db @dbname='TestDb',
    @physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
    GO


    Tuesday, July 17, 2012 5:23 AM
  • thanks !!!!!!!!!!

    rodrigobrs111

    Tuesday, July 17, 2012 4:22 PM
  • Hello I tried it, but  I had this error:

    Msg 5172, Level 16, State 15, Line 1
    The header for file 'C:\Microsoft SQL Server 2008\Data\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

    My script:

    USE MASTER 
    GO 


    EXEC  sp_attach_single_file_db 

          @dbname   = 'AdventureWorks2012_Data' ,
          @physname = 'C:\Microsoft SQL Server 2008\Data\AdventureWorks2012_Data.mdf' 


    GO

          

    rodrigobrs111

    Thursday, July 19, 2012 1:39 PM
  • What is the database name? Is it AdventureWorks2012_Data or just AdventureWorks2012?

    Check for possible solution in this thread: http://social.msdn.microsoft.com/Forums/da/sqldisasterrecovery/thread/edfd77ba-a23b-49e5-99f6-be69296861fd

    Was your database marked as "Suspect" before dettaching it?
    • Edited by irusul Thursday, July 19, 2012 2:20 PM
    Thursday, July 19, 2012 2:19 PM
  • The name of database is AdventureWorks2012_Data and

    I didn't do deattaching.

    I want only to do attach from my file mdf.


    rodrigobrs111

    Saturday, July 21, 2012 4:57 PM
  • Could you also try this method?

    CREATE DATABASE TestDb ON
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH_REBUILD_LOG
    GO
    • Edited by irusul Monday, July 23, 2012 5:55 AM
    Monday, July 23, 2012 5:55 AM
  • Hello Irisul yes I got it, thanks!!!!

    This database is AdventureWorks2008R2, It's a database model for study that's right.

    After this I couldn't access the tables althought existing the tables in database.

     I have this error now: "Msg 208, Level 16, State 1, Line 1 Invalid object name 'ProductInventory."

    But I can see the tables with this comand: SELECT * FROM sys.tables

    What's your opinion about it Irisul?

     


    rodrigobrs111

    • Marked as answer by rodrigobrs1 Tuesday, August 14, 2012 7:17 PM
    Monday, July 23, 2012 11:28 PM
  • You may also restore sql data due to sql database recovery tool damaged .mdf files after using unstable software, hard drive fails, system errors. Utility restores tables, procedures and etc. items.
    Monday, September 24, 2012 3:15 PM