asked
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.
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.
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.
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.
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 .
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?
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.
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.
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! :)
16 People are following this question.
