Step by step guide to export SQL Server 2008 database (data + schema) to SQL file

In software/web development or in any other IT related sector, we are fully aware about the importance of backup. This importance gets more critical when it comes to the database world. There are many ways to take backup of your database. In this post i will show you how you can export your database (data+schema) to an sql file using SQL Server 2008 R2.

1. Login to SQL Server Management Studio and right click in your database to go to ‘Generate Scripts’ option.

step by step guide to export sql server 2008 database to sql file

step by step guide to export sql server 2008 database to sql file

2. Press ‘Next’ button on the dialogue screen as shown below.

sql server database schema and data exporting to sql file

sql server database schema and data exporting to sql file

3. Choose which tables/views/functions you want to backup or select all to take complete backup.

taking sql server 2008 backup in sql file

taking sql server 2008 backup in sql file

4. Here it comes the tricky part. By default SQL server will export only the database schema. You have to click on ‘Advance’ button to tell SQL server that you need Schema + Data as well.

exporting sql server 2008 data and schema to sql file

exporting sql server 2008 data and schema to sql file

on the same screen you can also chose to export data of each object to an individual sql file or all object to a single file.

how to export data and schema to and sql file

how to export data and schema to and sql file

5. That’s it after selecting ‘Schema and Data’ you will not get your complete database in the exported file. Keep this file safe to recover you data in case you need it.

Hope this small guide helped you to take your database backup:)

Muhammad Salman Junior Programmer Asked on November 5, 2015 in No Category.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.