Generate Script in SQL Server 2008 to Export Database

By: Arkadeep De Jul 31, 2014

Introduction:

Here I will explain how to generate script in sql server 2008 to export database or Export database in sql server with script.

Description:

In previous articles I explained SQL Server Get total rows count in union query, SQL Server Difference between Union and Union All, SQL Server Replace multiple spaces in string with single space, SQL Server Remove html tags from string and many articles relating to SQL server. Now I will explain to generate script in sql server 2008 to export database.

Generally after publishing the website we need to upload database with all its data into the online server for that we can create new tables in online database or we can upload the ".mdf" and ".ldf" files of database in server. If you want get your database “.mdf” and “.ldf” files you need to go to below path

My Computer à Program Files à Microsoft SQL Server à MSSQL10.MSSQLSERVER à MSSQL à DATA

Within this DATA folder you will get all the ".mdf" and ".ldf" files. Upload the files from here. Before access these files you have to stop the SQL Server Services. And for this go to Task manager -> Process. Click on the "Show process from all user" then go to Services and select the MSSQLSERVER, right click on that and stop the service like as shown below

Now you can access those ".mdf" files.

Method2:

Another second and better option you have is to generate script of your database. For this open your SQL Server Management Studio and select your Database. Now right click on the database and select "Task" and then "Generate Script".

Here my database name is Arkatest. After selecting the option "Generate Script" a dialog box will be open with welcome message. After click on the "Next" button you will be displayed all of your databases present in your local server. Choose yours and click on the "Next" button.

Now a new window will be opened with lots of properties. Here you have to change "Script Data" into "True" to generate the script with data present in the database otherwise it will generate only schema of the database.

Now check all the options and proceed to next window. In the next windows check all the options and proceed to the next one. At the "Output Option" Select which option you preferred. And after this click on the "Finish" button. Wait for few minutes and your script is ready. After a couple of minutes you will get a complete message for server. This script is full of create and insert query of your tables, store procedures and data.

Now your database is captured into a script file. Open your online database and go to the custom query option. Paste the generated script and run. Your tables, store procedures, functions and all others with data will be updated into your online database.

Your online database is ready to use now. Enjoy... :)