August 14, 2022 at 8:37 pm
Hi
I'll start by stating the system I'm running:
SQL Server 2014 Developer SP3, in a Windows 7 Pro SP1 virtual machine in Virtualbox 6.1.36. I have also installed the Win 7 'SP2' rollup update pack.
I've got a certain amount of database experience, but I am trying to learn about SQL Server by way of Mike Hoteks book 'SQL Server 2008 Step By Step' and applying it to SQL Server 2014. I figure the basics would be the same or very similar.
I have successfully installed SQL 2014 and have set up separate service accounts for the various services. I am now trying to 'restore' the Adventureworks databases using the following SQL:
EXEC sp_configure 'filestream_access_level',2;
GO
RESTORE DATABASE AdventureWorks FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' WITH RECOVERY;
GO
RESTORE DATABASE AdventureWorksDW FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak' WITH RECOVERY;
GO
However, this is not working and I have deduced that this is probably a file/folder/account permissions issue that needs fixing in Windows. I get the following returned when I execute the query:
Query Completed With Errors:
Configuration option 'filestream access level' changed from 2 to 2. Run the RECONFIGURE statement to install.
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'AdventureWorks2014_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'AdventureWorks2014_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Msg 5133, Level 16, State 1, Line 7
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorksDW2014_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 7
File 'AdventureWorksDW2014_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorksDW2014_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 7
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorksDW2014_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 7
File 'AdventureWorksDW2014_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorksDW2014_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 7
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
I have given file permissions to the database engine account after looking in Services/SQL Server (MSSQLSERVER) to determine the account that is trying to access the file in the Logon tab. I have found this by looking online and there are suggestions that this should fix the problem, but I am still getting the error above and am now at a loss.
Does anyone have any ideas or know what I'm still missing? I would very much appreciate any assistance. Many thanks.
Neil
August 14, 2022 at 11:15 pm
I eventually managed to get this sorted with the SQL below:
RESTORE DATABASE AdventureWorks FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' WITH FILE = 1,
MOVE N'AdventureWorks2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Data.mdf',
MOVE N'AdventureWorks2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Log.ldf',
NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5
GO
RESTORE DATABASE AdventureWorksDW FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak' WITH FILE = 1,
MOVE N'AdventureWorksDW2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Data.mdf',
MOVE N'AdventureWorksDW2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Log.ldf',
NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5
GO
It was weird, the locations provided by the SQL below did not physically exist, but I found the appropriate folder and used the SQL above and it worked:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
GO
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak'
GO
This was all with the help at this link https://qawithexperts.com/article/sql/download-adventureworks-database-and-restore-in-sql-server-s/315
I hope this post helps someone else. Anyhow, if anyone still has any useful comments I would be very interested. Thanks.
September 8, 2022 at 8:25 am
I eventually managed to get this sorted with the SQL below:
RESTORE DATABASE AdventureWorks FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' WITH FILE = 1, MOVE N'AdventureWorks2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Data.mdf', MOVE N'AdventureWorks2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Log.ldf', NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5 GO
RESTORE DATABASE AdventureWorksDW FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak' WITH FILE = 1, MOVE N'AdventureWorksDW2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Data.mdf', MOVE N'AdventureWorksDW2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Log.ldf', NOUNLOAD, REPLACE, NOUNLOAD, STATS = 5 GO
It was weird, the locations provided by the SQL below did not physically exist, but I found the appropriate folder and used the SQL above and it worked:
RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' GO
RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak' GO
This was all with the help at this link https://qawithexperts.com/article/sql/download-adventureworks-database-and-restore-in-sql-server-s/315
I hope this post helps someone else. Anyhow, if anyone still has any useful comments I would be very interested. Thanks.
Also Thanks for the Post Link Man.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy