|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy DatabaseHi 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 Data Transformation Sservices.
AMB Show quoteHide quote "-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 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 Yes, but I need an SQL command that I can pass so that it doesn't involve the
user needing to go into enterprise manager :P Show quoteHide quote "Alejandro Mesa" wrote: > Data 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 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 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. Show quoteHide quote "-Ldwater" <Ldwa***@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 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. Show quoteHide quote "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" <Ldwa***@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 > > > 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/features/mssql/article.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/scripts/scriptdetails.asp?scriptid=599 Restoring a .mdf http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles for SQL Server -- Show quoteHide quoteAndrew J. Kelly SQL MVP "-Ldwater" <Ldwa***@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: > >> 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" <Ldwa***@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 >> >> >> 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. Show quoteHide quote "-Ldwater" <Ldwa***@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: > > > 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" <Ldwa***@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 > > > > > > Let's hope the users don't get the live version and "play" version confused.
Show quoteHide quote "-Ldwater" <Ldwa***@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  
Other interesting topics
|
|||||||||||||||||||||||
