Copy database for testing on same SQL Server?

I have a live and functioning database on my lone SQL Server 2000. I would
like to create a copy of this database on the same server for when my 3rd
party provider releases updates (SQL scripts) I don't have to immediat3ly
apply them to my live database. If the script causes a failure I want to
discover it on the test database so my users can keep doing their thing.
Is this possible? I've been looking in the Help files as well as scanning
this newsgroup but have yet to discover the solution.
Thanks in advance for your help.
Just do a backup of your live database, and then restore it to your
server as a new name, and use the MOVE switch on the RESTORE....see
below
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is
restored to its original location. If the RESTORE statement is used to
copy a database to the same or different server, the MOVE option may be
needed to relocate the database files and to avoid collisions with
existing files. Each logical file in the database can be specified in
different MOVE statements.
from
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Or you could script out everything on your database and DTS all the
data over to new db.
|||Thanks for the prompt reply and link. I'll give that a try!
"unc27932@.yahoo.com" wrote:

> Just do a backup of your live database, and then restore it to your
> server as a new name, and use the MOVE switch on the RESTORE....see
> below
> MOVE 'logical_file_name' TO 'operating_system_file_name'
> Specifies that the given logical_file_name should be moved to
> operating_system_file_name. By default, the logical_file_name is
> restored to its original location. If the RESTORE statement is used to
> copy a database to the same or different server, the MOVE option may be
> needed to relocate the database files and to avoid collisions with
> existing files. Each logical file in the database can be specified in
> different MOVE statements.
> from
> http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
>
> Or you could script out everything on your database and DTS all the
> data over to new db.
>

Copy database for testing on same SQL Server?

I have a live and functioning database on my lone SQL Server 2000. I would
like to create a copy of this database on the same server for when my 3rd
party provider releases updates (SQL scripts) I don't have to immediat3ly
apply them to my live database. If the script causes a failure I want to
discover it on the test database so my users can keep doing their thing.
Is this possible? I've been looking in the Help files as well as scanning
this newsgroup but have yet to discover the solution.
Thanks in advance for your help.Just do a backup of your live database, and then restore it to your
server as a new name, and use the MOVE switch on the RESTORE....see
below
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is
restored to its original location. If the RESTORE statement is used to
copy a database to the same or different server, the MOVE option may be
needed to relocate the database files and to avoid collisions with
existing files. Each logical file in the database can be specified in
different MOVE statements.
from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp
Or you could script out everything on your database and DTS all the
data over to new db.|||Thanks for the prompt reply and link. I'll give that a try!
"unc27932@.yahoo.com" wrote:
> Just do a backup of your live database, and then restore it to your
> server as a new name, and use the MOVE switch on the RESTORE....see
> below
> MOVE 'logical_file_name' TO 'operating_system_file_name'
> Specifies that the given logical_file_name should be moved to
> operating_system_file_name. By default, the logical_file_name is
> restored to its original location. If the RESTORE statement is used to
> copy a database to the same or different server, the MOVE option may be
> needed to relocate the database files and to avoid collisions with
> existing files. Each logical file in the database can be specified in
> different MOVE statements.
> from
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp
>
> Or you could script out everything on your database and DTS all the
> data over to new db.
>

Copy database for testing on same SQL Server?

I have a live and functioning database on my lone SQL Server 2000. I would
like to create a copy of this database on the same server for when my 3rd
party provider releases updates (SQL scripts) I don't have to immediat3ly
apply them to my live database. If the script causes a failure I want to
discover it on the test database so my users can keep doing their thing.
Is this possible? I've been looking in the Help files as well as scanning
this newsgroup but have yet to discover the solution.
Thanks in advance for your help.Just do a backup of your live database, and then restore it to your
server as a new name, and use the MOVE switch on the RESTORE....see
below
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is
restored to its original location. If the RESTORE statement is used to
copy a database to the same or different server, the MOVE option may be
needed to relocate the database files and to avoid collisions with
existing files. Each logical file in the database can be specified in
different MOVE statements.
from
http://msdn.microsoft.com/library/d...br />
25rm.asp
Or you could script out everything on your database and DTS all the
data over to new db.|||Thanks for the prompt reply and link. I'll give that a try!
"unc27932@.yahoo.com" wrote:

