Technical Article

BCP OUT all table data in your Database


Data Recovery is vital. This script will cursor through your database and BCP OUT the table data to a directory specifically for each day of the week (in numbers - where 1 = Sunday and 7 = Saturday) in order to have table recoverability up to 7 days. The first step is to create the 7 day directories somewhere on your network. The script will do the rest.

CREATE  procedure DBATools_bcp_backup__tables as 

/*************************************************//*  Purpose: Creates the BCP files for the PRSL  *//*  schema  and places them in their respective  *//*  day parts.  It will also overwrite the table *//*  data each week. Need to create 7 directories */ 
/*  called day1, day2,day3, day4, day5, day6 and *//*  day7                                         *//*************************************************/

declare @table_name varchar(255),
@statement varchar(512),
@dw tinyint

select @dw = datepart(dw,getdate())

declare tables cursor for 
select name from sysobjects where type = 'u' order by name

open tables
fetch tables into @table_name
while @@fetch_status = 0

--need to replace [DBName]
--need to replace FILESPEC with UNC Path to 
--the days directories

set @statement = 'bcp DBName..' + @table_name + 
        ' out ' + 'FILESPEC\day' + 
        cast(@dw as varchar(1)) + '\' + @table_name 
        + ' -T -c' 
print @statement
exec master..xp_cmdshell @statement , no_output
fetch next from tables into @table_name
print @@fetch_status
close tables
deallocate tables