Solved

Restore recent download of AdventureWorksDW2016CTP3.bak

Posted on 2016-10-31
43
59 Views
Last Modified: 2016-11-10
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
Comment
Question by:Karen Schaefer
  • 18
  • 14
  • 5
  • +2
43 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867345
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
 

Author Comment

by:Karen Schaefer
ID: 41867350
not sure how to create the instance.  Like I stated I am new to this.
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867354
Did you download and install MS SQL 2016 Express?
0
 

Author Comment

by:Karen Schaefer
ID: 41867358
not sure of version how do I check that
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867361
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
 

Author Comment

by:Karen Schaefer
ID: 41867370
Control panel does not indicate which version.
Capture.JPG
0
 

Author Comment

by:Karen Schaefer
ID: 41867374
in attempting to create a new instance, I getting the following message:

Capture.JPG
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867375
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867382
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
 

Author Comment

by:Karen Schaefer
ID: 41867388
sorry I am unable to find the instance I created in SSMS, where can I find out what the instance is called?
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867391
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
 

Author Comment

by:Karen Schaefer
ID: 41867395
I can find the server name

Capture.JPG
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867407
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
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41867412
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
0
 

Author Comment

by:Karen Schaefer
ID: 41867413
I do not see those files:

Capture.JPG
0
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41867418
Hi Karen ,

Please put  . in your server name. (symbolic dot)
0
 

Author Comment

by:Karen Schaefer
ID: 41867419
Tapan,

I do not have that file path.

Capture.JPG
0
 

Author Comment

by:Karen Schaefer
ID: 41867422
dustin,

see error message:

Capture.JPG
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867448
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
 

Author Comment

by:Karen Schaefer
ID: 41867452
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867461
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
 

Author Comment

by:Karen Schaefer
ID: 41867488
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41867524
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
 

Author Comment

by:Karen Schaefer
ID: 41867629
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41867774
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41868145
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41868405
@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
 

Author Comment

by:Karen Schaefer
ID: 41868780
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41868931
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
 

Author Comment

by:Karen Schaefer
ID: 41869269
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869271
Try changing the LogOnAs to a service account user, or another user and see if the service will start.
0
 

Author Comment

by:Karen Schaefer
ID: 41869284
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41869707
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
 

Author Comment

by:Karen Schaefer
ID: 41871117
Dustin,

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

Capture.JPGCapture1.JPG
0
 

Author Comment

by:Karen Schaefer
ID: 41871133
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
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
ID: 41871619
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41871712
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
 

Author Comment

by:Karen Schaefer
ID: 41881221
Solution not found.  Please award point for time spent.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41881737
No points for time spent only for right solutions,
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41881738
Are you going for a full server reinstall?
0
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 250 total points
ID: 41882255
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now