Data Warehouse Backup Ideas

Data Warehouse data volume is generally very large, we need to back up every day? This point I still do not understand, just feel that the data warehouse at the very least from the production library flow of data does not need to do a complete backup, but the backup is still needed, such as our ETL process is as follows

1: Environment understanding

Environment: SQLSERVER2008R2

The Data Warehouse extraction process is as follows

Image analysis:

Production Library →ods: Using SSIS, this step we only save the SSIS package, because ODS is the raw data that maximizes the consistency of the business library

ODS→DW: Using a stored procedure to iterate through the view, in principle how many views of the ODS layer, how many tables the DW layer has

Use [Szch_ods_his]go/** * * * object:storedprocedure [dbo].    [Odstodw] Script date:08/13/2015 16:26:59 * * * * **/SET ansi_nulls ongoset quoted_identifier ongocreate PROCEDURE [dbo]. [Odstodw] With EXEC as Callerasdeclare viewsfrom cursor for  SelectT.name fromsysobjects TwhereXtype='V'; open viewsfrom;declare @viewname nvarchar ( $);d eclare @sqlstr nvarchar ( +); while@ @FETCH_STATUS =0beginSet@sqlstr ='drop table [dw_his].dbo.'+@viewname; Print'Delete a table'+@viewname exec (@sqlstr); BeginTry  Set@sqlstr ='select * into [dw_his].dbo.'+ @viewname +'From [Ods_his]. [dbo]. ['[Email protected]+']'; Print'Draw a table'+@viewnameexec (@sqlstr); Print'Finish Processing'+@viewnameendTrybeginCatchINSERT INTO ETLCONFIGURATION.DBO.ODSTODWERRMSGSelect@viewname, Error_message (), GETDATE () EndCatchFETCH Next fromviewsfrom into @viewname; Endclose viewsfrom;deallocate Viewsfrom; GO

Dw→target: As with the ODS→DW approach

2: Design Plan

According to our DW extraction scheme, I feel that after backing up the SSIS program, the ODS layer data is basically guaranteed OK, the big deal to re-draw a full amount, it may be time-consuming, of course, this place can be optimized. The simplest of our

The method is to back up the View creation script and procedure creation script for the ODS and DW layers.

2.1: Related Scripts

--------------------------------------------------------------------Related Technology------------------------------------------------- --------------------------------

Get the Create script for all views and storage in the target database (SQL Server)

Select  from syscomments cm inner join sysobjects o on o.id=where xtype ='P' or  xtype ='v'order by O.xtype,o.name,cm.text

The effect is as follows

Also attached:

--get views and stored procedure creation statements
Select O.xtype,o.name,cm.text from syscomments cm
Inner JOIN sysobjects o on o.id=cm.id
where xtype = ' P ' or xtype = ' V '
ORDER BY O.xtype,o.name,cm.text

SELECT * from sysobjects
where xtype= ' u '

--Query all table names, field names, types, lengths
Select O.name, c.name,t.name,c.length from syscolumns C
INNER JOIN systypes t on c.xtype= t.xtype
Inner JOIN sysobjects o on c.id= o.id
where o.xtype= ' u '
Order by O.name, C.name,t.name

--All the data comes from these four sheets
--select * from sysobjects
--select * from syscolumns
--select * from syscomments
--select * from systypes

--------------------------------------------------------------------Related Technology------------------------------------------------- --------------------------------

2.2: Operation Steps

Put the result set into a table with 2.1 of scripts and SSIS, such as the BACK_UP library

This allows us to back up all the views and stored creation scripts for the ODS and DW, restore the view first and then execute the storage to recover the data, and don't forget to restore by hierarchy

Implementation results:

We have implemented the backup object structure of the small data volume method to back up the data warehouse related structure and extraction method, recovery is a time to extract the problem

Of course, if the conditions allow, we still recommend the dual-machine hot standby, as well as the legendary disaster recovery backup, in addition to the problem of switching about the ideal solution, here only a small think

The road, but also in a specific environment, general if each layer of DW is extracted with kettle or SSIS ETL tools, then we will save

, is not only the backup ETL program is good, in fact, the situation is still more complex, such as we have manually created some enumeration values table, and so on, the next face data

Warehouse huge amount of data, do you feel it necessary to back up every day? Do you have a good idea? So write to tell me, hope and everyone in the bi this road together progress and mutual encouragement

Data Warehouse Backup Ideas