Exporting schema and data from MSSQL 2012

I wanted to write a quick post about how I exported my local Funnelweb's database version, mainly so that I can Google/Bing myself later and remember what I did. I could have deployed to my host and do everything there, Funnelweb definitely supports it. However, since I am planning to contribute to this project, I arranged a local set up with Visual Studio, developed my theme and configured all the settings locally. When time came to deploy to my host, I didn't want to start from scratch again; I wanted to export my local database. This is how I did it from Microsoft SQL Server Management Studio 2012 Express.


Note: I am not a DBA. These are the steps that I figured out on my own. There is a huge possibility that this is not the best way of doing this. All warranties voided! It worked on my machine.!


Step 1

Go to MSSQL Studio and right click on the database in question. Select Tasks -> Generate Scripts

Step 2

Select the objects you are interesting in importing.

Step 3

Click on Next and on Advanced in the next screen

Step 4

Scroll down to "Types of data to script" option. Depending on what you want to do, choose among Schema only, Data only and Schema and Data options.

Step 5

Select the location to save the file, next, next and finish. At this point you should have a generated script that you can run in your host. In the advanced Scripting Option dialog there are many more options you can customize, like the target MSSQL server version, etc. In my case that was all I needed.

Next, I went a bit too fancy. I wanted to know whether I had missed any data in any table (I had initially selected two tables instead of exporting all; I added the rest a few minutes later). I got a lot of help from my friend Richie Rump who works as a data developer and knows a thing or two about databases. Together we came up with this script that queries the partition stats enumerating through the tables and tells you how many rows each table has. I ran this script locally and in my remote host. Obviously, I had the same amount of data in both.

USE <NameOfYourDB>
GO

SELECT   t.name,
         Total_Rows= SUM(st.row_count) 
FROM sys.dm_db_partition_stats st 
JOIN sys.tables                t
ON   st.object_id = t.object_id
WHERE   (index_id <2)
GROUP BY t.name
ORDER BY 2 DESC

Conclusion

I think the experience of moving the data from my local server to my host was very straight forward. If you have a better way of doing this, please share it in the comments.

Hope this helps somebody.