Directory lookup for the file failed with the operating system error 3 (The system cannot find the path specified.)

Question

I am trying to re-create a database (MyDB) from one SQL server (Source) to another one (Target). Source is located on my local machine and is SQL Server 2014. Target is located on a remote machine and it's SQL Server 2012. Here are the steps I've taken:

1. 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 entire database and all database objects".
   3. I click Next and on the next page, under Advanced, I select "Schema and data".
   4. That generates a SQL file (scripts.sql) that contains the definition for MyDB.
   5. Then I use the following osql command to re-create the database on Target:

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

6. After execution is finished 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, Server Target, Line 2 Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyDB.mdf" failed with the 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 listed could not be created. Check related errors. 1> 2> Msg 5011, Level 14, State 5, Server Target, Line 1 User does not have permission to alter database 'MyDB', the database does not exist, or the database is not in a state that allows access checks. 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 do have the file MyDB.mdf at the location it's complaining about on Source, but not on Target. There is no directory "MSSQL12.MSSQLSERVER" on Target. How can I fix this?

Answer 1

For those interested in the solution to this, the problem was that there was no "MSSQL12.MSSQLSERVER" directory on Target because it's on a different version of SQL Server, namely 2012. What I had to do was create the directory manually and it started working after that.