archive sql server database

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have a database in SQL Server 2014 and want to archive it and delete the original database data and import NEW data and I want to "restore" the archive just to view the dta, I want arcive the data every 3 month (the above explained procedure). In vb net is it possible?

Posted 28-Jul-15 22:43pm

Member 3892343

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-28T23:16:00

Solution 1

First of all I'm not sure that archiving is the best solution. The feasibility depends on why you're doing this. If it's because of performance reasons, I would strongly suggest searching other alternatives and optimizing the database and it's usage.

What comes to archiving, you have multiple choices. You can:

copy the data elsewhere using replication
detach the database, make a copy elsewhere and reattach, the detached database can be used as a separate database.
use SSIS to create data copy jobs etc

Of course you could also write a VB program to do at least part of the job, but I'd use SQL Server capability as much as possible.

Posted 28-Jul-15 23:16pm

Wendelius

Comments

Member 3892343 30-Jul-15 1:51am

It is storage issue I store and archiving text and photos in the database soon I will exceed the storage limit of SQL per database, I want to archive the records every 6 months (about 2 GB per 6 months) and recall them when needed, I have hear of olap technology but cant find any tutorial and I want to do it by vb or c# .Net

Wendelius 30-Jul-15 3:24am

First of all 2 GB doesn't sound so much, SQL Server can handle a lot more. Are you using Express or what is the limitation?

As far as I can see, OLAP isn't any solution in this kind of case, it's targeted for data warehouse reporting.

Member 3892343 30-Jul-15 6:04am

use Express 4 gb limit i think, found some code to archive but don't have it here am at Office, nevertheless is there any code tutorial? this I found is not so clear.

Wendelius 30-Jul-15 7:02am

I'm not sure which tutorial you're after. In your situation I'd probably use detach/attach scenario. This way I could have a separate database for example for each half a year. THe procedure would be something like
- detach database
- make a copy of it
- attach the database back
- attach the copy as a different database
- remove rows from original database.

An alternative would be to create a new database and just insert into..select from for each table to make an archive to another database