Creating a test environment

I just got my first job doing some DBA work (junior level) in a small company using SQL Server 2000.

I would like to make a copy of the production DB and use it for development of SP's, DTS packages and views.

What is the safest way for me to copy the entire production DB, to a new DB on the same server?

Should I create a new DB, and do a restore of the production backup?

Any help would be appreciated,
Thank you.restoring from a backup is probably the most common way.|||restoring from a backup is probably the most common way.

Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...

Thanks for the reply|||Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...

Thanks for the reply

you can try restoring the backup via Query Analyzer. Try consulting BOL for the syntax. Based on my experience it creates the database along with the devices. You can also specify that it move it's devices to a specific path.

Creating a script file from a diagram

Hi there,

I have created a diagram on my development SQL2005 database and want to transfer this diagram to my live environment. How do I go about creating a script to do this?

thx

Dawid

There is no way to script out diagrams in SQL Server 2005. The binary data in the diagram is tied to your SID (a unique identifier for security principals) when you created the diagram, so I can't even give you foolproof instructions on how to hack the tables to copy the diagram data.

I'm afraid you'll have to recreate your diagram on your live system. If this is a feature you'd like to see in the next version of SQL Server, please file a suggestion on http://connect.microsoft.com/SqlServer. Customer feedback is a very important criterion when we choose which features will make it into the next release.

Thanks,

Steve

|||

I highly recommend using Visio (or one of the other robust diagramming tools) to create your database diagrams.

Printouts are easier. Documentation is easier. Scripts are easy. Changes to the diagrams can be saved WITHOUT changing your database. (Great for development environments.) Multiple step Un-Do/Re-Do!!