none
restoring a database from a different database

    Question

  • I want to restore database new2db from newdb. both dbs are on different sql server instances.

    I have backup of newdb called newdb_july.bkup.

    I ran the below command :

    restore database new2db from disk = 'c:\mssql\backup\newdb_july.bkup' with file=1,
    move 'newdb' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\new2db.mdf',
    move 'newdb_log' to 'C:\Program Files\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\new2db_log.ldf', 
    replace,norecovery,stats=10

    the restore completed successfully but the database is still in 'Restoring' status.

    how do I get the database in active accessible status?

    appreciate the feedback.

    Wednesday, July 09, 2014 2:16 AM

Answers

  • Replace is used to restore the database even if there is an existing database with the same name. ie even if there was a database named new2db in that instance it would have restored the database using the backup you provided.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Chetan_DBA Wednesday, July 09, 2014 1:42 PM
    • Marked as answer by vtpcnk Thursday, July 10, 2014 1:10 AM
    Wednesday, July 09, 2014 12:52 PM
  • Yes norecovery if you want to restore more tlog or diff backup files... If you use recovery then you cant further restore any more tlog or diff backup's..

    • WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.

      If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops.

      If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.

    • WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by using the RECOVERY option.

    • With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, July 09, 2014 3:43 AM
  • please read this restore sequence details and what is the use and more...

    http://msdn.microsoft.com/en-us/library/ms186858.aspx


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by vtpcnk Thursday, July 10, 2014 1:10 AM
    Wednesday, July 09, 2014 3:45 AM

All replies

  • I ran the above restore command without "replace, norecovery" option and the database is now accessible.

    why do these two options place the database in 'Restoring' mode?


    • Edited by vtpcnk Wednesday, July 09, 2014 3:18 AM
    Wednesday, July 09, 2014 3:17 AM
  • ok got it. so it was 'norecovery' which was the issue.

    so norecovery option is to be used only when loading sequential transaction logs or while syncing up a mirror database?

    appreciate the feedback.

    Wednesday, July 09, 2014 3:30 AM
  • Yes norecovery if you want to restore more tlog or diff backup files... If you use recovery then you cant further restore any more tlog or diff backup's..

    • WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.

      If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops.

      If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.

    • WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by using the RECOVERY option.

    • With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, July 09, 2014 3:43 AM
  • please read this restore sequence details and what is the use and more...

    http://msdn.microsoft.com/en-us/library/ms186858.aspx


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by vtpcnk Thursday, July 10, 2014 1:10 AM
    Wednesday, July 09, 2014 3:45 AM
  • but then even without the REPLACE option I am able to restore a database from a database backup whose origin database has a different name as you can see in the restore command in my first post.
    • Edited by vtpcnk Wednesday, July 09, 2014 6:40 AM
    Wednesday, July 09, 2014 6:39 AM
  • Replace is used to restore the database even if there is an existing database with the same name. ie even if there was a database named new2db in that instance it would have restored the database using the backup you provided.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Chetan_DBA Wednesday, July 09, 2014 1:42 PM
    • Marked as answer by vtpcnk Thursday, July 10, 2014 1:10 AM
    Wednesday, July 09, 2014 12:52 PM
  • thanks all for the useful points.
    Thursday, July 10, 2014 1:10 AM