Tuesday, July 17, 2012 2:40 AM
Hello I am new in SQL SERVER 2008, and
how can I do RESTORE DATABASE using my file mdf?
Tuesday, July 17, 2012 3:12 AM
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...
(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).
/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 5:23 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 4:22 PMthanks !!!!!!!!!!
Thursday, July 19, 2012 1:39 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.
@dbname = 'AdventureWorks2012_Data' ,
@physname = 'C:\Microsoft SQL Server 2008\Data\AdventureWorks2012_Data.mdf'
Thursday, July 19, 2012 2:19 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-be69296861fdWas your database marked as "Suspect" before dettaching it?
- Edited by irusulMicrosoft Community Contributor Thursday, July 19, 2012 2:20 PM
Saturday, July 21, 2012 4:57 PM
The name of database is AdventureWorks2012_Data and
I didn't do deattaching.
I want only to do attach from my file mdf.
Monday, July 23, 2012 5:55 AM
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 irusulMicrosoft Community Contributor Monday, July 23, 2012 5:55 AM
Monday, July 23, 2012 11:28 PM
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?
- Marked As Answer by rodrigobrs1 Tuesday, August 14, 2012 7:17 PM
Monday, September 24, 2012 3:15 PMYou 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.