backup sql table

8/16/2019 Backup SQL Table

http://slidepdf.com/reader/full/backup-sql-table 1/11

How to backup SQL table ?Posted by Mahesh Gupta on February 22, 2012 Leave a comment (0)Go to comments

Backup SL tab!e, have you ever tr"ed to backup a s"n#!e SL tab!e "ns"de a database$ Let%s

see &o' to backup SL tab!e SL ab!e Backup *estore

DOES SQL Server supports table level backups ?

Backup ypes are dependent on SL Server *ecovery Mode!+ very recovery mode! !ets you

back up 'ho!e or part"a! SL Server database or "nd"v"dua! -!es or -!e#roups o. the

database+ Table-level backup cannot be created, there is no such option. B/ there

"s a 'orkaround .or th"s

ak"n# backup o. SL Server tab!e poss"b!e "n SL Server+ here are var"ous a!ternat"ve 'ays

to backup a tab!e "n s! SL Server

1+ BC (B/L 3P4 P*3G*5M)

2+ !enerate Table Script "ith data

6+ #ake a cop$ o% table usin& SELECT INTO

7+ S'(E Table Data Directl$ in a )lat *le

8+ E+port Data usin& SSS to an$ destination

Let%s see ho' 'e can use these methods to take table backup in sl server

o make "t more c!ear, !et%s take e9amp!e, 'e 'ant to backup SL tab!e named:Person+ontact:, 'h"ch res"des "n SL Server 'dventureorks samp!e database, 'h"ch

has 1;;<2 records and tab!e s"=e "s /000 1B

http://www.sqlserver-training.com/mahesh-gupta/-

http://www.sqlserver-training.com/how-to-backup-sql-table/-#respond

http://www.sqlserver-training.com/how-to-backup-sql-table/-#comments

http://www.sqlserver-training.com/sql-server-recovery-model-affect-performance/-

http://www.sqlserver-training.com/sample-sql-server-database-for-2012-adventure-works-download/-

http://www.sqlserver-training.com/how-to-backup-sql-table/-#respond

http://www.sqlserver-training.com/how-to-backup-sql-table/-#comments

http://www.sqlserver-training.com/sql-server-recovery-model-affect-performance/-

http://www.sqlserver-training.com/sample-sql-server-database-for-2012-adventure-works-download/-

http://www.sqlserver-training.com/mahesh-gupta/-



#ethod 2 3 Backup sl table usin& BC 4B5L1 CO6 7O!7'#8

o backup a SL tab!e named :Person+ontact:, 'h"ch res"des "n SL Server

'dventureorks, 'e need to e9ecute .o!!o'"n# scr"pt, 'h"ch

-- SQL Table Backup

-- Developed by DBATAG, www.DBATAG.com

DECLARE @table ARC!AR"#$%&,

@'(le ARC!AR"$))&,

@cmd ARC!AR")#$&

