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.
    • M
      Mike Davis
      last edited by Minion Queen

      I have a client with a MSSQL 2012 database on Windows 7 that they want to move to MSSQL 2016 on Windows 10. (It's used by one user.) Should this be as straight forward as exporting up the database using the SQL Server Management Studio and then importing it?

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

        @Mike-Davis Yes as long as the application did not add some weird security settings or anything.

        D 1 Reply Last reply Reply Quote 2
        • D
          dafyre @JaredBusch
          last edited by

          @JaredBusch said in MS QL export / import:

          @Mike-Davis Yes as long as the application did not add some weird security settings or anything.

          Double, triple, and Quadruple check this with the software vendor. My last job has been bit by this twice now.

          1 Reply Last reply Reply Quote 2
          • J
            JaredBusch
            last edited by

            The details of the application are really the important thing here. No one "uses" SQL server directly. it is pointless.

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

              Moving the database is likely that easy. Do you not need to move the application as well?

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

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

                Moving the database is likely that easy. Do you not need to move the application as well?

                How dare you tech.

                No teching allowed for you.

                M 1 Reply Last reply Reply Quote 3
                • M
                  Minion Queen Banned @JaredBusch
                  last edited by

                  @JaredBusch said in MS SQL export / import:

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

                  Moving the database is likely that easy. Do you not need to move the application as well?

                  How dare you tech.

                  No teching allowed for you.

                  Lol umm that was @scottalanmiller posting from my phone 😛

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

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

                    @JaredBusch said in MS SQL export / import:

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

                    Moving the database is likely that easy. Do you not need to move the application as well?

                    How dare you tech.

                    No teching allowed for you.

                    Lol umm that was @scottalanmiller posting from my phone 😛

                    How dare you let "that guy" use your phone. Doesn't he have enough methods for posting?

                    1 Reply Last reply Reply Quote 2
                    • 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
                                            • 1
                                            • 2
                                            • 1 / 2
                                            • First post
                                              Last post