0

How to create a .bak file for selected database table in SQL Server 2008?

I have a TESTDB database which have more than 15 tables. I want to make .bak file only for 5 selected tables.

So how can I create that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AJ027
  • 51
  • 2
  • 5
  • 1
    You can't do that - SQL Server backups are **database-level** backups. – marc_s Apr 05 '13 at 13:16
  • @marc_s are you sure ? after the follow of Praveen steps 2 files (LDF and MDF) are generated. – AJ027 Apr 05 '13 at 13:47
  • SQL Server has at least one data file (`.mdf` - could be multiple) and one transaction log file (`.ldf`) - but that's **not** a backup and that can't be limited to a group of tables - it's everything or nothing – marc_s Apr 05 '13 at 13:48
  • In theory you could move your 5 tables to a different filegroup, take a filegroup-level backup, and do a partial restore of the primary + that filegroup. But it would require Enterprise Edition on the restore side I believe and seems a lot more convoluted than what you're looking for. You could also look at [BCP out/in](http://msdn.microsoft.com/en-us/library/ms162802(v=sql.100).aspx). – Aaron Bertrand Apr 05 '13 at 13:55

1 Answers1

4

By making .bak file literally means taking a backup of the Database and Restoring it back. You don't have to do that here in your case.

DTS will solve it for you. Means you need to use the Export Wizard. Just follow the below steps.

Step 1: Right Click on the Database
Step 2: Select Task
Step 3: Select Export Data
Step 4: Enter Data Source details
Step 5: Enter Destination details
Step 6: Select the tables you want to recreate.

Thats it.

Praveen Nambiar
  • 4,852
  • 1
  • 22
  • 31