SQL Server Restore Syntax

SpaceCoastLife
SpaceCoastLife used Ask the Experts™
on
What is the syntax to restore a SQL Server .mdf file from the Command Prompt?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
If you have the .mdf file you would be attaching the database rather than a restore, a restore operation requires a full backup usually with a .bak file extension.
sqlcmd -S Server\Instance

USE [master]

GO

CREATE DATABASE [database_name] ON 

   ( FILENAME = N'filepath\databasefilename.mdf' )

FOR ATTACH ;

GO

Open in new window

Commented:
note: just in case  sqlcmd -S <yourSql server name>
you can run Select @@servername  to get it  
also you may need to use all path to SQLCMD.exe file location  if you have multiple sql server versions installed on the same box.

there is an another MSFT sample for the same  
Attaching a Local SQL Server Database Using sqlcmd
https://blogs.msdn.microsoft.com/bethmassi/2008/12/18/attaching-a-local-sql-server-database-using-sqlcmd/
Solution Manager
Commented:
Open command prompt and type below query to connect to instance
C:\> SQLCMD -S localhost\STELLARSQL2014 -U sa -P sql@2014”

Open in new window

Now, switch to MASTER database
1>use master
2>GO

Open in new window

Run the following query to restore the database
1> restore database Adventureworks2014 from disk=’e:\bharat\adventureworks2014.bak’ with stats=10
2> GO

Open in new window

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
Thanks for the help
Bharat BhushanSolution Manager

Commented:
Most welcome..

Keep reading!!

Regards,
Bharat

Commented:
SpaceCoastLify: you asked "What is the syntax to restore a SQL Server .mdf'  not from  BAK.  BAK nad MDF is not the same thing. Right?  
it means Paul Jackson and my answers are the "answers" and restore from BAK is an another question. Make sense?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial