Is the archive going to be exactly the same as the existing db?
here is what I do - there may be better ways.
In Enterprise Manager - create a blank db (MyDB_Archive) on any sql server machine could be the same as the source db (MyDB) server.
Add all the same database users to Archive db.
Go to MyDB right click >
Export Data >
you will get DTS Export / Import Wizard >
Click next >
enter source db info - server name, sa user and password, select source db >
Next >
do the same for destination db >
next >
Select the bottom option "Copy objects and data between SQL Server databases" >
Next >
un-check the bottom check box "Use default options" >
Click the Options button >
un-check the first check box "Copy database users and database roles" >
OK >
Next >
Next >
double check your destination and source >
finish. - will take awhile depending on size of db.
hope this is what you are looking for?
If you do not care about maintaining permissions then you can skip adding the users to archive db, but you must also un-check the box "Copy object permissions" that is on the same screen as the "Copy database users and database roles" check box.
|
