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
Comments