Database Archival Procedure in SQL 2005

Question

Post reply

Database Archival Procedure in SQL 2005

guruprasadurs

Mr or Mrs. 500

Points: 530
More actions
August 23, 2012 at 1:54 am

#393011

Hi,
What is the best archiving technics followed in SQL Server 2005. Please let me know any step by step guide is there.
Thanks in Advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

When you say "archive" do mean:

1. Backups?

2. Remove older or inactive records from a database?

3. Keeping a live copy of the database somewhere?

4. Any other definition not covered above?

The word archive is somewhat generic and more precise requirements would be appreciated.

Thanks!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

follow this as well and answer the above if this is not your query

http://www.sqlservercentral.com/Forums/Topic1348066-146-1.aspx

Regards
Durai Nagarajan

guruprasadurs Mr or Mrs. 500 Points: 530 More actions · Answer 3

yes backup need to keep that backup archive for next 7 years

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

do you want to keep old backup for 7 days?

Regards
Durai Nagarajan

guruprasadurs Mr or Mrs. 500 Points: 530 More actions · Answer 5

Thanks for your reply..:)

1. I will take the backup of final DB after my final data entry. And I will keep it for 7 years.

2. What is the solution if I need to take onlline archival every day in Live database.

Thanks in Advance

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 6

At the risk of repeating myself, what do mean by Archive? Archive has no real meaning in SQL Server.

Try Googling the phrase

sql server archive

and see if you can get some help here. Also look at URL's that other people here have provided for you.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

durai nagarajan SSCoach Points: 15778 More actions · Answer 7

as robin said your explainations are confusing, can you reply to his first reply.

Regards
Durai Nagarajan

guruprasadurs Mr or Mrs. 500 Points: 530 More actions · Answer 8

Thanks Robin for your reply,

I have Final database which is around 100 tables and around 500 rows data. I can keep backup copy but I need to do archived copy. Need to keep for future use after 5 years. I dont know how to do archive of these many tables.

It will be great full and very helpful to me if you provide any guidance.

Many Thanks in advance

Looking forward to here in from you soon

durai nagarajan SSCoach Points: 15778 More actions · Answer 9

500 rows are smaller tables you can think of move old data to different table/DB once it is over 4 or 5 millions records in a table.

are you facing any slowness to plan for this at this stage?

Regards
Durai Nagarajan

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 10

From what I understand you want to keep a copy of the live database available for the next five years?

If that's the case, do the following:

1. Backup up the database

2. Restore the database as a different name onto a SQL server of your choice

3. Optionally, decide if you want to make the database read only to prevent changes - Pinal Dave has an excellent post at http://blog.sqlauthority.com/2011/04/16/sql-server-making-database-to-read-only-changing-database-to-readwrite/

Hope that helps but again, I must stress using the word Archive doesn't help.

Good luck!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation: