September 8, 2011 at 5:13 am
hi,
well i have a db of 20gb i want to archive some data into a archive db.
So,kindly guide me how to create a archive db in sql server 2008 web edition.
and what is the effect of archiving data in db server disk spaces.?
all suggestion is well come.
September 8, 2011 at 5:30 am
What's the business need for data retention?
What's the need to access the data (how long can they wait and how fast does that access need to be performance wise).
20 GB is NOTHING these days.
We have a small ERP db here of about 25 GB and with the whole backup / restore test servers & all, we have over 1 TB allocated for that system on our 2 fastest sans. Yes I know this is 40 X the allocation.
Why do you think you need to archive now?
September 8, 2011 at 7:48 am
There isn't a switch or setting that creates an archive. You have to create a second database, or a second set of tables within your database, and then migrate the data into that new location. It's just a matter of defining the process and building the code that's necessary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2011 at 8:14 am
If you can have an archive offline, just take a backup and store that. If you need it online, create a new database, as Remi and Grant suggested, and then it's an ETL process to move the data. INSERT..SELECT from one database to the other.
September 8, 2011 at 9:41 pm
hi all,
well i will create an another db in my instance and transfer the required data from primary db to that archive db .
well for example if i am transffering 6 gb of data from the primary db to that archive db what amount of space will that archive db will consume.i mean it will have 6 gb total usage of less then it.
will the 6 gb data will be compressed to around 3 to 4 gb.
all suggestion are well come.
September 9, 2011 at 12:43 am
well for example if i am transffering 6 gb of data from the primary db to that archive db what amount of space will that archive db will consume.i mean it will have 6 gb total usage of less then it.
will the 6 gb data will be compressed to around 3 to 4 gb.
all suggestion are well come.
September 9, 2011 at 12:48 am
Are you going to perform an archive and purge?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 9, 2011 at 12:51 am
i am going to archive. and what is purge?
can u plz explain
September 9, 2011 at 12:55 am
yes i am going to do both
September 9, 2011 at 1:03 am
Purging helps you to reclaim free space in the database. If you are expecting the free space in the OS level you need to do db shrinking. And DB shrinking is not recommended.
September 9, 2011 at 1:05 am
well for example if i am transffering 6 gb of data from the primary db to that archive db what amount of space will that archive db will consume.i mean it will have 6 gb total usage or less then it.
will the 6 gb data will be compressed to around 3 to 4 gb?
September 9, 2011 at 1:08 am
Ivan Mohapatra (9/8/2011)
hi,well i have a db of 20gb i want to archive some data into a archive db.
So,kindly guide me how to create a archive db in sql server 2008 web edition.
and what is the effect of archiving data in db server disk spaces.?
all suggestion is well come.
Depending upon what you are doing you could partition the table(s) are perform a Merge Split.
If not you will have to create some Tables and Stored Procedure.
I did this a few times but unfortunately I was not able to retain the code.
I wrote a script to create the Archive the Database. Create Logins, Users and permissions.
I wrapped the Archive (Select from the source to the Archive Database) into a Transaction.
I created a control table where I stored the batch size so that I could process a certain number of records at a time.
I also had a column that indicated the Total Number of Records to Process.
I also had a column to store a boolean value (Active_Inactive) so that I could determined whether to terminate the program before it processed the next batch.
I create a Stored that had a While Loop and it each time it would check the Active Column as well as the batch size (in the event that you decided to change the batch size).
I also created a log table so that I could track what occurred and when, etc.
I also had to create a view because the users wanted to include the archived Data in their Queries.
I have to agree that 20GB is not that much Data. So consider that before you embark on this.
I hope this helps.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 9, 2011 at 1:11 am
will the transfered 6 gb data will be automatically compressed to around 3 to 4 gb after doing archive.
September 9, 2011 at 1:22 am
Ivan Mohapatra (9/9/2011)
will the transfered 6 gb data will be automatically compressed to around 3 to 4 gb after doing archive.
Do yo mean will it not be fragmented or compressed.
In your script to create the Archive Database spefify a size that will not result in fragementation.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 9, 2011 at 1:28 am
i mean if i create a archive db and transfer some around 8 gb of data into that archive db from my primary db and then purge the 8gb data in the primary db.then what space will that 8gb space of archive db will occupy in the server.
what i have hear that archive db occupies less spaces in the server as compared to normal db.?
kindly suggest what is archive db and there benifts related to space in the server.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply