 | |
Log Shipping: question about secondary dB mode?
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: Log Shipping: question about secondary dB mode? -
05-08-2008
, 01:02 PM
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#32
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#33
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#34
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#35
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#36
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
#37
| |||
| |||
|
|
Thanks for the concise and detailed answer Andrew! One last question...I tried to take a snapshot of the secondary dB using a query in SMS: CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data, FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT OF Northwind (where I replaced "northwind" with my dB name) But I receive an error: "Msg 1822, Level 16, State1, Line 1 The database must be online to have a database snapshot." "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uvqsvJRsIHA.5872 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is no need to shrink the log file. Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. The exception would be if you had a very long running open transaction that may prevent this until it is committed or rolled back. Taking a FULL backup will not break the log chain or affect log shipping. To access the secondary db you can create a database snapshot that will allow read only access to the data. If you need the data refreshed you can drop and recreate the snapshot. And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "geekyguy" <geeky (AT) guy (DOT) com> wrote in message news:ODgK4$QsIHA.3604 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hi: I set up tLog shipping between two sql2005 servers in a 2003 AD domain. Everything is working, but I have a couple questions. The primary dB is set to "full recovery". It's currently 5 gig with the log at 111 meg, set to grow by 100 MB increments. Although the dB is large with a lot of historical data, the actual transaction load is not very large. I have the transaction log backing up every 15 minutes, and the secondary dB is set to "no recovery mode". According to the logs, all the backup and restore jobs are proceeding without errors. The quarter-hourly transaction logs are quite small...generally less than 1 meg. Questions: Do I ever need to separately back up the log file and shrink it, if it's being backed up every 15 minutes? Or will it grow over time unless I do a separate backup/shrinkfile? I still need "snapshots" of the database in case we need to roll back for any reason...can I schedule a full db backup every 24 hours or will this conflict with the log-shipping? I had this scheduled in a maintenance plan, but when I set up tLog shipping it disabled the schedule for the full backup. With the database on the secondary in "no recovery" mode, it can't be accessed...it seems to be in permanent "restoring" mode...is that correct? If I wanted to be able to query it, would I need to change it to "standby" mode, and is it possible to change the mode from with the tLog shipping script? Or would I have to start over with "standy mode" selected for the secondary? Lastly, with the secondary in "no recovery" mode, in the event that I need to fail over to it, what do I need to do to get it out of "no recovery" mode? In SMS, I can't access it while it's in "no recovery" mode. Thanks in advance! |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.