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:
osql -S target -d master -E -i scripts.sql -o 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?
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.