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
