• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Restore recent download of AdventureWorksDW2016CTP3.bak

I am new to SQL Server 2016 and I am attempting to load/create a local connection to the above mentioned database.  I am unable to access this file.  How do I restore the .bak file and also How do I create a local connection to the restored file.

Thanks,
0
Karen Schaefer
Asked:
Karen Schaefer
  • 18
  • 14
  • 5
  • +2
3 Solutions
 
Dustin SaundersDirector of OperationsCommented:
You can find instructions here on Microsoft's website (https://msdn.microsoft.com/en-us/library/ms177429.aspx)

Connect to your instance with SQL Server Management Studio (SSMS) and right click on your instance, and choose 'Restore Database'.  Browse to your .bak and you can execute the command to pull it in as a DB in your instance.
0
 
Karen SchaeferAuthor Commented:
not sure how to create the instance.  Like I stated I am new to this.
0
 
Dustin SaundersDirector of OperationsCommented:
Did you download and install MS SQL 2016 Express?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Karen SchaeferAuthor Commented:
not sure of version how do I check that
0
 
Dustin SaundersDirector of OperationsCommented:
You can check the SQL version by going to the Control Panel and looking at what you have installed for SQL.

In case not, here's what you need to do to get started.

Download and install Microsoft SQL Server 2016 Express Edition.  This will install SQL into an Instance, which you will configure.  By default, the instance will simply be the name of the computer.  This Microsoft Guide walks through the process.  You're probably going to want to install SQL in mixed mode, so be sure to choose that option.

Next you need SQL Server Management Studio 2016 (SSMS).  Download and install this.

Once installed, open SSMS and enter in the name of the instance you installed (if default, just use the name of the computer).  From there you can follow the restore procedure outlined above.
0
 
Karen SchaeferAuthor Commented:
Control panel does not indicate which version.
Capture.JPG
0
 
Karen SchaeferAuthor Commented:
in attempting to create a new instance, I getting the following message:

Capture.JPG
0
 
Dustin SaundersDirector of OperationsCommented:
I see, so 2016 is the version-- Express is the edition.  The edition will be Express, or a paid one like Standard, etc.  When you connect to the instance in SSMS, all you need to do is run a New Query and use:

SELECT @@version

Open in new window


But if you already have it installed, all you need to do is connect with SSMS (or install it if you haven't yet) then restore the DB.
0
 
Dustin SaundersDirector of OperationsCommented:
Since you've already got an instance installed, you don't need to create a new one (unless you wanted to for some reason).  Once you get Management Studio installed, you should be able to connect to that instance you already have and update it.
0
 
Karen SchaeferAuthor Commented:
sorry I am unable to find the instance I created in SSMS, where can I find out what the instance is called?
0
 
Dustin SaundersDirector of OperationsCommented:
On your PC, click Start>Run and type in 'Services.msc'

This opens your Services snap in, scroll down to 'SQL Server' and you should see the instance name in parenthesis.  MSSQLSERVER denotes the default instance name, so you would just connect to the name of the computer your installation is on.  If it were anything else, you would use COMPUTERNAME\INSTANCENAME.  (like DUSTIN-PC\INSTANCE2)
ss_instanceName.png
0
 
Karen SchaeferAuthor Commented:
I can find the server name

Capture.JPG
0
 
Dustin SaundersDirector of OperationsCommented:
It will be the name of the computer you installed it on, which in this case looks like DESKTOP-QCU4U0A .   Alternatively, if you are on the computer that it is installed to, you can just type localhost
0
 
Tapan PattanaikSenior EngineerCommented:
Hi Karen Schaefer,

Please keep your "AdventureWorks2016CTP3.bak" file in the below path.

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup

For more details Please find the attachment.
Steps-to-Restore-Database.docx
1
 
Karen SchaeferAuthor Commented:
I do not see those files:

Capture.JPG
0
 
Tapan PattanaikSenior EngineerCommented:
Hi Karen ,

Please put  . in your server name. (symbolic dot)
0
 
Karen SchaeferAuthor Commented:
Tapan,

I do not have that file path.

Capture.JPG
0
 
Karen SchaeferAuthor Commented:
dustin,

see error message:

Capture.JPG
0
 
Dustin SaundersDirector of OperationsCommented:
Hmm it looks like you don't have an instance installed then.

Why don't you start fresh, uninstall SQL 2016 from your computer and then reboot, and restart the installation from scratch.  Just follow the defaults, but be sure to select 'Database Engine' in the installation phase (I'm guessing that got missed).  Leave everything else at defaults except for when you have the option to install in 'Mixed Mode Authentication'.
0
 
Karen SchaeferAuthor Commented:
Dustin,

Thanks I was hoping to avoid that, however, I will reinstall the program.

By the way I like your picture, I have a thing for Raccoons.

I let you know if the reinstall solves the issue.

K
0
 
Dustin SaundersDirector of OperationsCommented:
Ha, thanks!  Raccoons are my favorite animal, really fun to watch them in videos.

I think the uninstall/reinstall method will work the best for this situation, checking the box for 'Database Engine' during the reinstall should do the trick.   You shouldn't need to reinstall SQL Server Management Studio, just the 2016 Sql Server install.

Let us know how it goes and we'll get through the restoration.
0
 
