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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply