1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-SQLSERV

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

    From Tim Ritberg@21:1/5 to All on Sun Apr 18 13:01:13 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Sun Apr 18 23:15:43 2021
    Tim Ritberg (tim@server.invalid) writes:
    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?


    What does "SELECT @@version" report for the two instances?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Mon Apr 19 22:20:26 2021
    Am 18.04.21 um 23:15 schrieb Erland Sommarskog:
    Tim Ritberg (tim@server.invalid) writes:
    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?


    What does "SELECT @@version" report for the two instances?


    Can't login today. The server instance is a Linux MsSQL 15.
    On Desktop is a (Express) SQL Server 2016 SP1.

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Mon Apr 19 22:53:57 2021
    Tim Ritberg (tim@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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Mon Apr 19 23:25:10 2021
    Am 19.04.21 um 22:53 schrieb Erland Sommarskog:
    Tim Ritberg (tim@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.


    And what about users/permissions etc?

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Tue Apr 20 20:44:36 2021
    Tim Ritberg (tim@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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Tue Apr 20 22:56:41 2021
    Am 20.04.21 um 20:44 schrieb Erland Sommarskog:
    Tim Ritberg (tim@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.


    I guess, I would use admin account.

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Wed Apr 21 20:25:49 2021
    Tim Ritberg (tim@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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Tim Ritberg@21:1/5 to All on Wed Apr 21 21:23:30 2021
    Am 21.04.21 um 20:25 schrieb Erland Sommarskog:
    Tim Ritberg (tim@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.


    But this application should not use an admin account, so I have to
    transfer user from express to server.

    Tim

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Tim Ritberg on Thu Apr 22 21:20:06 2021
    Tim Ritberg (tim@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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)