ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Solved MS SQL export / import

    IT Discussion
    7
    30
    2.3k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • P
      pchiodo
      last edited by

      Is this full blown SQL Server? or is this SQL Serve Express? If the later of the two, you should be able to port without issue depending on the software using the DB. What is the use case for SQL? This would help in making the determination.

      You could potentially just make a backup of the DB, and restore it to a test install of 2016 and see what happens.

      1 Reply Last reply Reply Quote 0
      • M
        Minion Queen Banned
        last edited by

        Has to be Express as this is Windows 7 to Windows 10. No Server OS.

        J P D 3 Replies Last reply Reply Quote 0
        • M
          Mike Davis
          last edited by

          @pchiodo said in MS QL export / import:

          Is this full blown SQL Server? or is this SQL Serve Express?

          Fairly certain it's express, but I'll double check before getting started.

          T 1 Reply Last reply Reply Quote 0
          • M
            Minion Queen Banned
            last edited by

            Risk should be low because the old system beed not be shut down or modified for the move.

            1 Reply Last reply Reply Quote 1
            • J
              JaredBusch @Minion Queen
              last edited by

              @Minion-Queen said in MS SQL export / import:

              Has to be Express as this is Windows 7 to Windows 10. No Server OS.

              No it does not. SQL Server 2012 will install just fine on Windows 7. So does 2014. Never installed 2016 yet, but I assume it would too.

              Now there is little point in doing so, but it CAN be done.

              I have it for dev/testing purposes

              1 Reply Last reply Reply Quote 0
              • P
                pchiodo @Minion Queen
                last edited by

                @Minion-Queen AKA @scottalanmiller

                @Minion-Queen said in MS SQL export / import:

                Has to be Express as this is Windows 7 to Windows 10. No Server OS.

                I think this changed with SQL Server 2012. Fairly certain it could be installed on Windows 7:

                https://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx

                J 1 Reply Last reply Reply Quote 0
                • J
                  JaredBusch @pchiodo
                  last edited by

                  @pchiodo said in MS SQL export / import:

                  @Minion-Queen AKA @scottalanmiller

                  @Minion-Queen said in MS SQL export / import:

                  Has to be Express as this is Windows 7 to Windows 10. No Server OS.

                  I think this changed with SQL Server 2012. Fairly certain it could be installed on Windows 7:

                  https://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx

                  SQL Server 2005 and 2008 and 2008 R2 all installed on desktop OS also.

                  1 Reply Last reply Reply Quote 2
                  • T
                    thwr @Mike Davis
                    last edited by thwr

                    @JaredBusch said in MS SQL export / import:

                    @pchiodo said in MS SQL export / import:

                    @Minion-Queen AKA @scottalanmiller

                    @Minion-Queen said in MS SQL export / import:

                    Has to be Express as this is Windows 7 to Windows 10. No Server OS.

                    I think this changed with SQL Server 2012. Fairly certain it could be installed on Windows 7:

                    https://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx

                    SQL Server 2005 and 2008 and 2008 R2 all installed on desktop OS also.

                    Any SQL Server Express (2005 and up) can be installed on any NT based OS as far as I remember, as long as minimum requirements are met.

                    @Mike-Davis said in MS SQL export / import:

                    @pchiodo said in MS QL export / import:

                    Is this full blown SQL Server? or is this SQL Serve Express?

                    Fairly certain it's express, but I'll double check before getting started.

                    Express or not doesn't matter at all: Express is a cost-free version meant for very small and predictable workloads, primary use case is development or very small scale applications. Restrictions in Express apply to the # of CPU cores (locked down to a single core), RAM (1GB per instance) and database size (10GB in newer versions). Oh, and there's no SQL Server service agent installed.

                    If you are using a full blown SQL server, be sure you got the license and the CAL. But this would be a total waste of money in next to all cases anyway.

                    1 Reply Last reply Reply Quote 0
                    • D
                      Dashrender @Minion Queen
                      last edited by

                      @Minion-Queen said in MS SQL export / import:

                      Has to be Express as this is Windows 7 to Windows 10. No Server OS.

                      Why does it HAVE to be? sure it's likely, but nothing stops you from install SQL server on Win 7 or Win10.

                      1 Reply Last reply Reply Quote 0
                      • M
                        Mike Davis
                        last edited by

                        It is Express. Couldn't do a simple export to the new server. It wouldn't connect. New machine is not domain joined and couldn't connect with Windows creds. My thought was to backup the database on the old box, copy the backup file to the new box and then restore it. It seemed to need a database target to restore to, so I tried to create one. That failed and I noticed that the connection type was Windows auth, and the old database was using SQL Server mode with the SA account.

                        Couldn't switch the new database to SA authentication.
                        0_1474312520403_database-SA.png

                        I could be going about this totally wrong.

                        T 1 Reply Last reply Reply Quote 0
                        • T
                          thwr @Mike Davis
                          last edited by

                          @Mike-Davis said in MS SQL export / import:

                          It is Express. Couldn't do a simple export to the new server. It wouldn't connect. New machine is not domain joined and couldn't connect with Windows creds. My thought was to backup the database on the old box, copy the backup file to the new box and then restore it. It seemed to need a database target to restore to, so I tried to create one. That failed and I noticed that the connection type was Windows auth, and the old database was using SQL Server mode with the SA account.

                          Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                          M 1 Reply Last reply Reply Quote 2
                          • M
                            Mike Davis @thwr
                            last edited by Mike Davis

                            @thwr said in MS SQL export / import:

                            Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                            OK, that's what I was trying to do. It looks like in SQL 2016 you have to select "Enable server proxy account" in order to enable SQL server mode auth. Does that account have to get created somewhere else before you can use it, or by putting in what you want to use are you creating it there?

                            T J 2 Replies Last reply Reply Quote 0
                            • T
                              thwr @Mike Davis
                              last edited by

                              @Mike-Davis said in MS SQL export / import:

                              @thwr said in MS SQL export / import:

                              Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                              OK, that's what I was trying to do. It looks like in SQL 2016 you have to select "Enable server proxy account" in order to enable SQL server mode auth. Does that account have to get created somewhere else before you can use it, or by putting in what you want to use are you creating it there?

                              Uhm, wait. Are you installing that new version on the machine where the old version is installed? This may work, but you should not have multiple versions of SQL server installed on the same machine. You will most probably face problems identifying the instances etc.

                              I've never installed 2016 myself yet, but Microsoft provides decent instructions. The proxy account (https://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx) seems to be related to the Agent, which should not be available on Express.

                              J 1 Reply Last reply Reply Quote 0
                              • J
                                JaredBusch @thwr
                                last edited by

                                @thwr said in MS SQL export / import:

                                @Mike-Davis said in MS SQL export / import:

                                @thwr said in MS SQL export / import:

                                Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                                OK, that's what I was trying to do. It looks like in SQL 2016 you have to select "Enable server proxy account" in order to enable SQL server mode auth. Does that account have to get created somewhere else before you can use it, or by putting in what you want to use are you creating it there?

                                Uhm, wait. Are you installing that new version on the machine where the old version is installed? This may work, but you should not have multiple versions of SQL server installed on the same machine. You will most probably face problems identifying the instances etc.

                                Mixing SQL instances on the same machine works fine. Each version gets a specific instance name. and only one can be the default instance that you access without specifying the instance name. Definitely and advanced configuration though. Not for a novice.

                                T 1 Reply Last reply Reply Quote 0
                                • J
                                  JaredBusch @Mike Davis
                                  last edited by

                                  @Mike-Davis said in MS SQL export / import:

                                  @thwr said in MS SQL export / import:

                                  Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                                  OK, that's what I was trying to do. It looks like in SQL 2016 you have to select "Enable server proxy account" in order to enable SQL server mode auth. Does that account have to get created somewhere else before you can use it, or by putting in what you want to use are you creating it there?

                                  Cannot help you here. I've not install 2016 yet. Too many other things to do.

                                  1 Reply Last reply Reply Quote 0
                                  • M
                                    Mike Davis
                                    last edited by

                                    @thwr said in MS SQL export / import:

                                    Are you installing that new version on the machine where the old version is installed?

                                    no, the 2016 version is on a new Windows 10 computer.

                                    1 Reply Last reply Reply Quote 0
                                    • M
                                      Mike Davis
                                      last edited by

                                      @thwr said in MS SQL export / import:

                                      I've never installed 2016 myself yet, but Microsoft provides decent instructions. The proxy account (https://technet.microsoft.com/en-us/library/ms190698(v=sql.105).aspx) seems to be related to the Agent, which should not be available on Express.

                                      I think this is the part I missed:
                                      Note that you must create a credential first before you create a proxy if one is not already available. For more information about creating a credential, see How to: Create a Credential (SQL Server Management Studio) or CREATE CREDENTIAL (Transact-SQL).

                                      1 Reply Last reply Reply Quote 0
                                      • M
                                        Mike Davis
                                        last edited by

                                        So the sa account is there, but disabled. When I , it errors out with a permissions error. 0_1474318476706_sql-error.png

                                        T 1 Reply Last reply Reply Quote 0
                                        • dafyreD
                                          dafyre
                                          last edited by

                                          Check the SQL Server's authentication mode and see that it is set to Mixed?

                                          1 Reply Last reply Reply Quote 0
                                          • T
                                            thwr @JaredBusch
                                            last edited by

                                            @JaredBusch said in MS SQL export / import:

                                            @thwr said in MS SQL export / import:

                                            @Mike-Davis said in MS SQL export / import:

                                            @thwr said in MS SQL export / import:

                                            Just create a normal backup (Task -> Backup). Setup the new SQL Server Express, create a new database and restore the backup. Job done. Make sure you install the new SQL Server with mixed mode authentication if you want to use non-Windows users to connect to the DB

                                            OK, that's what I was trying to do. It looks like in SQL 2016 you have to select "Enable server proxy account" in order to enable SQL server mode auth. Does that account have to get created somewhere else before you can use it, or by putting in what you want to use are you creating it there?

                                            Uhm, wait. Are you installing that new version on the machine where the old version is installed? This may work, but you should not have multiple versions of SQL server installed on the same machine. You will most probably face problems identifying the instances etc.

                                            Mixing SQL instances on the same machine works fine. Each version gets a specific instance name. and only one can be the default instance that you access without specifying the instance name. Definitely and advanced configuration though. Not for a novice.

                                            That's why I said it's better not to mix them (or run multiple instances) 😉

                                            1 Reply Last reply Reply Quote 0
                                            • 1
                                            • 2
                                            • 1 / 2
                                            • First post
                                              Last post