Sql-server – Moving Databases between Instances of different Versions of SQL Server

migrationsql serversql-server-2017

I found out SQL Server Developer Edition is now free.

I would like to use my older databases from SQL Server Express 2016 to a new instance of SQL Server Developer 2017.

I want all the data objects: indexes, triggers, etc. to be moved together with the data files.

I am trying to figure out the ways in which I can do this. I am pretty sure these would work between instances of the same version, but I am not sure if it would work if moving between different versions of SQL Server:

Backup Old and Restore to New
Script Old and run the script on New
Detach Old and reattach it on New

Are these 3 workable? Are there any other ways?

The instances are on the same machine, and I have not yet installed SQL Server developer edition.

Migration does not need to be immediate, but at most within a week or so.

Best Answer

The best way will be to use backup-restore - using start-dbamigration .. This will take care of migrating entire instance including all databases, logins, jobs, etc.

Make sure you use -BackupRestore switch with appropriate network location

e.g.

Start-DbaMigration -Source sql2016 -Destination sql2017 -BackupRestore -NetworkShare \nas\sql\migration

Related Solutions

Sql-server – SQL Server Migration restore backup vs copy data and log files

For speed (eg less downtime) backup/restore. Backup/restore is quicker because of Instant File Initialisation (only for MDF). The actual backup file is a lot smaller then the MDF/LDF sizes so copies quicker.

It's arguably slighty less risky because there is no downtime on the SQL 2000 instance.

However, detach removes access to the old database. You'll have to take this offline with a backup/restore. If there is a risk of end users connecting, use this (or take offline as soon as backup is complete)

Basically, it's whatever suits you best. I've used both methods for a recent server migration.

Sql-server – Difficulty in moving a SQL Server database

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer. You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything. It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to. If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it. Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation). By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'

Best Answer

Related Solutions

Sql-server – SQL Server Migration restore backup vs copy data and log files

Sql-server – Difficulty in moving a SQL Server database

Related Question