Back up Sql Server with .mdf and .ldf ,Error [Resolved]

Posted by Raja_89 under Sql Server on 10/3/2013 | Points: 10 | Views : 1432 | Status : [Member] | Replies : 8
Hai

Recover a Database SQL Database 2005

How can i take a restore a Sql Database file having .mdf and .ldf
Steps
1.Choose task->Restore ->Database-> a window came for location

I have choose .mdf only
and
I have choose .mdf and .ldf

It shows an error

" An Exception occurred while executing a Transact-SQL statements batch

Additional Information
SQL Server cannot process this media family "



Could i restore the database or else

Regards




Responses

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Steps to be followed to restore a DB
1) Create a database
2) Right click on the DB name and choose "Attach" option using Object Explorer
3) Select the path where you have .mdf and .ldf files so that you can restore a DB into new database

References:
http://technet.microsoft.com/en-us/library/ms190209.aspx
http://stackoverflow.com/questions/3128578/restore-problem-sql-server-version
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c62dfa9c-dda3-44a3-95ba-1577f700fdfc/backup-restore-to-different-server-media-family-is-incorrectly-formed

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
As per Error Message I can say that
You cannot restore a backup from a higher SQL Server version on a lower version - this has never been able in SQL Server.

If you have a SQL Server 2008 backup, you can only restore that on a SQL Server 2008 (or later) machine - but not on a 2005 box. No trick, no workaround, no way to do it.



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Run the below script to let you know whether the back up file is usable or corrupted?
RESTORE VERIFYONLY
FROM DISK='F:\BackUpFilePath.bak' -- Your Back up file path
GO

IF @@ERROR = 0
BEGIN
PRINT ‘Backup usable'
END
ELSE
BEGIN
PRINT ‘Backup not usable'
END
GO


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 10/3/2013 [Member] Starter | Points: 25

Up
0
Down
Hai Bandi
That file was created in SQL SERVER 2005

Since i am having .mdf and .ldf i have tried with this


while running that code it shows an error message

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'E:\database\signature.ldf'. Operating system error

2(The system cannot find the file specified.).

Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '‘'.




How can i create a database backup to get .mdf and .ldf


What is the best way to get back up the database wheather .mdf/.ldf or .bak

with explanation




Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 10/3/2013 [Member] Starter | Points: 25

Up
0
Down
Hai
After creating a database i am unable to see the options [to go 3 options ]

1) Create a database
2) Right click on the DB name and choose "Attach" option using Object Explorer
3) Select the path where you have .mdf and .ldf files so that you can restore a DB into new database




Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Raja,

Second step should be "Select Attach.. option by right-click on Databases under Object Explorer "

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer links for restore database from .mdf & .ldf files... Screenshots available in those links
http://www.c-sharpcorner.com/uploadfile/raj1979/how-to-attach-a-database-in-sql-server-2005/
http://www.sqlservercentral.com/Forums/Topic362955-357-1.aspx
"How to Attach in a Perfect World:"
http://learningsqlserver.wordpress.com/2011/02/13/how-can-i-open-mdf-and-ldf-files-in-sql-server-attach-tutorial-troublshooting/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Have you solved the above issue? If yes, click on "Mark as Answer " link

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response