Post reply

Best Practice - Copy Database

  • January 27, 2004 at 4:59 pm

    #82147

    Wondering what the best practice or best approach would be to solving a problem.  We are currently migrating from IIS to Sun Java Web Server.  This is being done to address various security flaws in IIS.  So the plan is to move all the applications and database to a development / test environment and run the applications through their paces on the new web server.

    I have a bit of problem copying the databases from a production environment to a new development environment.  The dev environment is brand new and I need to copy all the tables, stored procedures, logins, & dts packages.

    Any suggestions that would get me going?

    Thanks!

    Jeff

  • January 27, 2004 at 5:39 pm

    #491843

    If the production DB can be brought offline, make a copy of them on the test server and sp_attach them. If not, Backup, restore is a fast operation also. You may want to have Master, msdb, databse as part of this "copy". That will get you other DB objects also like Jobs, Logins etc. Otherwise you'll just get tables, stored procedures, views, indexes etc belonging to the individual DB. Sorry, I do not know right off hand where DTS stuff is stored??? I do know, if you move the entire set of DBs to a differently named server, you'll have some manual adjustments to make on master, msdb database. Nothing to bad, search this site for "sp_AddServer", sp_DropServer, and "Job", you'll find it. This is a pretty "off the top of my head" reply, but getting backups or copies of the DBs to the new box is the 1st and maybe timeconsuming step.



    Once you understand the BITs, all the pieces come together

  • January 28, 2004 at 12:01 am

    #491876

    As Thomas stated I would use backup/restore to move the database. For the dts packages (I think they are actually stored in the msdb database) I would manually open them in the designer and then simply do a save as to the new server.

    As for the logins I think I would script them out and just run the script on the new server.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 28, 2004 at 7:21 am

    #491939

    For the initial job, I would follow the excellent advice above.  This would also get the dts packages -- the only other way I know without writing code is to save them outside of sql and then import them into the new box (this is very tedious if you have more than a few dts packages).

    However, I have found that I never have to copy the data only once and bringing the database down in production is sometimes difficult.  The easiest way I have found is to use one of the DTS functions for this.  The transfer databases task is very good, but more importantly is the Copy SQL Server Objects task.  This allows me to setup a list of objects that I want copied, providing a more customized method of copying -- as in refresh the Test environment.


    Joe Johnson
    NETDIO,LLC.

  • January 28, 2004 at 10:13 am

    #492019

    I like to use the dts copy objects task as well.  However, I first script the logins using "sp_helprevlogins" - see "Migrating Logins To Another Server" on this site.  This is a good file to save just in case of disaster - it has the passwords for sql server logins. 

    I then use EM "Generate SQL Script..." to script the users and roles of the database. 

    After creating the new database, adding logins, and roles (no users at this time), the dts copy objects can be used to move all objects WITHOUT data.  Do not elect to copy logins, users, or roles in the task.   Select move object permissions.  If the copy works, then great.  Otherwise, fix coding problems, add linked servers, move user permissions to roles, etc.  You might be amazed at how much can be caught by this.  If you start with copying the data, it might be a very long time before you see an error and have start over. 

    Now the users can be added - they do not have object permission at this time.  Note: If you are not role base - yuck - then you can elect to move the users with the logins and roles just after the db is created. 

    The final step is to elect to copy all objects and data (use the append option).  You might get some FK violations.  If the data has to remain, the FKs can be scripted and updated with a "WITH NOCHECK" to allow them be created despite the data. 

     

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply