How To Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014

It is easy to Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014

Before you start make sure that you have administrative accounts on both servers.
This is 2 step process.
I would recommend reading this post for user export
Also read this document from Microsoft knowledge base.
1. we are going to export database using SQL Scripts from our old MsSQL 2000 server and then use that SQL Script file to create new version of Same database on our New MsSQL2014 server.

Ok on SQL 2000 server right click database you want to migrate then click on Generate SQL Script… as shown in figure 1
figure1

Figure 1

Then click on Show All button as shown in Figure 2
mssqlexport_figure2

Figure 2

Now choose objects to create scripts for:
mssqlexport_figure3

Figure 3

Next we decide on Formatting as shown in Figure 4
mssqlexport_figure4

Figure 4

Finally we need to setup some Security and Table Scripting Options and as well one or multiple files, I prefer to use single file per database as shown in Figure 5.
mssqlexport_figure5

Figure 5

Click Ok and wait for server to do its thing.
Now To the 2014 server…

Open Microsoft SQL Server Management Studio and connect to your 2014 server
click on open file or Ctrl + O (letter o not zero)
find your Exported SQL Script file from 2000 server.
Check your CREATE DATABASE directive and make sure that FILENAME is pointing to the place where you want new database to be stored.
2. Once all this is done we can do data Import using SQL Server Import and Export Wizard as explained in this post.

Leave a Reply

Your email address will not be published. Required fields are marked *