backup selected month from database

Question

backup selected month from database

hodhod26

Mr or Mrs. 500

Points: 515
More actions
February 1, 2010 at 11:25 am

#216063

HI all,
i have database with various tables with various content.
my boss want me to backup every month in the database (they are 2 years :w00t:).
so is there any way to backup every month indivualy ( january , february and so on)
if there is any details not clear ask me and i will answer.
thanks for all.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

To do that, you'd have to first partition the data into separate files for each month, then you could back those files up.

Is the data partitioned?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

hodhod26 Mr or Mrs. 500 Points: 515 More actions · Answer 2

the data not partioned.

and i don't know how to do this. i new user for database.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

If you want to follow GSquared advice, I would advice to read up on Table partitioning first. There is a very good white paper on Table partitioning for SQL 2008 in MSDN

http://msdn.microsoft.com/en-us/library/dd578580.aspx

-Roy

Alan. T. Hall of Fame Points: 3221 More actions · Answer 4

Roy Ernest (2/1/2010)
If you want to follow GSquared advice, I would advice to read up on Table partitioning first. There is a very good white paper on Table partitioning for SQL 2008 in MSDN
http://msdn.microsoft.com/en-us/library/dd578580.aspx

Thanks I hadn't found this in a single document before.

Alan

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 5

Partitioning is only for tables, for certain data. Note that backing up a partition, without considering all of the other related data, might not get you something you can use down the road.

I would question your boss as to what data needs to be backed up and for what purpose.

hodhod26 Mr or Mrs. 500 Points: 515 More actions · Answer 6

I would question your boss as to what data needs to be backed up and for what purpose.

we send SMS for people. this table contain custmers join the services and custmers leave the services.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 7

I have had to do this for auditing purposes before. The ledgers had to be backed and moved off site to provide an audit able change control for security.

I ended up creating a blank archive database that contained all of the routines to move the data to archives in the data base. The end result is a backup name for the month it is an archive. Note: this had to be fully scripted so it could be audited as well. The work flow was as follows:

1. Restore the blank archive database

2. Run a copy package. Today I would use SSIS copy the necessary data using the a set of variables to control the date range

3. Rename the database to the month you are archiving

4. Take a full backup using the renamed name

5. Copy the backup to read only media

6. Delete the new archive database

The date range was used since this would be run after the period had been officially closed so it could be run at any date for any range.

Hope that helps,

Alan

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 8

Ummmm.... what would be so bad with doing a BCP "Native" export for the month data involved? It would simplify things quite a bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 9

I would prefer the suggestions given by Jeff and Alan.

Partitioning the database might be a possibility only if the Edition is Enterprise Edition.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

GSquared SSC Guru Points: 260824 More actions · Answer 10

Going to give another vote for a standard archiving package for this.

Rather than archiving into a database, I might just export to csv or something simple like that. Would depend on the complexity of the data. But it sounds like it's a single table, and csv or tab-delimited would handle that efficiently.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 11

I'll disagree in that I like the backup to database. It's easy to copy that data back from a restored db to your primary, and you'll easily identify this as SQL data. Might not be as easy in CSVs if someone is looking for the data later. I could see someone confused.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 12

To continue Steve's comment one of the biggest issues with flat files restoration is maintaining all of the relationships. To use flat files you would also need complete and up to date documentation of all relationships used within the tables. If you have ever had to find the source look up tables in a large database to reestablish foreign keys you get the idea. A database backup that includes the key and constraint structure allows data to be merged in any way the user needs including a data warehouse.

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 13

To use flat files you would also need complete and up to date documentation of all relationships used within the tables.

Nope... that should all be in the code you use to gen the files with.

I'm not suggesting doing away with any backups. A good backup schedule for the entire database is always and absolutely required. The CSV's (or whatever) would only be available as archives that could be temporarily loaded into a temporary DB if someone absolutely needed them. This type of thing is frequently done with fully denormalized data so that you don't need to maintain relationships... they live on each row.

I used to do this type of thing with CDR's (Call Detail Records). When you're collecting 4 million a day, it was just a whole lot easier if the FBI, SEC, or whoever subpoenaed a months worth of CDR's for a given set of phone numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)