How to migrate a database from on prem SQL Server to SQL Server in VM

Fred Luk 1

I have several databases on a SQL Server running on premises. How can I migrate one particular database from my SQL Server to SQL Server running on a VM ? I want to migrate the data as well as all the stored procedures.

My problem is that my SQL Server on VM uses Windows authentication with a different user name than the PC that I run my on-prem SQL Server management studio. So, I cannot use the COPY Database facility as describe in the following link.

https://www.c-sharpcorner.com/UploadFile/4b43b4/copy-database-from-one-server-to-another-server-in-sql/

Thanks in advance,
Fred

Cris Zhan-MSFT 6,626 Reputation points

2020-11-20T01:44:16.46+00:00

Hi anonymous userLuk-9638,
Is the version of the target SQL Server instance that you need to migrate lower than the source instance version? for example, you need migrate a SQL Server 2017 database to 2014.
Because you don’t seem to try to backup and restore.

5 answers

Dirk Hondong 871 Reputation points

2020-11-21T13:13:29.1+00:00

Hi anonymous userLuk-9638 ,

so, to recap: you have a local PC with SQL Server and you want to move one particular DB to a new SQL server which resides in a VM.
If you only want to move the database, then just do a backup of the database to your local file system.
Grab the database dump afterwards, copy it to your vm or a network share, which is accessible.
Then run a restore.

Much simpler than using the copy database wizard.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Fred Luk 1 Reputation point

2020-11-19T23:49:47.467+00:00

This is the screenshot that shows the error when I tried to connect to the remote SQL Server on VM.
Please sign in to rate this answer.
Cris Zhan-MSFT 6,626 Reputation points

2020-11-20T01:51:50.423+00:00

This is a common connection error,there may be many reasons for this.
But first you must ensure that the network connection between the two computers is feasible. Then, you need to make sure that certain SQL Server settings are properly enabled.

you can refer to this doc:
Troubleshoot connecting to the SQL Server Database Engine
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Chandresh Awasthi 1 Reputation point Microsoft Employee

2020-11-20T00:04:36.51+00:00

You can either create a SQL credential on your on-premise DB and login using that on your restored SQL DB on the VM, but, better option would be to have your windows ID federated with Azure AD and then using the AD credentials on the cloud version.
Please sign in to rate this answer.
Dirk Hondong 871 Reputation points

2020-11-21T13:10:31.797+00:00

If Fred has never done something before with azure then I'm not sure if that way is a bit too much.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Fred Luk 1 Reputation point

2020-11-21T07:11:49.763+00:00

Hi Chandresh, Thank you for your reply. Can you explain how to do the first approach ? I am not familiar with Azure AD so that would take more effort to set up. I log into the SQL Server on my VM with server name: cocetlvbraquyd4 and username: cocetlvbraquyd4\qb and no password.

Thanks in advance,
Fred
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Fred Luk 1 Reputation point

2020-11-22T04:56:26.763+00:00

Hi Dirk, Thanks for your reply. So will the store procedures be copied as well ? I guess I will give it a try.

Many thanks,
Fred
Please sign in to rate this answer.
Visakh 211 Reputation points

2020-11-22T05:41:59.81+00:00

Yes
When you take a backup of the database, all objects within it like Tables, Procedures, Triggers, UDFs etc will be included within it.
Only thing you need to be careful about is on the version of the SQLServer on your On-Prem system vs that on VM. The version on the VM has to be either same as or later version than that on your On-Prem system. You cant restore a backup taken from a later version of SQLServer instance onto an earlier one.

Ricardo Piekarski 6 Reputation points

2020-11-22T14:33:59.827+00:00

Hello anonymous userLuk-9638 ,

@Dirk Hondong and @Visakh are on point here. Backup and Restore should be the standard solution for what you are trying to achieve. As long as the destination is at the same or newer version as the source the restore should work. Every database object will be included within the database backup. Only SQL Server instance level objects will not be included.

For example, if the users in your database are currently mapped into SQL Server logins - therefore not a contained database - you will have to create new SQL Server logins to be mapped into users in your new database.

Additionally I would recommend you to compress the backup in order to save on your migration time window.

Cheers,
Ricardo
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to migrate a database from on prem SQL Server to SQL Server in VM

5 answers

Your answer