Coping Database with TSQL

[svagelis]

My objective is to do the following
1 year after the creation of the database i want to create a new one with the same tables , st.procedures,roles , users (of course some tables will be empty p.e orders etc)
I was thinking of copying the entire database with a different name and then initialize the tables in it
(all this should be done with TSQL beacause i will include the entire procedure in a stored procedure with parameters)
I want to do that because my application should switch from year to year ( or 6month period) by altering the name of the database only from a combo box within the apllication.
Thanks

 

[Bernadette]

I would suggest that you Script the Database schema and save it to a Script file.
You can do this by
[ol]
[li] Select the Database you wish to script[/li]
[li] Click on the Tools Menu [/li]
[li] Select Database Scripting ... [/li]
[li] Select All Objects [/li]
[li] Under the "Formatting" Tab you can use to script, logins, users, permissions, indexes, triggers and contrainsts. Remember though if you script the logins, their password will default to NULL, so maybe you should amend this manually.
[li] Save as an .sql file[/li]
[/ol]

You can then amend this script to include creating a new database. You can run this script as a job every 6 months. Although you would probably have to amend the name of the new database in the script if you want to give it a new name each time. You could take in the new name as a parameter - from your combo box. You would only need to put

Use MyNewDatabaseName before the script generated by sql server to forece the objects to be created in your new database.

Hope This Helps :->
Bernadette

 

[svagelis]

Thanks I think that ll do the job
One thing remaining
Who do i move data from one database to the other through that script ?

 

[Bernadette]

What you could do is create a dts package to transfer the data to your new database and shedule this to run also. You will probably need to also change the name of the destination database every time to match the name of the new database.


Hope this helps :-> Bernadette