|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Backing Up Mirrored databaseI'm looking for some suggestions & strategies I should take when backing up a
mirrored database. Ideally I just want to backup the principal, but how do I create a maintenance plan that is aware of the failover partner? It is not clear to me what you mean by this. Why would the backup plan need
to be aware of the mirrored server? -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "BigSam" <Big***@discussions.microsoft.com> wrote in message news:09BDBDB1-2199-4F00-8DB0-523D79497F65@microsoft.com... > I'm looking for some suggestions & strategies I should take when backing > up a > mirrored database. > Ideally I just want to backup the principal, but how do I create a > maintenance plan that is aware of the failover partner? When I create a backup plan I don't know if the database is the principal or
the mirror on the server. I assume I need to create a backup plan on each server for the mirrored database. I could live with that, but I don't want to copy 2 versions of the database backups to my offsite storage. Show quote "Andrew J. Kelly" wrote: > It is not clear to me what you mean by this. Why would the backup plan need > to be aware of the mirrored server? > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "BigSam" <Big***@discussions.microsoft.com> wrote in message > news:09BDBDB1-2199-4F00-8DB0-523D79497F65@microsoft.com... > > I'm looking for some suggestions & strategies I should take when backing > > up a > > mirrored database. > > Ideally I just want to backup the principal, but how do I create a > > maintenance plan that is aware of the failover partner? > > You can't backup a mirrored database. If you try, you will get an error message. If you want your
agent job to take this into account, you can in your job query the sys.database_mirroring catalog view to see whether "you" are principal or mirror. If you are mirror, you can just exit without attempting to do backup. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "BigSam" <Big***@discussions.microsoft.com> wrote in message news:D4F05338-82D7-4C13-8DAB-9D31EE5A234E@microsoft.com... > When I create a backup plan I don't know if the database is the principal or > the mirror on the server. I assume I need to create a backup plan on each > server for the mirrored database. I could live with that, but I don't want to > copy 2 versions of the database backups to my offsite storage. > > "Andrew J. Kelly" wrote: > >> It is not clear to me what you mean by this. Why would the backup plan need >> to be aware of the mirrored server? >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "BigSam" <Big***@discussions.microsoft.com> wrote in message >> news:09BDBDB1-2199-4F00-8DB0-523D79497F65@microsoft.com... >> > I'm looking for some suggestions & strategies I should take when backing >> > up a >> > mirrored database. >> > Ideally I just want to backup the principal, but how do I create a >> > maintenance plan that is aware of the failover partner? >> >> What Tibor said, just adding two cents.
I have backup job on both SQL Servers (principle and mirror) set to backup all databases; and both run successfully without any issues. SQL Server is smart enough to ignored mirrored databases if the job is on mirrored partner because databases are in Restoring mode. So I don't have to do any extra work when partner fails over. But it does backup the system databases, without any issues. -- Show quoteMohit K. Gupta B.Sc. CS, Minor Japanese MCTS: SQL Server 2005 "BigSam" wrote: > When I create a backup plan I don't know if the database is the principal or > the mirror on the server. I assume I need to create a backup plan on each > server for the mirrored database. I could live with that, but I don't want to > copy 2 versions of the database backups to my offsite storage. > > "Andrew J. Kelly" wrote: > > > It is not clear to me what you mean by this. Why would the backup plan need > > to be aware of the mirrored server? > > > > -- > > Andrew J. Kelly SQL MVP > > Solid Quality Mentors > > > > > > "BigSam" <Big***@discussions.microsoft.com> wrote in message > > news:09BDBDB1-2199-4F00-8DB0-523D79497F65@microsoft.com... > > > I'm looking for some suggestions & strategies I should take when backing > > > up a > > > mirrored database. > > > Ideally I just want to backup the principal, but how do I create a > > > maintenance plan that is aware of the failover partner? > > > > > I have backup job on both SQL Servers (principle and mirror) set to backup Interesting... When you say "ignoring", do you mean> all databases; and both run successfully without any issues. SQL Server is > smart enough to ignored mirrored databases if the job is on mirrored partner > because databases are in Restoring mode. "Attempt to backup but receives an error message from the engine so the job fails"? or "Doesn't attempt to backup and hence nor error message"? I guess the later, since you say "run successfully". I do know that if you try BACKUP DATABASE on a mirrored database, you do get an error message. This is why I'm curious. Are you using Maintenance Plans? If so, perhaps the MPs are smart enough to not attempt backing up a mirrored database? -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Mohit K. Gupta" <mohitkgu***@msn.com> wrote in message news:28254A7C-F73B-4D65-A633-EA19EA01A2DF@microsoft.com... > What Tibor said, just adding two cents. > > I have backup job on both SQL Servers (principle and mirror) set to backup > all databases; and both run successfully without any issues. SQL Server is > smart enough to ignored mirrored databases if the job is on mirrored partner > because databases are in Restoring mode. So I don't have to do any extra > work when partner fails over. But it does backup the system databases, > without any issues. > > -- > Mohit K. Gupta > B.Sc. CS, Minor Japanese > MCTS: SQL Server 2005 > > > "BigSam" wrote: > >> When I create a backup plan I don't know if the database is the principal or >> the mirror on the server. I assume I need to create a backup plan on each >> server for the mirrored database. I could live with that, but I don't want to >> copy 2 versions of the database backups to my offsite storage. >> >> "Andrew J. Kelly" wrote: >> >> > It is not clear to me what you mean by this. Why would the backup plan need >> > to be aware of the mirrored server? >> > >> > -- >> > Andrew J. Kelly SQL MVP >> > Solid Quality Mentors >> > >> > >> > "BigSam" <Big***@discussions.microsoft.com> wrote in message >> > news:09BDBDB1-2199-4F00-8DB0-523D79497F65@microsoft.com... >> > > I'm looking for some suggestions & strategies I should take when backing >> > > up a >> > > mirrored database. >> > > Ideally I just want to backup the principal, but how do I create a >> > > maintenance plan that is aware of the failover partner? >> > >> > Hi Tibor,
Correct I meant the later, it doesn't attempt to backup and hence no error message, I am using MPs, and looks like they are smart enough to ignore the partner databases. Thanks! -- Show quoteMohit K. Gupta B.Sc. CS, Minor Japanese MCTS: SQL Server 2005 "Tibor Karaszi" wrote: > > I have backup job on both SQL Servers (principle and mirror) set to backup > > all databases; and both run successfully without any issues. SQL Server is > > smart enough to ignored mirrored databases if the job is on mirrored partner > > because databases are in Restoring mode. > > Interesting... When you say "ignoring", do you mean > "Attempt to backup but receives an error message from the engine so the job fails"? > or > "Doesn't attempt to backup and hence nor error message"? > > I guess the later, since you say "run successfully". I do know that if you try BACKUP DATABASE on a > mirrored database, you do get an error message. This is why I'm curious. Are you using Maintenance > Plans? If so, perhaps the MPs are smart enough to not attempt backing up a mirrored database? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > Interesting...
Thanks Mohit! :-) -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Mohit K. Gupta" <mohitkgu***@msn.com> wrote in message news:7621F481-9F20-4B88-88D6-63793B08F097@microsoft.com... > Hi Tibor, > > Correct I meant the later, it doesn't attempt to backup and hence no > error message, I am using MPs, and looks like they are smart enough to ignore > the partner databases. Thanks! > > -- > Mohit K. Gupta > B.Sc. CS, Minor Japanese > MCTS: SQL Server 2005 > > > "Tibor Karaszi" wrote: > >> > I have backup job on both SQL Servers (principle and mirror) set to backup >> > all databases; and both run successfully without any issues. SQL Server is >> > smart enough to ignored mirrored databases if the job is on mirrored partner >> > because databases are in Restoring mode. >> >> Interesting... When you say "ignoring", do you mean >> "Attempt to backup but receives an error message from the engine so the job fails"? >> or >> "Doesn't attempt to backup and hence nor error message"? >> >> I guess the later, since you say "run successfully". I do know that if you try BACKUP DATABASE on >> a >> mirrored database, you do get an error message. This is why I'm curious. Are you using >> Maintenance >> Plans? If so, perhaps the MPs are smart enough to not attempt backing up a mirrored database? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> Take a look at this script:
http://www.sqlcommunity.com/Default.aspx?grm2id=47&tabid=56 It will ignore any mirrored databases. (DBMaintplan in SQL Server 2005 SP1 would ignore mirror databases but SP2 will throw an error) -- Show quoteThank you, Saleem Hakani HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community) SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL Server Fun. "BigSam" wrote: > I'm looking for some suggestions & strategies I should take when backing up a > mirrored database. > Ideally I just want to backup the principal, but how do I create a > maintenance plan that is aware of the failover partner?  |
|||||||||||||||||||||||
Other interesting topics