Do I need the MDF and LDF files when I run a script to generate my schema? [closed]

Question

I'm migrating a database from SQL Server 2019 to SQL Server 2017. To do so, I am using the "Generate scripts" feature in the source database to generate the schema and data separately. (I will likely need to break the data import into multiple files to avoid memory errors.)

When I try to run the schema script in the destination server, the script fails with this response:

Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "E:\SqlData\my-db-test.mdf" failed with the operating system error 21(The device is not ready.).

None of the instructions I've seen mention any need to copy this .mdf file from the source server to the destination, but it appears the destination server expects the file to be present. The beginning of the script looks like this:

USE [master]
GO
/****** Object:  Database [my-db-test]    Script Date: 2/9/2024 8:14:49 AM ******/
CREATE DATABASE [my-db-test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'my-db-dev8b', FILENAME = N'E:\SqlData\my-db-test.mdf' , SIZE = 794624KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'my-db-dev8b_log', FILENAME = N'E:\SqlData\my-db-test_log.ldf' , SIZE = 5971968KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [my-db-test] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

Do I need to copy the .mdf and .ldf files from the source server to the destination in order to run this script? Is it possible to alter the script so I don't need the .ldf file?

I'm not sure why the script references my-db-dev8b, which is an old name for the database. I don't know if these old names in the script will complicate matters.

Are the .mdf and .ldf files necessary for migrating the database from SQL Server 2019 to SQL Server 2017? Can I help my cause by manually editing the script in some way?

Answer 1

It looks like I just needed E:\SqlData to be present on the new server. Once I created the necessary directory, I was able to run the script and the .mdf and .ldf files were generated automatically.

In my case, there is no E drive on the destination server, so I created the SqlData directory in my D drive and edited the script to reference D:\SqlData:

USE [master]
GO
/****** Object:  Database [my-db-test]    Script Date: 2/9/2024 8:14:49 AM ******/
CREATE DATABASE [my-db-test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'my-db-dev8b', FILENAME = N'D:\SqlData\my-db-test.mdf' , SIZE = 794624KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'my-db-dev8b_log', FILENAME = N'D:\SqlData\my-db-test_log.ldf' , SIZE = 5971968KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Answer 2

Create database will create the files referenced, but the folder structure must exist & be writeable.

You can check the locations through server properties (or the GUI)

select SERVERPROPERTY('InstanceDefaultDataPath') as 'Data Default'
select SERVERPROPERTY('InstanceDefaultLogPath')as 'Log Default' 
select SERVERPROPERTY('InstanceDefaultBackupPath')as 'Backup Default' 

See https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver16

For future dbs through you can change the default location (if you have permission) either through the server properties (Database Settings) or by writing the registry:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\Data'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\Log'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, 'F:\Backup'

This will require a restart to activate the new setting.

Note that it just affects the default used in the GUI. Create database scripts will still require a fully qualified path.