|
Home > Archive > MS SQL Server New Users > June 2005 > Creating a copy of a database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Creating a copy of a database
|
|
| msnews.microsoft.com 2005-06-07, 7:23 am |
| Hi,
Probably a silly question but, I have a database which is currently live. I
would like to create a copy of that database on the same server though under
a different name for testing purposes.
I'm really not sure as to how to go about this. I imagine that the best way
would be to recreate the tables, SP, views, triggers, etc etc in a new
database and then using DTS to copy the data to the new database but I have
no idea about how to recreate my existing database.
If anyone could supply any pointers, links or any other information it would
be gratefully appreciated.
Thanks
Chris.
| |
| Hari Prasad 2005-06-07, 7:23 am |
| Hi,
Generating scripts and creating a database is lengthy process. Easy method
is Backup and Restore.
1. Backup the database (See Backup Database command)
2. Restore the database with new name and with Move options (See Restore
Database command in books online)
Thanks
Hari
SQL Server MVP
"msnews.microsoft.com" <hotmail@solace1884.com> wrote in message
news:%23$UzjJ1aFHA.3840@tk2msftngp13.phx.gbl...
> Hi,
>
> Probably a silly question but, I have a database which is currently live.
> I would like to create a copy of that database on the same server though
> under a different name for testing purposes.
>
> I'm really not sure as to how to go about this. I imagine that the best
> way would be to recreate the tables, SP, views, triggers, etc etc in a new
> database and then using DTS to copy the data to the new database but I
> have no idea about how to recreate my existing database.
>
> If anyone could supply any pointers, links or any other information it
> would be gratefully appreciated.
>
> Thanks
>
> Chris.
>
| |
| Steen Persson 2005-06-07, 9:23 am |
| You could also run sp_detach_db, copy the .mdf and .ldf files to some new
names and then run sp_attach_Db to attach the new files as a new database.
The downside of this method compared to Hari's answer, is that your existing
database will be unavailable while you copy the files.
Regards
Steen
Hari Prasad wrote:[color=darkred
]
> Hi,
>
> Generating scripts and creating a database is lengthy process. Easy
> method is Backup and Restore.
>
> 1. Backup the database (See Backup Database command)
>
> 2. Restore the database with new name and with Move options (See
> Restore Database command in books online)
>
>
>
> Thanks
> Hari
> SQL Server MVP
>
> "msnews.microsoft.com" <hotmail@solace1884.com> wrote in message
> news:%23$UzjJ1aFHA.3840@tk2msftngp13.phx.gbl...
| |
| msnews.microsoft.com 2005-06-07, 9:23 am |
| Steen, Hari,
Many thanks for your replies. The backup/restore approach seems to have
worked although I'm still scratching my head for the best way to update the
"live" database if I make any changes with the "test"...
Thanks again.
Chris.
| |
| Sue Hoegemeier 2005-06-08, 3:23 am |
| If you script all of your changes in test, you can execute
the same scripts in production.
-Sue
On Tue, 7 Jun 2005 14:48:17 +0100, "msnews.microsoft.com"
<hotmail@solace1884.com> wrote:
>Steen, Hari,
>
>Many thanks for your replies. The backup/restore approach seems to have
>worked although I'm still scratching my head for the best way to update the
>"live" database if I make any changes with the "test"...
>
>Thanks again.
>
>Chris.
>
| |
| Chris Strug 2005-06-09, 7:23 am |
| "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:s9kca1dgjfvln6b
3e3p1a0963ur3k1rajg@
4ax.com...
> If you script all of your changes in test, you can execute
> the same scripts in production.
>
> -Sue
>
Silly question but how do I go about doing that exactly? I'm assuming that
there's something fairly substantial in EM that I've missed....
Cheers,
| |
|
| First let me give you the traditional DBA lecture on using Enterprise
Manager as a method of modifying a database:
"Don't do it, Don't do it, and Don't do it. That being said, don't do
it.".
OK, now if you need to do this, or if you're just starting out with
SQL, and you want to see how the EM GUI makes changes to an underlying
object, you can save the script file from the design mode. When in
design mode, after you make a change to a table but BEFORE you save it,
there's an icon on the toolbar that looks like a disk in front of a
peice of paper; click it, and your change script will appear in window
from which you can cut and paste.
That's still a bit kludgy to keep up with, however, particularly if you
have a lot of changes to make. There really isn't a good (read:free)
way to use versioning with SQL Server for development purposes. I have
developed a scripting process for use at work that relies on Visual
SourceSafe, but it is far from ideal. Basically it all comes down to
you keeping track of what changes you make, and the scripts you used to
make those changes. The less you rely on the Enterprise Manager to
make those changes, the easier it is to keep up with them.
Hope that helps, even though it really doesn't solve your problem. You
may want to search for SQL Server Versioning or Source Control
software.
Stu
|
|
|
|
|