Solved MS SQL export / import
-
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?
-
@Mike-Davis Yes as long as the application did not add some weird security settings or anything.
-
@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.
-
The details of the application are really the important thing here. No one "uses" SQL server directly. it is pointless.
-
Moving the database is likely that easy. Do you not need to move the application as well?
-
@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.
-
@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
-
@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?
-
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.
-
Has to be Express as this is Windows 7 to Windows 10. No Server OS.
-
@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.
-
Risk should be low because the old system beed not be shut down or modified for the move.
-
@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
-
@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
-
@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.
-
@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.
-
@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.
-
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?