SET @table * +Advetueok/.0e/o.Cotact+ -- Table 1ame w2(c2 you wat to backup

SET @'(le * +C345SSQL4Backup4+ 6 @table 6 +7+ 6 C81ERT"C!AR"%&, GETDATE"&, ##$& --

Replace C345SSQL4Backup4 to de/t(at(o d( w2ee you wat to place table data backup

6 +.dat+

SET @cmd * +bcp + 6 @table 6 + out + 6 @'(le 6 + - -T +

E9EC ma/te..:p7cmd/2ell @cmd



+csharpcode, +csharpcode pre > .ont?s"=e@ sma!!A co!or@ b!ackA .ont?.am"!y@ conso!as, :our"er

e':, cour"er, monospaceA back#round?co!or@ CDDDA E'h"te?space@ preAE +csharpcode pre

> mar#"n@ 0emA +csharpcode +rem > co!or@ C00H000A +csharpcode +k'rd > co!or@

C0000DA +csharpcode +str > co!or@ C00I0H0A +csharpcode +op > co!or@ C0000c0A

+csharpcode +preproc > co!or@ CccII66A +csharpcode +asp > back#round?co!or@ CDD00A

+csharpcode +htm! > co!or@ CH00000A +csharpcode +attr > co!or@ CD0000A +csharpcode +a!t> back#round?co!or@ C.7.7.7A '"dth@ 100JA mar#"n@ 0emA +csharpcode +!num > co!or@

CI0I0I0A

O5T5T

9ote ?

1+ 4ou must have bu!k "mport E e9port pr"v"!e#es



2+ Kn above Scr"pt -n denotes nat"ve SL data types, 'h"ch "s key dur"n# restore

6+ -T denotes that you are connect"n# to SL Server us"n# "ndo's

5uthent"cat"on, "n case you 'ant to connect us"n# SL Server 5uthent"cat"on

use -5usernameN -passordN

7+ h"s '"!! a!so te!!, you speed to data trans.er, "n my case th"s 'as 2127IH+0H

ro's per sec+

8+ 3nce th"s commands comp!etes, th"s '"!! create a -!e named

:'dventureorks.erson.Contact:;<2;<;;;: "s a spec"-ed dest"nat"on

.o!der

'lternativel$, you can run the BP v"a command prompt and type the .o!!o'"n# command

"n command prompt, both operat"on per.orms the same act"v"ty, but K !"ke the above

ment"oned method as that%s save type "n open"n# a command prompt and type+

bcp Advetueok/.0e/o.Cotact out

C345SSQL4Backup4Advetueok/.0e/o.Cotact7$;#$;$$$.dat - -T



> mar#"n@ 0emA +csharpcode +rem > co!or@ C00H000A +csharpcode +k'rd > co!or@

C0000DA +csharpcode +str > co!or@ C00I0H0A +csharpcode +op > co!or@ C0000c0A


+csharpcode +htm! > co!or@ CH00000A +csharpcode +attr > co!or@ CD0000A +csharpcode +a!t

> back#round?co!or@ C.7.7.7A '"dth@ 100JA mar#"n@ 0emA +csharpcode +!num > co!or@

CI0I0I0A



backup 'h"ch can not restored "s o. no use, !et%s per.orm a u"ck restore to ver".y that th"s

tab!e !eve! backup do 'orksO+

7estore SQL table backup usin& BC 4B5L1 CO6 7O!7'#8

he .o!!o'"n# scr"pt '"!! he!p you to per.orm a tab!e !eve! restore, 'h"ch 'e backed up "n

above steps

B<L= >1SERT Advetueok/.0e/o.Cotact/7Re/toe

?R85 +C345SSQL4Backup4Cotact.Dat+

>T! "DATA?>LET0E*+at(ve+&

Screen O5T5T = (eri*cation



> mar#"n@ 0emA +csharpcode +rem > co!or@ C00H000A +csharpcode +k'rd > co!or@C0000DA +csharpcode +str > co!or@ C00I0H0A +csharpcode +op > co!or@ C0000c0A


+csharpcode +htm! > co!or@ CH00000A +csharpcode +attr > co!or@ CD0000A +csharpcode +a!t

> back#round?co!or@ C.7.7.7A '"dth@ 100JA mar#"n@ 0emA +csharpcode +!num > co!or@

CI0I0I0A



#ethod ; 3 Backup sl table usin& !enerate Table Script "ithdata

o backup a SL tab!e named :Person+ontact:, 'h"ch res"des "n SL Server

'dventureorks, can #enerate a Scr"pt .or tab!e schema as 'e!! as data+

Let%s u"ck!y see, ho' 'e #enerate a Scr"pt .or SL tab!e "nc!ud"n# SL ata+



3nce you -n"sh that you '"!! ab!e reat"on Scr"pt '"th ata "nsert statements, 'h"ch "s

noth"n# Qust eua! to dump statement "n mys! databases+

Kn case o. restore "s reu"red, Qust e9ecute the Scr"pt 'h"ch 'e #enerated+

#ethod > 3 Backup sl table usin& SELECT INTO

SELECT INTO statement se!ects data .rom one tab!e and "nserts se!ected data "nto a

d"Derent tab!e+ h"s "s noth"n# Qust !"ke mak"n# a copy tab!e+ h"s '"!! make a copy o. a tab!e

"ns"de a database on!y+

K do persona!!y use th"s statement, pr"or to make chan#es to product"on database ". tab!e ".

o. .e' MB%s+ on%t use th"s .or !ar#e tab!es, th"s m"#ht -!! up ent"re space o. your database E

dr"ve+



he .o!!o'"n# Scr"pt '"!! create a tab!e name ontactsRopyR20120221, and copy a!! data

.rom tab!e ontact to th"s ne'!y created tab!e+

/elect (to Advetueok/.0e/o.Cotact/7Copy7$;#$;$$#

'om Advetueok/.0e/o.Cotact

#ethod 3 Backup sl table usin& S'(E Table Data Directl$ in a)lat *le

hen you e9ecute any Se!ect statement, SL Server by de.au!t sho's you resu!t "n resu!t

area, but 'e can chan#e that opt"on and set

'hen 'e e9ecute a statement, sent the output to a at -!e, "nstead o. sho'"n# that on SSMS

screen+

h"s "s ho' 'e Backup s! tab!e us"n# S5T ab!e ata "rect!y "n a F!at -!e

#ethod @ - Backup s! tab!e us"n# SSKS packa#e by e9port"n# data to anyest"nat"on

backup sql table

Documents