Get tables and their data without backing up the database: SQL Server 2008 R2

There may be situations when you want to get all of the tables in a SQL Server database and the data they currently hold, but you can’t (because of permissions assigned to your account, for example) or don’t want to backup the whole database. Well, follow the following steps to achieve this:

1- Open SSMS and connect to the correct instance of SQL Server;

2- Right-click on the appropriate database entry under Databases folder, go to Tasks, and then select “Generate Scripts…” . Generate and Publish Scripts wizard launches;

3- Click next in Introduction page;

4- In the next page, Choose Objects, pick out “Select specific database objects” and then check the checkbox beside Tables entry. You can even select the tables for which you want to get the scripts specifically by clicking on the + sign beside Tables entry. Click Next;

5- Here’s the tricky part: you can get either just the tables’ schema only or schema together with the data for the tables selected in the previous step. To do this, click on the Advanced button. Then in the Advanced  Scripting Options page find “Type of data to script” property and change it to “Schema and data”. (It is set to “Schema only” by default) Click Ok to go back to Set Scripting Options page;

6– Select “Save to new query window” and hit Next 2 times. When all the actions listed are completed click Finish.

After completing these steps, you’ll get a new query window opened in SSMS which has the script to create all the tables and insert the records they already contain in the original database.

Advertisements

About Saeed Barghi

Big Data/DW/ETL Consultant based in Melbourne, Australia View all posts by Saeed Barghi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: