Copy SQL Express DB to another PC

Topic

New Reply

I have a SQL Express 2008 database on a virtual machine (Win7/Office 2010) of VMWare Workstation. I want to copy this database to another virtual machine (Win10/Office 2016). The VMs share network storage so there exists an intermediary location for files. How do I do this? It seems that SQL Express 2008 does not have a Copy feature. Can I back up and do something with those files? I am planning to use SQL Express 2012 on the destination PC, but I can use 2008 if necessary.

I have very little experience with SQL, so I appreciate any guidance.

Thanks,
Richard

Reply | Quote

Replies

Without knowing what the database is used for / by it’s not possible to give you much advice, beyond saying it may be possible.
SQL databases generally have set up requirements that you may be able to work around without knowing much about the database – you need to create users and add permissions which are not contained in the database, but in the SQL control database.
The other issue is SQL express may not have the tools required to export, import and check the database.

What is the database and where did you get it?
Do you have it backed up via a SQL backup? This is not a normal file backup.
Does it contain information that you need or can you start afresh?

cheers, Paul

Reply | Quote

I created the database to mimic a database used by a client. I have a similar database because I write macros in Word that reference the database. No one else uses my database, so there are no users or permissions. I have not done a SQL backup. It’s on a VMWare Workstation virtual machine, and I backup the VM regularly. There are only about 30 records, but I’d like to avoid recreating the tables and fields, and of course ensure that I get an exact replica of the DB on the VM with Win10/Office2016.
Does this give you a better understanding of what I want/need to do?

Cheers,
Richard

Reply | Quote

That’s now a yes regarding the backup. I just did it.

Reply | Quote

If you have a backup of all of the databases you can restore them to another SQL instance.

cheers, Paul

Reply | Quote

There is only one database, called CaseInfo. It backed up to CaseInfo.bak in the Backup folder. I can just move that file to my new PC where SQL is installed and run Restore. Does it have to be the same version of SQL Express? I can install SQL 2008 if it must be the same, or I can install a more recent release. Do you think it matters?

Many thanks for your help.
Richard

Reply | Quote

You should be able to move the DB between versions, but it’s not guaranteed.
This post seems to describe it well.
https://cybertext.wordpress.com/2008/02/05/restoring-an-sql-express-database-on-another-computer/

cheers, Paul

Reply | Quote

Thank you very much. This is a big help.

Richard

Reply | Quote

If you’ve installed the SQL Server Management Studio, you can detach the database and then copy/move it.

Reply | Quote

One of the standard ways of doing what you are doing is a backup & restore. As Paul says, this must be a SQL Server backup. When the database is restored to the target system, SQL Server converts anything that must be converted, to the new SQL Server format(s).

The rule you must know is, only upgrades are supported. You cannot downgrade using this mechanism. There are ways of downgrading but they can get complicated and Microsoft won’t support you if you get into trouble doing it.

Reply | Quote