How to backup on MSSQL by table level

MSSQL is good database. Unlike as Oracle, it seems that can not backup sqlserver databasee tables one by one.

However there is always way.

Thanks to

I did modify and then , it can auto baclkup table one by one no matter how many table is.

Here is script:

-- SQL Table Backup

-- Developed by DBATAG, www.DBATAG.com

DECLARE @table VARCHAR(128),

@file VARCHAR(255),

@cmd VARCHAR(512)

declare @m int

declare @n int

--declare @tmp nvarchar(MAX)

declare @tableList TABLE (id int IDENTITY(1,1) NOT NULL,contents VARCHAR(128) NOT NULL)

DECLARE @SQL2 VARCHAR(MAX)

SET NOCOUNT ON

set @SQL2='select DB_NAME()+''.'' +schema_name() + ''.''+ name from sys.tables where type=''U'''

INSERT INTO @tableList (contents) exec (@SQL2)

set @m=(select count(*) from @tableList)

set @n=1

while @n<=@m

begin

set @table=(select contents from @tableList where id=@n)

--SET @table = 'AdventureWorks.Person.Contact' -- Table Name which you want to backup

--SET @table = 'MES.dbo.Sys_Group'

--SET @table = 'select name from sys.tables where type='U' order by 1'

SET @file = 'C:\backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) -- Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup

+ '.bcp'

SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '

EXEC master..xp_cmdshell @cmd

set @n=@n+1

end

Bingo