SQL Server Performance Forum – Threads Archive

how to backup just last week’s data?

Hi, We have a large SQL Server database in another site so we couldn’t directly copy tables or views to our SQL Server database. What I’m trying to do is to copy all user’s tables that only contain the last week’s data to a file. Then import all these data tables from this file to our SQL Server database. Is there a way to do this? Can someone show me how to do this? Thanks in advance.
Li

The only option I can think of is to use DTS (aka export/import in Enterprise Manager). Wizards in Enterprise Manager will allow you to do this using a graphical UI It does assume that you can determine the data to be exported, as SQL has no inherent way of working out the update/create date associated with the data Cheers
Twan

If there is no full backup between two weeks, differential backup can aslo be taken and applied on the remote site…. Else you’ll have to write custom procedures for exporting the data. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I do something similar using BCP. I have a stored procedure, let’s call it usp_export_query that accepts a server name password the query and the output file name, and runs a bcp using xp_cmdshell. I’m calling this stored procedure from another wrapping stored procedure that is reading from a table. In this table I hold all queries for each table with, in my case, last identity processed. The query string looks like this SELECT * db_name.db_owner.my_table WHERE id > %last_processed_id%. You can do the same using dates WHERE my_date > %last_processed_date%. In the wrapping stored procedure I replace these %last_processed_variable% with the required one than calls usp_export_query with the current query. You can pass the parameter (date or last_index_processed) to the wrapping stored procedure, or if it changes from one table to another, store it in the table. Now all you need to do is create a job that calls the wrapping stored procedure, and schedule it to your needs. This solutions allpied to new rows in tables. Modified and deleted rows will obviously require extra work. Let me know if you need further explanation.

Follow as suggested by Bambola to use BCP which is non-logged operation as the database size is huge in terms of your explanation. And its nothing wrong with DTS but it take bit of more time and transaction log to complete the process, hope schedule for transaction log backup is in place. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks all for your response.
I am new to SQL Server. In order to write a stored procedure like Banbola mentioned, do I need to learn Transact-SQL? What’s the BCP? Thanks again!

Review information from SQL BOOKS ONLINe about BCP and examples listed. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Li,
As Satya said you will find more information on BOL. Here is something to get you started. It’s a simple procedure that export a given query to a txt file.
CREATE Procedure QueryToTextFile
(
@ServerNamevarchar(50),
@LoginNamevarchar(20),
@Passwordvarchar(20),
@Queryvarchar(1000),
@FileNamevarchar(200),
@FileDelimiterchar(1)= ‘|’
)
As
set nocount on Declare @BcpCommandvarchar(1000),
@ErrorFilevarchar(250),
@OutputFilevarchar(250),
@DataFilevarchar(250),
@Resint — adding the date to the file name
SELECT @FileName = @FileName + ‘_’ + replace(replace(convert(varchar(20), getdate(), 120),’:’,”), ‘ ‘,’_’) SELECT @DataFile = @FileName + ‘.txt’ SELECT @BcpCommand = ‘bcp "’ + @Query + ‘" queryout "’
SELECT @BcpCommand = @BcpCommand + @DataFile + ‘" -U ‘ + @LoginName + ‘ -P ‘ + @Password + ‘ -c -t "’ + @FileDelimiter + ‘" -S ‘ + @ServerName EXEC @Res = master..xp_cmdshell @BcpCommand RETURN(@Res) — 0 ok. 1 Error. If I need to export all rows from table authors in database pubs that have state ‘CA’, my call to the procedure will look like this DECLARE @RC int
EXEC @RC = TEST.dbo.QueryToTextFile ‘server_name’,’login_name’,’password’,’select * from pubs.dbo.authors where state = ”CA”’,’c:authors’,’|’
SELECT @RC
Bambola.

]]>