September 2, 2009 at 6:24 am
I am trying to setup log shipping on a couple of SQL 2005 servers.
I have managed to get so far without a problem. I have configured the database for log shipping in the properties and the scheduled task runs every 15 minutes without a problem and it is marked as a success.
On the secondary server the database is showing up in the list but it is marked as still restoring with - (restoring...) appearing at the end of the database name and there is also a small green up arrow on the database.
How do i get rid of this and make the database operational?
Thanks in advance
September 2, 2009 at 6:29 am
You don't. For log shipping to run, the secondary database must be either RECOVERING or STANDBY. If the database is brought online further log backups cannot be restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2009 at 6:36 am
The database i am testing this on isn't in use so I can safely bring the secondary one online without having to worry about not using the first one.
I just need to test this out fully and ensure i know all the steps before i go and use this in our production environment.
What is the next stage? I have tried knocking the original one offline but that didnt work!
Apologies as I am a bit new to SQL administration!
thanks
September 2, 2009 at 6:48 am
RESTORE DATABASE < Dagtabase Name > WITH RECOVERY
Be aware, once you've done that the only way to restart log shipping is to start by restoring the full backup again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2009 at 8:14 am
Just tried this but i am getting an error:
RESTORE DATABASE databaseCMS WITH RECOVERY
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '60'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
September 2, 2009 at 8:38 am
Did you run just that? There's no '60' in that command that the incorrect syntax error refers to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2009 at 9:13 am
Yes just that
Do i need anything around the database name? brackets or anything similar?
September 2, 2009 at 9:14 am
ps thanks for the tips so far. Much appreciated!
September 2, 2009 at 9:27 am
fox1977 (9/2/2009)
Do i need anything around the database name? brackets or anything similar?
Only if the database name starts with a non-alpha character or has a space or other disallowed character in it. If what you posted was the real DB name then no, it needs no brackets.
I tested this to be sure. With a database named 'Testing' that's in the state recovering the following brings it online.
restore database testing with recovery
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2009 at 5:13 am
GilaMonster (9/2/2009)
You don't. For log shipping to run, the secondary database must be either RECOVERING or STANDBY. If the database is brought online further log backups cannot be restored.
You mean RESTORING or STANDBY.
Fox1977,
Rather than fully recovering the database (breaking log shipping), use RESTORE DATABASE [database] WITH STANDBY = '';
where is the full path and file name of a file SQL Server can create and write to, to store UNDO information.
This will allow read-only access to the database, and will allow log shipping to continue at a later time.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply