Red Gate forums :: View topic - Error trying to Restore DB & Move files with NORECOVERY
Return to www.red-gate.com RSS Feed Available

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

Error trying to Restore DB & Move files with NORECOVERY

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Fri Jan 11, 2013 5:02 pm    Post subject: Error trying to Restore DB & Move files with NORECOVERY Reply with quote

Hi, I am trying make a copy our Production DB from a backup & running into issues described below

Basically I am trying to

1. Restore the MyDB as the MyDB2 with NORECOVERY & also move/replace the data/log logical files

2. Restore the Log Files (backed up hourly) with NORECOVERY

3. Restore the MyDB2 with RECOVERY & finalize it



The issue we are running is that

1. We can restore the DB ONLY with REPLACE option, but that means we cannot restore the Logs incrementally as the db is allready recovered

2. Unable to restore the DB using the NORECOVERY option and get "The tail of the log for the database "PT" has not been backed up." error (not sure is there is something wrong with the backup OR its because we're trying to restore to a new DB (other than the one it was backed up))




Command to restore the db & restore the mdf/ldf files + move an extra file -

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MyDB2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\FULL_(local)_MyDB_20130110_000102.sqb' WITH NORECOVERY, MOVE N'MyDB_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.QA\MSSQL\Data\MyDB2.mdf', MOVE N'MyDB_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.QA\MSSQL\Data\MyDB2.ldf' "'

SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3159: SQL error 3159: The tail of the log for the database "MyDB2" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the
RESTORE statement to just overwrite the contents of the log.


On trying to restore the logs, I am getting

EXECUTE master..sqlbackup '-SQL "RESTORE LOG MyDB2 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\LOG_(local)_MyDB_20130109_180201.sqb' WITH NORECOVERY" '

SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
SQL error 3117: SQL error 3117: The log or differential backup cannot be restored because no files are ready to rollforward.

Any ideas?

(Disclaimer : I am not a DBA & have limited database knowledge)


Last edited by arvind_rao@hotmail.com on Fri Jan 11, 2013 6:46 pm; edited 1 time in total
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Jan 11, 2013 6:44 pm    Post subject: Reply with quote

Try replacing the MyDB2 rather than just restoring to it. As it exists there could be a taillog that would need to be offloaded first.

The other option is to take a log backup of MyDB2 before you try and replace it.

HTH

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Fri Jan 11, 2013 8:13 pm    Post subject: Reply with quote

Chris, Thank for your tip.

I had tried replacing the db earlier, using

Code:
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MyDB2] FROM DISK = N'... ' WITH REPLACE, MOVE N'MyDB_Data' TO N'...', MOVE N'MyDB_Log' TO N'...' "'

and although that ran fine, when I try to restore log files
Code:

RESTORE LOG MyDB2 FROM DISK = '...' WITH NORECOVERY

I get
Code:
The media family on device 'C:\....sqb' is incorrectly formed. SQL Server cannot process this media family.

I am not sure the issue is related so as I am trying to restore the log to a different DB (than original) OR cos the db is already closed/recovered as I used the REPLACE option

Any thoughts?
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Jan 11, 2013 8:15 pm    Post subject: Reply with quote

Try running a log backup on MyDB2 before you try the restore.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Fri Jan 11, 2013 10:10 pm    Post subject: Reply with quote

@#$% didnt work Sad

-SQL "BACKUP LOGS [MyDB2] TO DISK = N'C:\Temp\Log\<AUTO>' " [SQLSTATE 01000]

-SQL "RESTORE DATABASE [MyDB2] FROM DISK = N'....sqb' WITH MOVE N'MyDB_Data' TO N'...MyDB2.mdf', MOVE N'MyDB_Log' TO N'..MyDB2.ldf', , NORECOVERY " [SQLSTATE 01000]

RESTORE LOG MyDB2 FROM DISK = '....sqb' WITH NORECOVERY [SQLSTATE 01000]
Msg 3241, Sev 16, State 1, Line 1 : The media family on device '...sqb' is incorrectly formed. SQL Server cannot process this media family. [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 1 : RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Jan 11, 2013 10:23 pm    Post subject: Reply with quote

I hope that wasn't a typo but it should be BACKUP LOG not BACKUP LOGS.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Fri Jan 11, 2013 10:29 pm    Post subject: Reply with quote

hmm I did use LOGS as thats what the documentation said Wink

Code:
L. Back up transaction logs for multiple databases

This example creates transaction log backups for databases northwind and pubs in the default location.

SQLBackupC.exe -I {instance name} -SQL "BACKUP LOGS [northwind, pubs] TO DISK = '<AUTO>' "

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [northwind, pubs] TO DISK = ''<AUTO>'' " '


http://www.red-gate.com/supportcenter/Content/SQL_Backup/help/7.2/SBU_Toolkit_syntax_egs#o15223

will try with LOG now & see

Thanks for your help
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Jan 11, 2013 10:56 pm    Post subject: Reply with quote

That was for two databases in one execution.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Fri Jan 11, 2013 11:20 pm    Post subject: Reply with quote

No change, same error....

-SQL "BACKUP LOG [MyDB2] TO DISK = N'C:\Temp\Log\DB2_FULL.log' " [SQLSTATE 01000]
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Jan 11, 2013 11:32 pm    Post subject: Reply with quote

Just a thought.

If MyDB2 seems recovered then run DBCC CHECKDB with PHYSICAL_ONLY then try using the GUI to take a LOG backup to see that you have the permissions correct.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Sat Jan 12, 2013 12:08 am    Post subject: Reply with quote

DBCC ran fine and was able to backup the log..
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Sat Jan 12, 2013 12:11 am    Post subject: Reply with quote

Now try the restore again using the GUI.

These should all be running with the account that runs SQLBackup so it doesn't look like a permissions issue.

I am leaving now so I look forward to reading this thread on Monday.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
arvind_rao@hotmail.com



Joined: 11 Jan 2013
Posts: 7

PostPosted: Sun Jan 13, 2013 8:15 pm    Post subject: Reply with quote

Well figured it out, was trying to restore the .sqb file with a Restore function Wink

Ran it with "EXECUTE master..sqlbackup" & it works. Still had to backup the log before I tried to restore the db.

Chris thanks for your help.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Mon Jan 14, 2013 6:57 pm    Post subject: Reply with quote

Glad you managed to get it to work. Since SQL2005 if you have either Full or Bulk-Logged recovery model you need to run a Tail-log backup unless you use SQLBackup to force over an existing database.

Chris
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
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