SQL Server has internal options for backing up databases. However, if you want to back them up via script and make the databases portable or for offline use, this script will show you how to use DMO to detach and reattach SQL Server databases. <BR><BR>Also shows how to create Full Text catalogs. You can remove that code if you want.<BR><BR>http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=87<BR><BR>
Is there anyway which I can do this thru an ASP page without detaching the whole database. I know I can manually create a new database with the exact fields as the source database and populate the target database. Itz juz that I think there should be a more efficient way to do this right?
What are you exactly trying to accomplish? More background info needed. Do this all need to occur while people are still accessing the database? The detach, copy to another folder, reattach is only going to take a couple of seconds.
Some info on what I am trying to achieve. Currently I have a database on sales forecast system. Every quarter of the year, the sales manager will need to "archive" the database. Effectively, this is just copying the data from the existing table to a new table and then deleting the data off the old table. In order for me to code such a function, I need to come out with a module that is extremely flexible in doing this as he will want to specify the exact tablename and have the ability to call out the "archive" data as and when he needs it.
I think you'll run into long term support problems. How will an end user know which tables have referencial integrity constraints with other tables? I would think you'll have to hard code in mandatory table "groups" to ensure you don't screw up your database integrity.<BR><BR>As far as actually accomplishing it, you can either use BCP (Bulk Copy) or create your archive tables with a primary key that is not auto generated.<BR><BR> delete from tableB where blah=blah<BR> insert into tableB<BR> select * from tableA<BR> where blah=blah <BR>
I have no worries about the rest of the tables reference. My only concern is to archive the main transaction table and I am done. So from your opinion you will think that by manually creating the Table and then populate is the best way round to tackle the situation?