RESTORE SQL SERVER 2008

Question

Hello I am new in SQL SERVER 2008, and

how can I do RESTORE DATABASE using my file mdf?

 

 

Answer 1

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

Answer 2

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

Answer 3

thanks !!!!!!!!!!

---

rodrigobrs111

Answer 4

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

Answer 5

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?

Answer 6

The name of database is AdventureWorks2012_Data and

I didn't do deattaching.

I want only to do attach from my file mdf.

---

rodrigobrs111

Answer 7

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

Answer 8

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

Answer 9

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.