asked on

Moving a SQL database to another server and an upgraded version

I currently have an sql database 2008 r2 (management studio) I want to move that database to a new 2016 desktop experience standard server with sql 2014 management studio. I have no idea where to even start. I have a picture of all the databases and all the jobs that run. I'd like to migrate everything.
SQL.png

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WellingtonIS

ASKER

I was able to copy over the DB's but is there a way to copy over the Jobs in the server agent? In addition I was able to upgrade the database to sql14 on the original server, however, I can't upgrade the OS it's 2008..

Vitor Montalvão

You can transfer the SQL Agent jobs. They are stored in msdb database but you shouldn't simple replace this BD. You have two options:
1 - Script each job from the source server and run the script in the target server
2 - Use the SSIS Transfer jobs task.

WellingtonIS

ASKER

OK I'm missing something. I've backed up and transferred all the backup files to the new server. I've even copied them to the default location. when I try to restore it can't find the files and I can't select them. Could this be because I haven't changed the server name yet? See attached..
restore.png

Vitor Montalvão

You don't have a database yet, so you need to choose from a device where you'll be asked for the path where the backup files are.

WellingtonIS

ASKER

It took me a while but I finally figured it out. Thanks for this information. I wound up detaching the database and doing it that way. It all worked out in the end.