how to copy (running) instance RRS feed

  • Question

  • I need to get up on my computer with MSSQL Server 2005 SP2 an exact copy of running MS SQL Server 2005 SP1 instance (with system objs, accounts, etc.).

    In the same LAN.

    Well, not exact one but the one as close as possible...

    And what exactly may I  mimick without interfering to existing SS2005 in production?


    What is the best way to do it without invoking excessive (manual) work and problems? 

    I see dozens of ways and I am still in doubt...
    No ideal or straight-forward solution

    Friday, May 25, 2007 9:35 AM


All replies

  • You want to get an SP2 instance set up on your personal computer with the same configuration and databases as what exists on an SP1 (production) instance on another computer.  Is that correct?


    If so, how big are the databases and what edition of SQL Server 2005 are you running in production (SP1)?



    Friday, May 25, 2007 12:56 PM
  • It is correct
    Production Server is SS2005 SP1 on MS Windows 2003 Enterprise Ed.
    I want to get a copy of instance (for testing and development) on Windows XP SP2 with SS2005 SP2
    I would like to get it with maintenance plans, accounts, diagrams

    Appr. 500 MB but
    I do not think that the size is important since it is on high-speed  LAN


    The same collation, default language, protocols , services configured on my workstation SS2005 SP2 on XP SP2

    Sunday, May 27, 2007 3:12 AM
  • The 'best' way is to BACKUP the Production database(s) and RESTORE them on the SP2 instance.


    In order to have the Jobs, accounts, you will have to also BACKUP and RESTORE the msdb and master databases. Read these articles for more assistance.


    Moving Server/Database/Logins
    http://vyaskn.tripod.com/moving_sql_server.htm  Moving DBs
    http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving system dbs 2005
    http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
    http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
    http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp Moving Users
    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://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
    http://www.support.microsoft.com/?id=240872  Resolve Permission Issues -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=320125 Moving a Diagram
    http://www.sqlservercentral.com/scripts/contributions/1598.asp Script Roles and Permissions
    http://sqljunkies.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk Move Tables to New Files

    Sunday, May 27, 2007 4:44 AM
  • Arnie, thanks a lot,

    I believe I saw already most of these articles.


    I have either problems or additional issues to treat (or investigate) with online options.
    Backup gives an error and I just started to dig into it 

    What is the best way to get up a copy of instance if to stop production server?

    Sunday, May 27, 2007 5:26 AM
  • You are using the term 'instance' in a confusing manner. I think that you mean that you want a copy of a database.


    Normally, 'Instance' means a complete, functioning SQL Server.

    Sunday, May 27, 2007 6:57 AM
  • I mean instance - "complete, functioning SQL Server" with a few databases and all the rest.

    I want to have on my computer a staging or testing machine 

    Sunday, May 27, 2007 9:47 AM
  • You don't really 'copy' an instance.


    You install SQL Server on another computer, and then you may make copies of the databases on that additional server.


    The previous articles detail how to move and copy databases from one server to another.

    Sunday, May 27, 2007 2:50 PM
  • I'd stick with what Arnie's suggesting with those articles.  The only thing I would add is that you will probably be working with Developer Edition b/c you're running this on XP.

    Tuesday, May 29, 2007 12:52 AM
  • Running Developer Ed. is not must. It just happened that I first got a comp with preinstalled Windows XP SP2, hence then I installed SS20005 Developer Edition SP2 (with postSP2 hotfix) on it and only after that I started thinking how would I get a “copy” (of running SS2005 SP1 on Windows 2003).


    Really Copy Database Wizard seems very comfortable since it offers to copy with databases also:
    - Stored procedures from master database
    - SQL Server Agent jobs
    - User-defined error messages
    - Endpoints
    - SSIS packages
    - Logins
     ... Though it always fails for me in any combination if I run CDW between different machines.



     Arnie Rowland wrote:

    You don't really 'copy' an instance.


    You install SQL Server on another computer

    Suppose I make the binary image of hard drive, unplug the cable and start from it...

    Or even better copy it to virtual machine... 

     Bryan C. Smith wrote:

    I'd stick with what Arnie's suggesting with those articles. 

    Which one from them?

    The first one just spoiled the whole already configured instance The others require a lot of manual work and understanding of all internals of running instance.
    Those articles invoke more questions than answers. And a  lot of time... Just to start the whole series of posts "how to complicate the life of DBA and how to undo it"


    But the point is upside down:
    I need a copy in order to study it and test the changes before putting into production.

    Wednesday, May 30, 2007 1:59 AM
  • Gennady,

    Is this still an issue?

    Thank you!

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, February 3, 2014 6:08 AM