August 4, 2009 at 7:59 am
I have created a basic Logshipping setup. Primary server runs a job which backups log files every minute. The secondary server copies and restores this log files. And i see the secondary database is in "..Restoring" mode always.
Now i made a small change in a table in primary database. And the backup, copy and restore jobs went fine. I want to see the changes in secondary database, but the db is always in "..Restoring" mode. What to do ?
August 4, 2009 at 8:44 am
Chandu (8/4/2009)
I have created a basic Logshipping setup. Primary server runs a job which backups log files every minute. The secondary server copies and restores this log files. And i see the secondary database is in "..Restoring" mode always.Now i made a small change in a table in primary database. And the backup, copy and restore jobs went fine. I want to see the changes in secondary database, but the db is always in "..Restoring" mode. What to do ?
You have to take a tail log backup from your primary server and restore it in secondary server with (RESTORE WITH RECOVERY) option.
doing so will bring your secondary database online and you will be able to view all of you primary server data into secondary server database.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 4, 2009 at 9:19 am
What is a tail log ? I believe all the logs (.trn files) are being copied over to secondary server instantly. Sorry if it is silly question, but i am new to DBA.
By the way, what does a .bak file contain and where can i use this file ?
August 4, 2009 at 9:48 am
Chandu (8/4/2009)
Please refer to following artical
http://msdn.microsoft.com/en-us/library/dd297499.aspx
By the way, what does a .bak file contain and where can i use this file ?
.bak file is you database backup file. also, for your information .trn is transaction log back file extension.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 4, 2009 at 10:08 am
Just know that if you recover your database, you will need to setup log shipping again. And if you backup the tail of log you will leave your production side (primary) in restoring state. This is how you should failover to your secondary if the primary is available.
If you just want to read data from the log shipped database but not interrupt anything, consider restoring to standby mode instead of no recovery mode leaving your secondary in read only in between restores.
August 4, 2009 at 10:17 am
Chandu (8/4/2009)
I have created a basic Logshipping setup. Primary server runs a job which backups log files every minute. The secondary server copies and restores this log files. And i see the secondary database is in "..Restoring" mode always.Now i made a small change in a table in primary database. And the backup, copy and restore jobs went fine. I want to see the changes in secondary database, but the db is always in "..Restoring" mode. What to do ?
This is because your log shipping is setup in NORECOVERY mode which means the secondary server will always expect log files from the primary. If you want to break the log shipping, you can put the secondary in RECOVERY mode by running the below command.
restore database yourDB with recovery
If you need to see the changes in the secondary, you need to setup log shipping in STANDBY mode. This way you'll get a read only secondary database and you will be able to connect to the secondary and query it. Whenever restore job starts, all connected users will be disconnected. Once the restoration gets over, you'll be able to query again.
August 4, 2009 at 10:23 am
Chandu (8/4/2009)
What is a tail log ? I believe all the logs (.trn files) are being copied over to secondary server instantly. Sorry if it is silly question, but i am new to DBA.Check out here on tail log backups
http://msdn.microsoft.com/en-us/library/ms179314.aspx
By the way, what does a .bak file contain and where can i use this file ?
Its a naming convention used by sql dbas to indicate that it is a backup file generally a full backup file. But using the extension is completely optional.For transaction log backups we use .trn file extension which is also optional.
[font="Verdana"]Thanks
Chandra Mohan[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply