Restore Database suceeded but physical file location wrong

reinisbreinisb Posts: 17
edited November 20, 2012 8:19PM in SQL Backup Previous Versions
Hi all,

I restored a training database a couple of months ago from a redgate backup of our production database. I supplied the new file locations since the data and log needed to be on a different disk and the restore was fine.

We had no problems until a full server restart was required last week. I found out this morning that the database did not come back online after the restart because it was still looking for the data and log files in the original location, not the location I gave it in Redgate. I don't know how it was ok before the restart and not ok after the restart.

Has anyone else had a problem like this?

In the end it was a fairly simple fix but I don't like the fact that it happened in the first place.

Thanks,

Reinis
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the contents of the log file for the restore that you performed a couple of months back, if you still have it?

    SQL Backup generates a log file for each process that it runs. The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • reinisbreinisb Posts: 17
    Here it is. As far as I can see this was the last restore (which was not actually performed by me but still looks like it should have fixed the references in the DB).

    Is there anything obvious that looks like an issue? I don't see anything weird.

    Thanks,

    Reinis
    ____________________________________________________
    SQL Backup log file 6.5.1.9

    -SQL "RESTORE DATABASE [CRM_Training] FROM DISK = 'M:\DB_Backup\FULL_LAXSQLCRM_CRM_20120325_220001.sqb' WITH RECOVERY, MOVE 'CRM_DATA' TO 'M:\Data\CRM_Training\CRM_Training.mdf', MOVE 'CRM_DATA1' TO 'M:\Data\CRM_Training\CRM_Training_1.ndf', MOVE
    'CRM_DATA2' TO 'M:\Data\CRM_Training\CRM_Training_2.ndf', MOVE 'CRM_DATA3' TO 'M:\Data\CRM_Training\CRM_Training_3.ndf', MOVE 'CRM_log' TO 'M:\Log\CRM_Training\CRM_Training_log.ldf', REPLACE, ORPHAN_CHECK "
    ERRORS AND WARNINGS


    3/30/2012 3:28:58 PM: Restoring CRM_Training (database) on LAXSQLDEV instance from:
    3/30/2012 3:28:58 PM: M:\DB_Backup\FULL_LAXSQLCRM_CRM_20120325_220001.sqb

    3/30/2012 3:28:58 PM: RESTORE DATABASE [CRM_Training] FROM VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC01', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C1484
    41AC02', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC03', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC04', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC05', VIRTUAL_DEVICE =
    'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC06', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC07', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC08', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC0
    9', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC10', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC11', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC12', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-466
    1-90C3-B83C148441AC13', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC14', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC15', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC16', VIRTUAL_DEVICE =
    'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC17', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC18', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC19', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC2
    0', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC21', VIRTUAL_DEVICE = 'SQLBACKUP_55207C67-073A-4661-90C3-B83C148441AC22' WITH BUFFERCOUNT = 46, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY, MOVE 'CRM_DATA' TO
    'M:\Data\CRM_Training\CRM_Training.mdf', MOVE 'CRM_DATA1' TO 'M:\Data\CRM_Training\CRM_Training_1.ndf', MOVE 'CRM_DATA2' TO 'M:\Data\CRM_Training\CRM_Training_2.ndf', MOVE 'CRM_DATA3' TO 'M:\Data\CRM_Training\CRM_Training_3.ndf', MOVE 'CRM_log' TO
    'M:\Log\CRM_Training\CRM_Training_log.ldf', REPLACE

    3/30/2012 3:45:49 PM: Processed 1250208 pages for database 'CRM_Training', file 'CRM_DATA' on file 1.
    3/30/2012 3:45:49 PM: Processed 143176 pages for database 'CRM_Training', file 'CRM_DATA1' on file 1.
    3/30/2012 3:45:49 PM: Processed 828048 pages for database 'CRM_Training', file 'CRM_DATA2' on file 1.
    3/30/2012 3:45:49 PM: Processed 899592 pages for database 'CRM_Training', file 'CRM_DATA3' on file 1.
    3/30/2012 3:45:49 PM: Processed 8 pages for database 'CRM_Training', file 'CRM_log' on file 1.
    3/30/2012 3:45:49 PM: RESTORE DATABASE successfully processed 3121032 pages in 1006.300 seconds (24.230 MB/sec).
    3/30/2012 3:45:50 PM:
    3/30/2012 3:45:50 PM: Warning 472: Orphaned users: 4
    3/30/2012 3:45:50 PM:
    3/30/2012 3:45:50 PM: UserName UserSID
    3/30/2012 3:45:50 PM:
    3/30/2012 3:45:50 PM: ConsumerDataUser 0x64A08ED1BAAE6548AA4F2B242B82C825
    3/30/2012 3:45:50 PM: LaxSql3Link 0x96EDD85EB4523C448601170E4E89EC17
    3/30/2012 3:45:50 PM: report 0xD38633F319373644A73B6D1F9F1A7E9A
    3/30/2012 3:45:50 PM: sqllink 0x5766AD2AE8859548BFFEA6CF0B54EA44
    3/30/2012 3:45:50 PM:
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Everything seems to be in order. At the end of this restore, the database files should have been relocated to the new locations.

    In the SQL Server error log, were there any messages logged related to this restore? Could you post the errors logged in the error log when SQL Server attempted to bring the database online after the server restart?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.