|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Backup multi databaseI have two db in my SQL Server 2005 engine. DB1 and DB2.
I have a transaction that work over the two DB. If i start backup of DB1 and DB2 during the transaction, is possible have this scenario: on DB1 the transaction is not committed and when start backup DB2 the transaction is committed. Each DB is consistent and ok. But i've got only a piece of the transaction on DB2. Is possible to syncronize the two backup to avoid this situation to obtain a consistent "server" backup (all DBs)? Thanks! Start a transaction with a mark name (see Books Online for BEGAIN
TRAN), then inside it do log backups. This allow you to restore log backups to that named transaction (RESTORE LOG ... STOPATMARK). -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "checcouno" <checco***@discussions.microsoft.com> wrote in message news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... >I have two db in my SQL Server 2005 engine. DB1 and DB2. > I have a transaction that work over the two DB. > If i start backup of DB1 and DB2 during the transaction, is possible > have > this scenario: on DB1 the transaction is not committed and when > start backup > DB2 the transaction is committed. > Each DB is consistent and ok. But i've got only a piece of the > transaction > on DB2. > Is possible to syncronize the two backup to avoid this situation to > obtain a > consistent "server" backup (all DBs)? > > Thanks! > And for backup FULL?
Show quoteHide quote "Tibor Karaszi" wrote: > Start a transaction with a mark name (see Books Online for BEGAIN > TRAN), then inside it do log backups. This allow you to restore log > backups to that named transaction (RESTORE LOG ... STOPATMARK). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "checcouno" <checco***@discussions.microsoft.com> wrote in message > news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... > >I have two db in my SQL Server 2005 engine. DB1 and DB2. > > I have a transaction that work over the two DB. > > If i start backup of DB1 and DB2 during the transaction, is possible > > have > > this scenario: on DB1 the transaction is not committed and when > > start backup > > DB2 the transaction is committed. > > Each DB is consistent and ok. But i've got only a piece of the > > transaction > > on DB2. > > Is possible to syncronize the two backup to avoid this situation to > > obtain a > > consistent "server" backup (all DBs)? > > > > Thanks! > > > > > Your log backups need to be complemented with a full backups, as a
starting point. You can't get a consistent timestamp view over several databases with only full backups. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "checcouno" <checco***@discussions.microsoft.com> wrote in message news:BDF3A3AB-66C4-4686-9C83-5641B30AFCD7@microsoft.com... > And for backup FULL? > > > > "Tibor Karaszi" wrote: > >> Start a transaction with a mark name (see Books Online for BEGAIN >> TRAN), then inside it do log backups. This allow you to restore log >> backups to that named transaction (RESTORE LOG ... STOPATMARK). >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "checcouno" <checco***@discussions.microsoft.com> wrote in message >> news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... >> >I have two db in my SQL Server 2005 engine. DB1 and DB2. >> > I have a transaction that work over the two DB. >> > If i start backup of DB1 and DB2 during the transaction, is >> > possible >> > have >> > this scenario: on DB1 the transaction is not committed and when >> > start backup >> > DB2 the transaction is committed. >> > Each DB is consistent and ok. But i've got only a piece of the >> > transaction >> > on DB2. >> > Is possible to syncronize the two backup to avoid this situation >> > to >> > obtain a >> > consistent "server" backup (all DBs)? >> > >> > Thanks! >> > >> >> >> I think there can still be sync problems with this. For example,
Connection 1 starts a transaction in DB1, then connection 2 starts a transaction and updates both DB1 and DB2 and commit the transaction, then the connection doing the log backup starts the Mark transaction and does the log backup, finally connection 1 commits its transaction. Sometime later the DB1 and DB2 are restored to the transaction mark. The transaction from connection 2 will be in DB2 since it occurred before the mark. However, DB1 will be rolled back to the time that Connection 1 started its transaction since it was still in transaction state when the database was backed up, thus the transaction from connection 2 will not be in DB1. Using STOPATMARK only guarentees that nothing done after the mark transaction will be in the database. It can't guarentee that everything done before the mark will be there. Tom Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:%23b048tPYJHA.4596@TK2MSFTNGP06.phx.gbl... > Start a transaction with a mark name (see Books Online for BEGAIN TRAN), > then inside it do log backups. This allow you to restore log backups to > that named transaction (RESTORE LOG ... STOPATMARK). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "checcouno" <checco***@discussions.microsoft.com> wrote in message > news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... >>I have two db in my SQL Server 2005 engine. DB1 and DB2. >> I have a transaction that work over the two DB. >> If i start backup of DB1 and DB2 during the transaction, is possible have >> this scenario: on DB1 the transaction is not committed and when start >> backup >> DB2 the transaction is committed. >> Each DB is consistent and ok. But i've got only a piece of the >> transaction >> on DB2. >> Is possible to syncronize the two backup to avoid this situation to >> obtain a >> consistent "server" backup (all DBs)? >> >> Thanks! >> > > I can't understand why "DB1 will be rolled back to the time that Connection 1
started its transaction". Connection 2 committed the transaction before the MARK, so i expect to have transaction of connectino two in both DBs. Thanks. Show quoteHide quote "Tom Cooper" wrote: > I think there can still be sync problems with this. For example, > > Connection 1 starts a transaction in DB1, > then connection 2 starts a transaction and updates both DB1 and DB2 and > commit the transaction, > then the connection doing the log backup starts the Mark transaction and > does the log backup, > finally connection 1 commits its transaction. > > Sometime later the DB1 and DB2 are restored to the transaction mark. The > transaction from connection 2 will be in DB2 since it occurred before the > mark. However, DB1 will be rolled back to the time that Connection 1 > started its transaction since it was still in transaction state when the > database was backed up, thus the transaction from connection 2 will not be > in DB1. > > Using STOPATMARK only guarentees that nothing done after the mark > transaction will be in the database. It can't guarentee that everything > done before the mark will be there. > > Tom > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in > message news:%23b048tPYJHA.4596@TK2MSFTNGP06.phx.gbl... > > Start a transaction with a mark name (see Books Online for BEGAIN TRAN), > > then inside it do log backups. This allow you to restore log backups to > > that named transaction (RESTORE LOG ... STOPATMARK). > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://sqlblog.com/blogs/tibor_karaszi > > > > > > "checcouno" <checco***@discussions.microsoft.com> wrote in message > > news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... > >>I have two db in my SQL Server 2005 engine. DB1 and DB2. > >> I have a transaction that work over the two DB. > >> If i start backup of DB1 and DB2 during the transaction, is possible have > >> this scenario: on DB1 the transaction is not committed and when start > >> backup > >> DB2 the transaction is committed. > >> Each DB is consistent and ok. But i've got only a piece of the > >> transaction > >> on DB2. > >> Is possible to syncronize the two backup to avoid this situation to > >> obtain a > >> consistent "server" backup (all DBs)? > >> > >> Thanks! > >> > > > > > > > You are correct. I shouldn't try to answer these before I have my second
cup of coffee. I do, however, have a more fundamental objection to Tibor's method. SQL will not allow you to do a backup (either database or log) while the connection doing the backup is in transaction state. See SQL 2005 BOL at http://technet.microsoft.com/en-us/library/ms186865(SQL.90).aspx and SQL 2008 BOL at http://technet.microsoft.com/en-us/library/ms186865.aspx In both of them scroll down to the "Remarks" section. This restriction also exists in SQL 2000. I can't find it documented anywherein the SQL 2000 BOL, but if you try it, you will get error #3021 "Cannot perform a backup or restore operation within a transaction." Tom Show quoteHide quote "checcouno" <checco***@discussions.microsoft.com> wrote in message news:85627877-936A-402F-A3D8-3439C12BCC65@microsoft.com... >I can't understand why "DB1 will be rolled back to the time that Connection >1 > started its transaction". Connection 2 committed the transaction before > the > MARK, so i expect to have transaction of connectino two in both DBs. > > Thanks. > > > "Tom Cooper" wrote: > >> I think there can still be sync problems with this. For example, >> >> Connection 1 starts a transaction in DB1, >> then connection 2 starts a transaction and updates both DB1 and DB2 and >> commit the transaction, >> then the connection doing the log backup starts the Mark transaction and >> does the log backup, >> finally connection 1 commits its transaction. >> >> Sometime later the DB1 and DB2 are restored to the transaction mark. The >> transaction from connection 2 will be in DB2 since it occurred before the >> mark. However, DB1 will be rolled back to the time that Connection 1 >> started its transaction since it was still in transaction state when the >> database was backed up, thus the transaction from connection 2 will not >> be >> in DB1. >> >> Using STOPATMARK only guarentees that nothing done after the mark >> transaction will be in the database. It can't guarentee that everything >> done before the mark will be there. >> >> Tom >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in >> message news:%23b048tPYJHA.4596@TK2MSFTNGP06.phx.gbl... >> > Start a transaction with a mark name (see Books Online for BEGAIN >> > TRAN), >> > then inside it do log backups. This allow you to restore log backups to >> > that named transaction (RESTORE LOG ... STOPATMARK). >> > >> > -- >> > Tibor Karaszi, SQL Server MVP >> > http://www.karaszi.com/sqlserver/default.asp >> > http://sqlblog.com/blogs/tibor_karaszi >> > >> > >> > "checcouno" <checco***@discussions.microsoft.com> wrote in message >> > news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... >> >>I have two db in my SQL Server 2005 engine. DB1 and DB2. >> >> I have a transaction that work over the two DB. >> >> If i start backup of DB1 and DB2 during the transaction, is possible >> >> have >> >> this scenario: on DB1 the transaction is not committed and when start >> >> backup >> >> DB2 the transaction is committed. >> >> Each DB is consistent and ok. But i've got only a piece of the >> >> transaction >> >> on DB2. >> >> Is possible to syncronize the two backup to avoid this situation to >> >> obtain a >> >> consistent "server" backup (all DBs)? >> >> >> >> Thanks! >> >> >> > >> > >> >> >> My bad. You don't do the backup in the transactions, but you do need
the transactions marks for restore purposes. Below BOL 2008 entry has some info on this scenario: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/50a73574-1a69-448e-83dd-9abcc7cb7e1a.htm "Typical Scenario for Using Marked Transactions A typical scenario for using marked transactions includes the following steps: Create a full or differential database backup of each of the related databases. Mark a transaction block in all the databases. Back up the transaction log for all the databases. Restore database backups WITH NORECOVERY. Restore logs WITH STOPATMARK." -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:OXV5SqUYJHA.5108@TK2MSFTNGP05.phx.gbl... > You are correct. I shouldn't try to answer these before I have my > second cup of coffee. I do, however, have a more fundamental > objection to Tibor's method. SQL will not allow you to do a backup > (either database or log) while the connection doing the backup is in > transaction state. See SQL 2005 BOL at > http://technet.microsoft.com/en-us/library/ms186865(SQL.90).aspx > and SQL 2008 BOL at > http://technet.microsoft.com/en-us/library/ms186865.aspx > > In both of them scroll down to the "Remarks" section. This > restriction also exists in SQL 2000. I can't find it documented > anywherein the SQL 2000 BOL, but if you try it, you will get error > #3021 "Cannot perform a backup or restore operation within a > transaction." > > Tom > > "checcouno" <checco***@discussions.microsoft.com> wrote in message > news:85627877-936A-402F-A3D8-3439C12BCC65@microsoft.com... >>I can't understand why "DB1 will be rolled back to the time that >>Connection 1 >> started its transaction". Connection 2 committed the transaction >> before the >> MARK, so i expect to have transaction of connectino two in both >> DBs. >> >> Thanks. >> >> >> "Tom Cooper" wrote: >> >>> I think there can still be sync problems with this. For example, >>> >>> Connection 1 starts a transaction in DB1, >>> then connection 2 starts a transaction and updates both DB1 and >>> DB2 and >>> commit the transaction, >>> then the connection doing the log backup starts the Mark >>> transaction and >>> does the log backup, >>> finally connection 1 commits its transaction. >>> >>> Sometime later the DB1 and DB2 are restored to the transaction >>> mark. The >>> transaction from connection 2 will be in DB2 since it occurred >>> before the >>> mark. However, DB1 will be rolled back to the time that >>> Connection 1 >>> started its transaction since it was still in transaction state >>> when the >>> database was backed up, thus the transaction from connection 2 >>> will not be >>> in DB1. >>> >>> Using STOPATMARK only guarentees that nothing done after the mark >>> transaction will be in the database. It can't guarentee that >>> everything >>> done before the mark will be there. >>> >>> Tom >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >>> wrote in >>> message news:%23b048tPYJHA.4596@TK2MSFTNGP06.phx.gbl... >>> > Start a transaction with a mark name (see Books Online for >>> > BEGAIN TRAN), >>> > then inside it do log backups. This allow you to restore log >>> > backups to >>> > that named transaction (RESTORE LOG ... STOPATMARK). >>> > >>> > -- >>> > Tibor Karaszi, SQL Server MVP >>> > http://www.karaszi.com/sqlserver/default.asp >>> > http://sqlblog.com/blogs/tibor_karaszi >>> > >>> > >>> > "checcouno" <checco***@discussions.microsoft.com> wrote in >>> > message >>> > news:607C5072-F480-45B2-B18E-EE3DA3E51195@microsoft.com... >>> >>I have two db in my SQL Server 2005 engine. DB1 and DB2. >>> >> I have a transaction that work over the two DB. >>> >> If i start backup of DB1 and DB2 during the transaction, is >>> >> possible have >>> >> this scenario: on DB1 the transaction is not committed and when >>> >> start >>> >> backup >>> >> DB2 the transaction is committed. >>> >> Each DB is consistent and ok. But i've got only a piece of the >>> >> transaction >>> >> on DB2. >>> >> Is possible to syncronize the two backup to avoid this >>> >> situation to >>> >> obtain a >>> >> consistent "server" backup (all DBs)? >>> >> >>> >> Thanks! >>> >> >>> > >>> > >>> >>> >>> > >  
Other interesting topics
bak file = 5gb, attempted restore claims its 100Gb
Transactional data fun... SQL Server 2005 Performance Large table maintenance using DATEADD to report informaiton from 60 days ago Performance Dashboard giving incorrect missing index SQL 2005 Management Studio truncate log permission question how to query time |
|||||||||||||||||||||||
