SQL Server Express 2014 Restore Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Express 2014 Restore Backup

Author

Topic

goss
Starting Member

29 Posts

Posted - 2015-02-22 : 14:05:34

Hi all,

Using SQL Server Express 2014.
I downloaded AdventureWorks 2014.BAK from CodePlex

I extracted to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\Backup

I tried to run this query per instructions

USE [master]

RESTORE DATABASE AdventureWorksDW2014
FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\Backup\AdventureWorksDW2014.bak'
WITH MOVE 'AdventureWorksDW2014_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\DATA\AdventureWorksDW2014.mdf',
MOVE 'AdventureWorksDW2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\DATA\AdventureWorksDW2014.ldf',
REPLACE

But I receive this error

quote:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\Backup\AdventureWorksDW2014.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

It appears I have rights to all folders. Other thoughts as to what the issue is.

Kind regards,
-w

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-22 : 14:43:16

Did you download AdventureWorks2014 or AdventureWorksDW2014?

goss
Starting Member

29 Posts

Posted - 2015-02-22 : 15:09:00

Thanks gbritton,

I downloaded Adventure Works 2014 Full Database Backup.zip from
[url]http://msftdbprodsamples.codeplex.com/releases/view/125550[/url]

Kind regards,
-w

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-22 : 16:29:44

That zip file contains AdventureWorks.bak, but in the code you posted, you are trying to restore from AdventureWorksDW1014.bak -- a different file not present in the zip

goss
Starting Member

29 Posts

Posted - 2015-02-22 : 16:55:21

Thanks gbritton,

I updated the query to remove all ref's to "DW"

Revised:


USE [master]

RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\Backup\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLEXPRESS\MSSQL\DATA\AdventureWorks2014.ldf',
REPLACE

Now I receive this error:

quote:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot execute script.

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

Could not find a part of the path 'C:\Users\myname\AppData\Local\Temp\Temp1_Adventure Works 2014 OLTP Script.zip\instawdb.sql'. (mscorlib)

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

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

Kind regards,
-w

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-22 : 17:04:51

Looks like you`re not posting what you`re running! There`s no reference to a Temp directory anywhere in the code you posted.

goss
Starting Member

29 Posts

Posted - 2015-02-27 : 09:05:29

Thanks gbritton,

I deleted everything from the data folder that I could and I rebooted the PC. Everything went well and I see AdventureWorks2014.mdf in the Data directory. I tried to attach the database but I received an error. I tried closing Studio and rebooting, I still receive the same 'Access is denied' error.

I searched Google a bit and found that I must right-click on Studio and Run As Administrator even though my user account has fullcontrol on 'C:\Program Files\Microsoft SQL Server\'

Kind regards,
-w

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-27 : 12:13:51

quote:

I deleted everything from the data folder

If you deleted system database files, which typically do exist in the data folder, then your SQL Server is screwed at this point.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources