Easy way to copy database from MsSQL Express to MsSQL-Server?

2 views
Skip to first unread message

Tim Ritberg

unread,
Apr 18, 2021, 7:01:19 AMApr 18
to
Hello!

A friend has a software, which uses a MsSQL Express Database. Now we
want to move this DB to his Server which runs SQL-Server.
How to do that?

Tim

Erland Sommarskog

unread,
Apr 18, 2021, 5:15:45 PMApr 18
to
What does "SELECT @@version" report for the two instances?

Tim Ritberg

unread,
Apr 19, 2021, 4:20:29 PMApr 19
to
Am 18.04.21 um 23:15 schrieb Erland Sommarskog:
Can't login today. The server instance is a Linux MsSQL 15.
On Desktop is a (Express) SQL Server 2016 SP1.

Tim

Erland Sommarskog

unread,
Apr 19, 2021, 4:53:58 PMApr 19
to
Tim Ritberg (t...@server.invalid) writes:
> Can't login today. The server instance is a Linux MsSQL 15.
> On Desktop is a (Express) SQL Server 2016 SP1.
>

So the server is of a later version that the the Express instance. That's
good, because then you can use BACKUP/RESTORE.

BACKUP DATABASE db TO DISK = 'C:\temp\backup.bak' WITH INIT

And then on the server:

RESTORE DATABASE db FROM DISK = '/path/backup.bak'
WITH MOVE 'db' TO '<path>',
MOVE 'db_log' TO '<path>'

When it comes to the names that follow MOVE, these are the logical names of
the devices. They often follow the pattern shown here, but this is not
always the case. Run sp_helpdb on the database on the source server to
see the names. They are in the first column of the second result set.


Tim Ritberg

unread,
Apr 19, 2021, 5:25:14 PMApr 19
to
Am 19.04.21 um 22:53 schrieb Erland Sommarskog:
And what about users/permissions etc?

Tim

Erland Sommarskog

unread,
Apr 20, 2021, 2:44:39 PMApr 20
to
Tim Ritberg (t...@server.invalid) writes:
> And what about users/permissions etc?
>

Quoting Books Online:

BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator fixed
database roles.

If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database.

Tim Ritberg

unread,
Apr 20, 2021, 4:56:45 PMApr 20
to
Am 20.04.21 um 20:44 schrieb Erland Sommarskog:
I guess, I would use admin account.

Tim

Erland Sommarskog

unread,
Apr 21, 2021, 2:25:52 PMApr 21
to
Tim Ritberg (t...@server.invalid) writes:
> I guess, I would use admin account.
>

Yeah, that is what I would expect too. And in that case, you don't need
to worry about permissions. In SQL Server at least. Possibly there can
be issues in the file system.

Tim Ritberg

unread,
Apr 21, 2021, 3:23:34 PMApr 21
to
Am 21.04.21 um 20:25 schrieb Erland Sommarskog:
But this application should not use an admin account, so I have to
transfer user from express to server.

Tim

Erland Sommarskog

unread,
Apr 22, 2021, 3:20:09 PMApr 22
to
Tim Ritberg (t...@server.invalid) writes:
> But this application should not use an admin account, so I have to
> transfer user from express to server.
>

Yeah, application shoiuld not run under admin accounts. But my
assumption was that you would do the BACKUP/RESTORE operation from SSMS,
outside the application.
Reply all
Reply to author
Forward
0 new messages