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.
Couldn't switch the new database to SA authentication.
I could be going about this totally wrong.
-
@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
-
@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?
-
@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.
-
@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.
-
@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.
-
@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.
-
@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). -
So the sa account is there, but disabled. When I , it errors out with a permissions error.
-
Check the SQL Server's authentication mode and see that it is set to Mixed?
-
@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)
-
@Mike-Davis said in MS SQL export / import:
So the sa account is there, but disabled. When I , it errors out with a permissions error.
Sorry, like others said, check the authentication mode again. Every prior version supports the mixed mode, and I never heard about the proxy account before. Guess its new in 2016.
-
I resinstalled and added the sa account this time. (I don't know if the user installed it the first time.) I did a named instance, so when I logged in I had to choose .\sqlexpress as the server. Once I did that I was able to just right click databases -> restore and select the files I had copied to the back up folder from the old server. Then it worked like it should.
