Does anyone got an idea on how to archive a Microsoft SQL Server Database?

[Prash]

I've to design a system that uses ASP to interact with the user. it collects some data from the user and stores them to a Microsoft SQL Server Database. I've designed to do this job.

But, I failed to understand the logic behind archiving old database. I want the database to be archived once in every two week. Can anyone suggest some link, where I can find them?

[vaibhav p]

Better u write a file handelling code.Which accesses SQLDMO object to stop server & allow u take a backup otherwise u mannually stop server then goto sql directory then data copy paste <DBName>.mdf *& .LDF file & add it to archive

[rod]

Assume you add 10 users every two weeks. Your first archived Db will have 10 users in it--your second will have 20, etc. The question is do you want to continue to store all data in each archive or do you only want to store the last two weeks?

As for archiving, it's pretty simple. Merely make a copy of the database, and compress it if you want to save storage space. Add the date to the name, like "MyDatabase20070412".

If you only want current data each time, then create an empty Db. Each time you archive, you rename the old Db with the date and make a copy of the blank Db. Name the blank copy the same thing the old copy was named and move it to the correct file path.

hope that helps