Copy database

Hello,
hope this is the correct group.
I am looking for an easy and safe way to create a copy of an existing live
database and put it on the same SQL Server in a test database.
Has anyone done this before?
I am not an experienced SQL admin and will really appreciate your help!
TIA!
other than what Paul mentioned you can use DTS to push it over. DTS allows
you to select a subset of the objects or data.
With a backup restore you will get an entire copy. Sometimes for space
issues this is not ideal.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:FB20DAC9-0F80-4AF6-AA6C-BADB0CAFB9B7@.microsoft.com...
> Hello,
> hope this is the correct group.
> I am looking for an easy and safe way to create a copy of an existing live
> database and put it on the same SQL Server in a test database.
> Has anyone done this before?
> I am not an experienced SQL admin and will really appreciate your help!
> TIA!
|||Thank you, Paul and Hilary.
It happened I dropped 3 tables from test db, then I copied them from
production to test db, but of course, having all data in place, they miss the
dependancies.. Is there a way besides recreating(copying) the whole db, to
roll back all these transactions?
"Paul Ibison" wrote:

> The simplest way is backup and restore (details in books
> on line). You can restore the database to another name
> and location than the original. If the database has to
> have the same name (due to eg 3 part naming convention
> being used in queries), you can restore to another
> instance of sql server on the same computer.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It depends on the recovery model. If you are in full or
bulk logged, then backup your current transaction log
then do a point-in-time restore back to just before you
dropped the tables. Of course any other work done since
then will also be removed.
If this is not viable, you could create a script of the
dependencies (I assume you mean FKs) from thje live
system and apply it to the test system. Such a script can
be generated by Enterprise Manager by right-clicking on
the relevant tables and selecting all tasks, generate sql
script, with the PK/FK option selected (final tab).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I have full recovery model. How would I start a point-in-time restore?
"Paul Ibison" wrote:

> It depends on the recovery model. If you are in full or
> bulk logged, then backup your current transaction log
> then do a point-in-time restore back to just before you
> dropped the tables. Of course any other work done since
> then will also be removed.
> If this is not viable, you could create a script of the
> dependencies (I assume you mean FKs) from thje live
> system and apply it to the test system. Such a script can
> be generated by Enterprise Manager by right-clicking on
> the relevant tables and selecting all tasks, generate sql
> script, with the PK/FK option selected (final tab).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jane,
I'm assuming you have a full database backup, as this won't work without it.
You need to restore this backup and any backups you have of logs. If you
haven't done any, then it'll just be the backup you make of your live log.
Each restore uses a similar syntax and is done with NORECOVERY, apart from
the last which is done with RECOVERY. For the log which has the 3 table
deletes in it, you'll be using the syntax STOP AT datetime
eg this restores the database and 2 logs, with the senond stopped at a
certain time:
RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERYRESTORE LOG
MyNwind FROM MyNwindLog1 WITH NORECOVERYRESTORE LOG MyNwind FROM
MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'See this
reference for more
info:http://msdn.microsoft.com/library/de...y/en-us/tsqlre
f/ts_ra-rz_25rm.asp HTH, Paul Ibison SQL Server MVP,
www.replicationanswers.com (recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you, Paul.
It turned out we do not backup a test database, so I went on and created a
script of the dependencies from the live system table with all "Table
Scripting Options". How would I apply the script to the table int test db?
"Paul Ibison" wrote:

> Jane,
> I'm assuming you have a full database backup, as this won't work without it.
> You need to restore this backup and any backups you have of logs. If you
> haven't done any, then it'll just be the backup you make of your live log.
> Each restore uses a similar syntax and is done with NORECOVERY, apart from
> the last which is done with RECOVERY. For the log which has the 3 table
> deletes in it, you'll be using the syntax STOP AT datetime
> eg this restores the database and 2 logs, with the senond stopped at a
> certain time:
> RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERYRESTORE LOG
> MyNwind FROM MyNwindLog1 WITH NORECOVERYRESTORE LOG MyNwind FROM
> MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'See this
> reference for more
> info:http://msdn.microsoft.com/library/de...y/en-us/tsqlre
> f/ts_ra-rz_25rm.asp HTH, Paul Ibison SQL Server MVP,
> www.replicationanswers.com (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Just open up query analyser, log onto the test system,
move to the test database, paste the script and hit F5.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am uncertain about Formatting options when generating script:
- Do I really need the Generate CREATE/DROP commands? The table already
exists (I imported it from production db). Or should I drop and recreate the
table through the script - maybe this way it will have all the missing
dependancies?
Then I will import the data into table?
Thank you so much for hanging in there with me!
- I probably do need "scripts for all dependent objects"
Do you think it
"Paul Ibison" wrote:

> Just open up query analyser, log onto the test system,
> move to the test database, paste the script and hit F5.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jane,
as you have the (3) empty tables on the test db, running
the script will just drop and recreate them, but along
with the extra constraints you need. Check that you only
have the (3) table scripts you need for this and not any
others. If in any doubt, post up the scripts and please
explain what I'm missing and I'll edit them and post them
back.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)