> Just do a backup of your live database, and then restore it to your
> server as a new name, and use the MOVE switch on the RESTORE....see
> below
> MOVE 'logical_file_name' TO 'operating_system_file_name'
> Specifies that the given logical_file_name should be moved to
> operating_system_file_name. By default, the logical_file_name is
> restored to its original location. If the RESTORE statement is used to
> copy a database to the same or different server, the MOVE option may be
> needed to relocate the database files and to avoid collisions with
> existing files. Each logical file in the database can be specified in
> different MOVE statements.
> from
> http://msdn.microsoft.com/library/d... />
z_25rm.asp
>
> Or you could script out everything on your database and DTS all the
> data over to new db.
>

Copy Database

Hi all,
In our system, were using quite a primative method of copying one database
to another, but renamed (like a live version & a play version).
At the moment, we do a simple backup to a physcial file, and then restore it
using a different name.
The problem is that at some clients, they have a locked down server and the
SQL server doesn't have access to write any files anywhere, so this process
failes miserably :(
Is there any way (SQL Command) that I can make an identical copy of a
database, with a different name, without going via the file system?
Thanks :DData Transformation Sservices.
AMB
"-Ldwater" wrote:

> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and th
e
> SQL server doesn't have access to write any files anywhere, so this proces
s
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Yes, but I need an SQL command that I can pass so that it doesn't involve th
e
user needing to go into enterprise manager :P
"Alejandro Mesa" wrote:
> Data Transformation Sservices.
>
> AMB
> "-Ldwater" wrote:
>|||Copying the production database to a reporting database is typically a
planned maintenace operation. Allowing the users to fire this off whenever
they please could be a significant performance hit on the system.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Well its not really a reporting database as such, just a plain old copy of
the existing one.
The system is in place so that the user has the abiliy to have 2 systems.
The live system and then a play one. This process is simply to copy over th
e
current database situation of the live, and overwrite / create the play
version.
The performance side of things is something that we can live with, if the
users are aware of it.
"JT" wrote:

> Copying the production database to a reporting database is typically a
> planned maintenace operation. Allowing the users to fire this off whenever
> they please could be a significant performance hit on the system.
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> it
> the
> process
>
>|||Restoring a full backup is probably the easiest. See these for more
details:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
> the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>|||Without using the usual backup/restore or detach/attch methods of copying a
database, the other method would be to create empty tables structures and
query/insert from the production to the copy database. Also scripting over
SPs, views, etc. The easiest way to do this would be to create a DTS package
and save it for re-use. There is DOS based executable called DTSRUN for
something like that which can take a package name as a parameter. This could
be a shortcut on the user's desktop. However, they will need sufficient
permissions to do this.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>
whenever
database
restore
and|||Let's hope the users don't get the live version and "play" version .
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D

copy database

I am trying to create a copy of an existing sql database. I want to run a
live version and a test version so that when I am coding the changes to my
application I can use the test version without impacting the live version.
I have tried scripting and I can reproduce the database structure; however,
when I try to import data it gives errors that are very cryptic. I would
think that since the data is from the database that was scripted it would be
ok.
Any thoughts or suggestions to accomplish this seemingly easy task.
WBI would start with a full backup of the original and restore it under a
different name but here are your choices:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"WB" <none> wrote in message news:Ojb4lFX0DHA.3196@.TK2MSFTNGP11.phx.gbl...
> I am trying to create a copy of an existing sql database. I want to run a
> live version and a test version so that when I am coding the changes to my
> application I can use the test version without impacting the live version.
> I have tried scripting and I can reproduce the database structure;
however,
> when I try to import data it gives errors that are very cryptic. I would
> think that since the data is from the database that was scripted it would
be
> ok.
> Any thoughts or suggestions to accomplish this seemingly easy task.
> WB
>