x

DB archiving

I am using a SQL 2008 express edition. Windows Client-server architecture. Datalayer sits in the server. I am planning to implement database archiving and DB backup restore . the difference between them is in archiving i need to delete the entries after i take the backup.

I also need to restore the DB in a different pc. what are the mechanisms i should be looking at.
more ▼

asked Feb 27 '13 at 07:02 AM in Default

jctronicsshiva gravatar image

jctronicsshiva
470 9 14 16

SQLExpress? Really? Why?
Feb 27 '13 at 08:42 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Since you're using Express you won't have access to SQL Agent, so you'll need to schedule and control this through your own code. Backup and restore are obvious topics to look up. Since you're planning on deleting data I'd suggest ensuring that your database is in the right recovery model and if you have it on Full that you have log backups in place. Further, you should focus on breaking up your delete transactions into smaller chunks to ensure that you don't have to grow the log to large sizes.

Except for the lack of SQL Agent that's pretty much the same things to cover that you'd need to do if you were on the full product.
more ▼

answered Feb 27 '13 at 10:43 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

Hmm.. thanks for the reply.. Can't i use SMO objects?. Basically i want the database to be archived on the user input. I don't want to run a scheduled task .
Feb 27 '13 at 11:46 AM jctronicsshiva

That wasn't clear based on the question. Backup and restore are very specific terms within SQL Server but not what you actually wanted here. You're talking about copying the data from one source to a different target and then deleting it on the source. Totally different than a backup which is a page level copy of the entire database (or a page level copy of the log, or some other variations).

If you want to do this on the fly as data gets added, you're probably better off looking at triggers, but archiving on input gets weird. You're going to let them insert a row, copy the row, then delete the row? Why insert it at all in that case?
Feb 27 '13 at 12:01 PM Grant Fritchey ♦♦

May be i was not very clear. I am planning to use full recovery model

I want to implement both Archiving and Backup/restore. In archiving, i basically want to delete the rows once its physically stored .

In backup,i need not delete the database once i store it physically. restore is same as described.

In both cases i should be able to restore back at a later case may be in a different PC.

So for archiving what are the best techniques ? For back up and archive i thought of using SMO objects.
Feb 27 '13 at 01:05 PM jctronicsshiva

I'm more confused than I was before. You want to delete a row after it's stored? I apologize for my lack of understanding, but that makes no sense. If you store a row, you store it. If you delete and store it... I'm lost. Sorry.

I'm completely missing what you're going for. If you're attempting to insert data and then, in some fashion, ensure that you can recover it somewhere else, we're not talking archiving. We're talking backups. There are full backups and log backups that you need to concern yourself with. A full backup can be taken as often as is practicable, let's say daily. Then, during the day, you can take log backups every 1/2 hour. This ensures that in the event of a failure on the system, you can restore to a point in time with no more than 1/2 hour of data lost.

SMO is not a part of this process.
Feb 27 '13 at 01:24 PM Grant Fritchey ♦♦

If this is something more than you practising at home and there is something important about the data then yes you really ought to consider upgrading from Express. You get the automation of SQL Agent, larger database sizes, more effective use of the hardware that the SQL server is installed on and more.

Feb 27 '13 at 08:58 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

My personal opinion...it sounds like you're using SQL Express to save some dough when you really need to store much more data than it can handle. What I think you're proposing to do is not really archiving, but copying a database and truncating or deleting just as dykesa says above. This will do nothing but cause you major headaches down the road. My advice if my assumption is right is to spring for the license or find an alternative that can handle the load.

And if my assumption is not correct, well, then I just don't understand! :)
more ▼

answered Feb 27 '13 at 01:53 PM

Zapper gravatar image

Zapper
40

(comments are locked)
10|1200 characters needed characters left
Well, for the archiving bit, it sounds like you just want to run a truncate on all the tables after you take the backup. To restore a backup on a local machine, we use a powershell script to go out to the network, copy the latest backup locally, and then restore it. Just a thought.
more ▼

answered Feb 27 '13 at 01:35 PM

dykesa gravatar image

dykesa
10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x51
x44
x8

asked: Feb 27 '13 at 07:02 AM

Seen: 592 times

Last Updated: Feb 27 '13 at 08:58 PM