Karen SchaeferAuthor Commented:
ok now having issue with the uninstalled I am getting following error msg:  When I am attempting to remove Sql Server 2015 using the control panel

TITLE: Remove SQL Server 2016
------------------------------

There are validation errors on this page. Click OK to close this dialog box. Review errors at the bottom of the setup page, then provide valid parameters or click Help for more information.

------------------------------
BUTTONS:

OK
------------------------------
Capture.JPG
0
 
Dustin SaundersDirector of OperationsCommented:
Hmm...  I'm not sure what originally got installed.  

When you were running the installation earlier and getting the error message, was that from the download link provided?
0
 
Karen SchaeferAuthor Commented:
I have tried to load both the Express and Developers versions without success.  I am getting the following error on both instances:

Capture.JPG
what else must I do to have a successful installation?

K
0
 
Pawan KumarDatabase ExpertCommented:
I think you should start a fresh.

Uninstall the SQL SERVER using control Panel as it look likes it is corrupted or not properly installed.

Install the new Version..

Put the bak file @ C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\Backup

Restore the same.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You shouldn't remove the SQL Server. It's installed and you just don't know how to connect to it.
Never use Services.msc to work with SQL Server related services. You should always use SQL Server Configuration Manager to perform those kind of tasks.
Btw, isn't localhost but (localhost). The parenthesis are important here. Alternatively you can just type a dot (.) - with no parenthesis.
But it you have a SQL Express edition then the default instance name is Servername\SQLExpress.
0
 
Dustin SaundersDirector of OperationsCommented:
@Vitor-

To find the name of the SQL service simply for connecting, why would one not use Services to find the name?  It does no harm.

I think what you mean is if you want to connect to the database locally use (local).  This is different than localhost which will not work with parenthesis, but will work on it's own.

@Karen-

My most recent install of SQL 2016 Express defaulted to an instance which does not require /SQLExpress.  But it doesn't look like any database engine components were originally installed.
0
 
Karen SchaeferAuthor Commented:
Thanks sorry I am confused by the last three comments.  What should I do, I have uninstall and attempt to reinstall both Express and Developer a couple of times.  Each time I get the above mentioned error message about the Setup.exe file.  I look online for solutions without much luck Why can't if get this to load correctly?

K
0
 
Dustin SaundersDirector of OperationsCommented:
Are you a full administrator on the local machine?  If not, it could shed some light as to why the install isn't going properly.

To me, it looks like something went wrong with your initial install and no Database Engine services got installed, but it's possible that the installation only 'half finished' leaving some manual clean up to be done.

If you can't run an install, a repair, or an uninstall, cleaning it up manually is a pretty involved step but might have to be done.
0
 
Karen SchaeferAuthor Commented:
ok I reinstalled the developers version and I am attempting to setup the SSMS and not having much luck.

What am I missing.  I have been trying to get this loaded and running on my local machine (I am the only user) for the pass two weeks.  I am just trying to get this done so I can due some tutorials on SSIS,SSRS and SSAS.  I am very frustrated.  PLEASE HELP,  I am going crazy.

Capture.JPG
0
 
Dustin SaundersDirector of OperationsCommented:
Try changing the LogOnAs to a service account user, or another user and see if the service will start.
0
 
Karen SchaeferAuthor Commented:
When I use the configuration Manager attempting to establish the connection I am getting the following error message:
TITLE: Connect to Server
------------------------------

Cannot connect to DESKTOP-QCU5U0A\KFSCHAEFERSQL16.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------

The system cannot find the file specified

------------------------------
BUTTONS:

OK
------------------------------


Capture.JPG
As you can see I do have the instance setup
Capture.JPG
What am I missing?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Both of your SQL Server instances are stopped. You can see that easily by your Configuration Manager print screen.
You need to start the SQL Server instances and then use SSMS to connect to the instances.
For the default instance you only need to provide the server name (DESKTOP-QCU5U0A).
For the named instance provide the following in the server name field: DESKTOP-QCU5U0A\KFSCHAEFERSQL16.
0
 
Karen SchaeferAuthor Commented:
Dustin,

I did as you suggestion, however, I am still getting failed request error

Capture.JPGCapture1.JPG
0
 
Karen SchaeferAuthor Commented:
could this be an issue with admin rights, if so I do I verify what admin rights are being used.  Since I am the only user, should this be an issue?

Karen

Here is the error log.

I unstalled everything including modify regedit and downloaded  Sql Express 2016.  Then I attempted to reinstall and still got the Setup issue error.  I attempted to reinstall using the Setup.exe in the media file and now I get the following issue.  Could this be an issue between 32 & 64 bit setups?

 ERRORLOG
Capture.JPG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You made so many modifications in that server (install/uninstall/install/...) that at this point my suggestion is to recreate the server from the scratch.
0
 
Karen SchaeferAuthor Commented:
Solution not found.  Please award point for time spent.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No points for time spent only for right solutions,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you going for a full server reinstall?
0
 
Dustin SaundersDirector of OperationsCommented:
I agree with Vitor, no points needed.

@vitor it looks like this topic is actually continued here https://www.experts-exchange.com/questions/28982119/Could-not-find-the-Database-Engine-startup-handle-in-SQL-Server-2016-or-2014-Installation-for-Win10.html

So you can request an unresolved closure.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 14
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now