The directory search for the file failed with an operating system error 3 (the system cannot find the path specified).

Question

I am trying to recreate a database (MyDB) from one SQL server ( Source ) to another ( Target ). The source is located on my local computer and is SQL Server 2014. Target is located on the remote computer and SQL Server 2012 on it. Here are the steps I took:

• On my local machine, I go to SQL Server Management Studio, I right-click on MyDB and go to Tasks -> Generate Scripts.
  2. There I select "Script the entire database and all database objects."
  3. I click "Next" and on the next page in the "Advanced" section, I select "Schema and Data."
  4. This creates an SQL file (scripts.sql) that contains the definition for MyDB.
  5. Then I use the following osql command to recreate the database in Target :

osql -S target -d master -E -i scripts.sql -o output.log

6. After completing this error, I get this error in the log file "output.log":

1> 2> 1> 2> 3> 4> 5> 6> 7> 8> Msg 5133, Level 16, State 1, Target Server, Line 2 Search the directory for the file "C: \ Program Files \ Microsoft SQL Server \ MSSQL12.MSSQLSERVER \ MSSQL \ DATA \ MyDB.mdf "failed with operating system error 3 (the system cannot find the path specified.). Msg 1802, Level 16, State 1, Server Target Line 2 CREATE DATABASE failed. Some file names cannot be created. Check related errors. 1> 2> Msg 5011, level 14, state 5, Target server, Line 1 The user does not have the right to modify the database "MyDB", the database does not exist or the database is not in a state that allows access checking. Msg 5069, Level 16, State 1, Server Target, Line 1 ALTER DATABASE statement failed.

Here are the first few lines of "scripts.sql":

USE [master] GO /****** Object: Database [MyDB] Script Date: 4/12/2016 4:30:20 PM ******/ CREATE DATABASE [MyDB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'MyDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyDB.mdf' , SIZE = 513024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 262144KB ) LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf' , SIZE = 1317504KB , MAXSIZE = 2048GB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT ON 

I have a file MyDB.mdf in the place where it complains about the Source , but not the Target . Target does not have the directory "MSSQL12.MSSQLSERVER". How can i fix this?

Answer 1

For those interested in resolving this issue, the problem was that Target did not have the "MSSQL12.MSSQLSERVER" directory, because it was in a different version of SQL Server, namely in 2012. To do this, you had to create the directory manually, and it started working after that.