Red Gate forums :: View topic - Restore Database suceeded but physical file location wrong
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Restore Database suceeded but physical file location wrong

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
reinisb



Joined: 05 Jul 2012
Posts: 13

PostPosted: Mon Nov 19, 2012 7:28 pm    Post subject: Restore Database suceeded but physical file location wrong Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Tue Nov 20, 2012 6:42 am    Post subject: Reply with quote

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 7
Back to top
View user's profile Send private message Send e-mail
reinisb



Joined: 05 Jul 2012
Posts: 13

PostPosted: Tue Nov 20, 2012 4:43 pm    Post subject: Reply with quote

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:
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Nov 21, 2012 2:19 am    Post subject: Reply with quote

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 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group