How to backup SQL table ?

Posted by Mahesh Gupta on February 22, 2012 Leave a comment (0)Go to comments

Backup SQL table, have you ever tried to backup a single SQL table inside a database? Letssee How to backup SQL table | SQL Table Backup RestoreDOES SQL Server supports table level backups ?Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets youback up whole or partial SQL Server database or individual files or filegroups of thedatabase. Table-level backup cannot be created, there is no such option. BUT thereis a workaround for thisTaking backup of SQL Server table possible in SQL Server. There are various alternative waysto backup a table in sql SQL Server1. BCP (BULK COPY PROGRAM)2. Generate Table Script with data3. Make a copy of table using SELECT INTO4. SAVE Table Data Directly in a Flat file5. Export Data using SSIS to any destinationLets see how we can use these methods to take table backup in sql serverTo make it more clear, lets take example, we want to backup SQL table named"Person.Contact", which resides in SQL Server AdventureWorks sample database, whichhas 19972 records and table size is 6888 KB

Note 1. You must have bulk import / export privileges

2. In above Script -n denotes native SQL data types, which is key during restore3. -T denotes that you are connecting to SQL Server using WindowsAuthentication, in case you want to connect using SQL Server Authenticationuse -U<username> -P<passord>4. This will also tell, you speed to data transfer, in my case this was 212468.08rows per sec.5. Once this commands completes, this will create a file named"AdventureWorks.Person.Contact_20120222" is a specified destinationfolderAlternatively, you can run the BCP via command prompt and type the following commandin command prompt, both operation performs the same activity, but I like the abovementioned method as thats save type in opening a command prompt and type.bcp AdventureWorks.Person.Contact outC:\MSSQL\Backup\AdventureWorks.Person.Contact_20120222.dat -n -T

backup which can not restored is of no use, lets perform a quick restore to verify that thistable level backup do works.Restore SQL table backup using

BCP (BULK COPY PROGRAM)

The following script will help you to perform a table level restore, which we backed up inabove stepsBULK INSERT AdventureWorks.Person.Contacts_RestoreFROM 'C:\MSSQL\Backup\Contact.Dat'WITH (DATAFILETYPE='native');

Method 2 Backup sql table using Generate Table Script with

dataTo backup a SQL table named "Person.Contact", which resides in SQL ServerAdventureWorks, can generate a Script for table schema as well as data.Lets quickly see, how we generate a Script for SQL table including SQL Data.

Once you finish that you will Table Creation Script with Data insert statements, which isnothing just equal to dump statement in mysql databases.In case of restore is required, just execute the Script which we generated.

Method 3 Backup sql table using SELECT INTO

SELECT INTO statement selects data from one table and inserts selected data into adifferent table. This is nothing just like making a copy table. This will make a copy of a tableinside a database only.I do personally use this statement, prior to make changes to production database if table ifof few MBs. Dont use this for large tables, this might fill up entire space of your database /drive.

The following Script will create a table name Contacts_Copy_20120221, and copy all datafrom table Contact to this newly created table.select * into AdventureWorks.Person.Contacts_Copy_20120221from AdventureWorks.Person.Contact

Method 4 Backup sql table using SAVE Table Data Directly in a

Flat file

When you execute any Select statement, SQL Server by default shows you result in resultarea, but we can change that option and setwhen we execute a statement, sent the output to a flat file, instead of showing that on SSMSscreen.This is how we Backup sql table using SAVE Table Data Directly in a Flat file