Copy that Floppy

So currently I am in the process of migration of data from one database to another. They are mostly the same so it should be just an export here and an import there...right?

Well let's go over step one of anytime you are going to be migrating anything. Practice locally. If you follow the steps below you can create a working copy of a MS SQL database on your local machine.

The first step is to install SQL server management studio. You can do a Google search for that yourself and get the version you need. In this demo we will be using 2008 R2, old, but that is what we use at work.

It is a basic windows install using a wizard. So if you can't figure this part out, you best stop now. We will be working in the command prompt shortly and I don't want to cause a panic attack.

Alright you installed? Good. The next step is to connect to the database that you need to copy. Find the database and right click on it. Then select the Tasks > Generate Scripts. Now depending on your access you have on the database you might can select all or pick what you need from the check boxes below. I personally select the bottom radio button for "Select specific database objects". From there select Table, Stored Procedures, User-Defined Functions, and users. Yes that is all of the database, yes there is a radio button for select all. For some reason I don't have access to use that button, go figure.

Now select next and you see save to file, save to clipboard, save to query window. Chose save to file, this will be a large file, few hundred MB to a GB depending on the number of rows you will be copying. I'll explain in another step why you must use the file. So make sure the file is named something that you can remember and find it in a minute. I named mine ScriptToCopy.sql

Now here comes the part that I miss every time, part of the reason why I am writing this post is so I don't forget. Click the Advanced button. Most of this will stay as is, but there are a few places we need to update. First is Append to File, make that true. Even though we said to on the last screen, we must do it here as well. Then scroll down to Table/View Options. The field directly above this will be Types of data to script, make sure to select Schema and Data. This way you get your tables and rows. Then below that, you can select your indexes and triggers if you need them. Select OK, then select next, next, and wait for your script to copy.

Alright now that you have your script, it is time to create your local database. Step one is connect to your local database, mine is SQLEXPRESS. You will need to create an empty database locally with the same name as the one that you just copied. So this one was named BlobPostDatabase.

Now here comes the fun part. Open up your command prompt, navigate to the directory where you script is located, and type in the following command.

sqlcmd -S localhost\SQLEXPRESS -i ScriptToCopy.sql

So as you can see, we call the sqlcmd command. -S is the server that your database will be on. So mine is localhost(or you can use your PC name, I suggest localhost)\SQLEXPRESS. -i Stands is the input file command. It tells sqlcmd to run this file.

Now why do I have to do this in the command prompt? Since this file is large, see above for file size, you will run out of memory trying to use the GUI. I Have a machine with 8GB physical and another 8 virtual, so trust me that you will run out. So there you go, you now have a local copy of your database.

Next up will be steps to prepare for a migration. Don't expect this post next week or the following, I am still figuring this out and will be getting help from our DBA on this process. Hopefully I will have some cool tricks to share.