Post reply

Copy Database from Dev instance to Prod Instance

  • July 19, 2007 at 7:28 am

    #153690

    We have SQL Server 2005 installed with two instances on one machine (development and production).

    I created a database on the dev instance and now I want to copy it to the prod instance.

    What are some ways to to this?

    Thanks!

  • July 19, 2007 at 7:32 am

    #720757

    If you don't want that database in dev machine the easiest way is to detch in dev insance and attach in prod. Else take a back and restore in prod instance.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 19, 2007 at 7:38 am

    #720764

    Thanks, Sugesh!

  • July 19, 2007 at 7:57 am

    #720778

    Need more help!

    Backed up the database on the dev instance without a problem.

    Loged in to the prod instance and attempted a restore by doing this:

    1. right click database node

    2. select "restore database..."

    3. entered "to database" name

    4. selected "from device" radio button and navigated to backup file location.

    5. click <ok>

    This resulted an a message box that said: "restore failed for...".  "The operating system returned '32 (The process cannot access the file because it is being used by another process.)'

    Any suggestions?   Remember, there is one install of SQL with two instances.

  • July 19, 2007 at 9:05 am

    #720814

    That means that you are restoring the files to a destination that already exists.  That would be a huge a warning to me that you were about to destroy a production database.  MAKE SURE YOU VERIFY the paths before doing it again, or you may need to re-verify you resume.

  • July 19, 2007 at 9:26 am

    #720820

    You MUST change the location of the database files during the restore.  By default it tries to use the path recorded in the backup and since your development and production "instances" are on the same machine you tried to overwrite the development database files (which are still in use) with the database you were restoring to the production instance.

    James.

  • July 19, 2007 at 9:33 am

    #720822

    Here are some commands that should help you accomplish what you want.  You should try to use "real" SQL commands rather than the wizards whenever possible. 

    Use Server Management Studio and run the following commands (altered to support your database name, path, and number of files, etc):

    --Backup the "development database" from the development instance

    backup database [DevDatabase] to DISK='d:\db_backups\DevDatabase.dmp' with format

    go

    --Change connections to the production instance

    --Use the "Restore filelistonly" to see what files are in the backup set

    RESTORE FILELISTONLY FROM DISK='d:\db_backups\DevDatabase.dmp'

    go

    --Now run a real restore, change the file paths of the physical database files.

    RESTORE DATABASE [DevDatabase]

    FROM DISK='d:\db_backups\DevDatabase.dmp'

    WITH

        REPLACE,

        MOVE 'DevDatabase_dat' TO 'd:\sqldata\DevDatabase.MDF',

        MOVE 'DevDatabase_log' TO 'd:\SQLData\DevDatabase.LDF'

    go

    --James.

  • July 19, 2007 at 11:49 am

    #720867

    Thanks, James.

    I will keep your reply for future reference.

    What I did before reading your solution was to export my database.  I right-clicked the database, then selected 'Tasks', then 'Export Data...'.

    Seemed to have worked.

    Again, thanks  very much for your help! 

Viewing 8 posts - 1 through 8 (of 8 total)

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