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

posted @ 2020-02-16 20:56  怕提神  阅读(128)  评论(0编辑  收藏  举报