Copy Schema

Hi,

What do I need to do in order to make a copy of a schema. For example lets say my database has 3 schemas - Test, Production and Staging. I want to make an exact copy of Production. ie. complete copy - all objects - tables, views, stored procs and also all the data and probably call it Production-New. Now we have 4 schemas in the database.

This needs to be initiated from the app that the user is currently using. Is there any built in support (TSQL functions/commands/Sps ?) for such an activity ? and if not what may be the direction that I need to look at to take this further ?

Overall I'm looking at being able to take a quick snapshot of a schema and start using that. I must mention that the volume of data will be substancial and the solution devised should be able to do it rather quickly - The user clicks a button in order to take a snap shot. So the wait time has to be reasonable.

Any suggestions/ideas will be great.

Thanks -

Avinash

If you mean an exact copy, then I just do a backup and restore. You can write a batch file to do this pretty easily. Write a backup stored script and a drop database/restore script and execute them with SQLCMD (or OSQL/ISQL for earlier versions)

The reason I suggest using a batch file is that you can run it as a user that has file system rights to the two databases and integrated security to make a copy to a different server.

For more elaborate schemes where you only want part of the data, then SSIS/DTS would be the way to go, but backup/restore is very straightforward.

|||

Many thanks MS Louis Davidson for your response.

Will try out SSIS.

Thanks and Regards,

Avinash