• 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.

    http://blogs.msdn.com/b/felixmar/archive/2011/02/14/partitioning-amp-archiving-tables-in-sql-server-part-1-the-basics.aspx

    http://blogs.msdn.com/b/felixmar/archive/2011/08/29/partitioning-amp-archiving-tables-in-sql-server-part-2-split-merge-and-switch-partitions.aspx

    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/