How to set up a stable backup plan?

Our database is more than 20,000,000 bytes and increase largely everyday

who can give me some advise about stable backup?

My database is sqlserver2005 and now i use files to backup including full backup and diff backup,whether is better to use device?

any ideas? thanks in advance

Your backup plan will depend on your recovery point objective (RPO) and recovery time objective (RTO). How long should it take for you to restore your databases should a disaster strikes? How critical is your data and from when do you need to have your data restore? You should also look at your database recovery model as this will affect your RPO and RTO.|||

thanks in advance,however RPO and RTO seem a little different for me,can u give me some details about that? and any ideas about what i did now

1.I first set up maintenace plan ,backup full and diff database to files and specify schedule

2.set up a job,type is ssis and package is from "maintencace plan/***" and specify schedule same with backup plan

two issues:

1.whether i need to backup transaction log in maintenace plan?

2.what's the difference backup to files and backup devices? which is better?

I think if i write T-SQL script in job and backup database to device, looks simple,is it right?

any ideas about thisT-SQL script?

much appreciate

|||

Hi Andrew,

RTO is the maximum amount of time that the service can be unavailable whereas RPO is the maximum amound of data that can be lost following a disaster. So, your requirements may be to have the service back up and serving clients within 2 hours following a hardware disaster (RTO), but you could afford to lose 1 full day's worth of data because, for instance, it's easily reproduced (RPO).

Define your RPO and RTO requirements first, then a stable, suitable backup plan can be created to meet the RPO requirement. Although your chosen backup strategy will contribute to meeting your RTO objective, RTO is usually fulfilled via high-availability options such as clustering, mirroring, offsite dr etc. (assuming, of course, that your RTO dictates a high-availability solution!)

For an example, you could define a daily full backup job at 00:00, followed by a tran log backup job every 15 minutes. This would give you an RPO of 15 minutes.

Cheers,

Rob

|||

much appreciate for all

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Lenght for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan