February 1, 2010 at 11:25 am
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.
February 1, 2010 at 11:30 am
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
February 1, 2010 at 11:32 am
the data not partioned.
and i don't know how to do this. i new user for database.
February 1, 2010 at 12:36 pm
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
February 1, 2010 at 12:59 pm
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
Thanks I hadn't found this in a single document before.
Alan
February 1, 2010 at 1:07 pm
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.
February 1, 2010 at 1:11 pm
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.
February 1, 2010 at 1:16 pm
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
February 1, 2010 at 6:00 pm
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
Change is inevitable... Change for the better is not.
February 1, 2010 at 6:40 pm
I would prefer the suggestions given by Jeff and Alan.
Partitioning the database might be a possibility only if the Edition is Enterprise Edition.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2010 at 6:08 am
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
February 2, 2010 at 7:35 am
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.
February 2, 2010 at 7:50 am
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.
February 5, 2010 at 4:30 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply