I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.
Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.
|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.
|||Brad
I see what you mean
http://www.sql-server-performance.co...g_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...[vbcol=seagreen]
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.
|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.co...g_shipping.asp
But I can't do real log shipping because I am on standard.
Showing posts with label reattach. Show all posts
Showing posts with label reattach. Show all posts
Sunday, March 11, 2012
Detaching and Attaching Standby Database
I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...[vbcol=seagreen]
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?[/vbco
l]
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.c...og_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?[vbcol=seagreen]
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.c...og_shipping.asp
But I can't do real log shipping because I am on standard.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...[vbcol=seagreen]
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?[/vbco
l]
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.c...og_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?[vbcol=seagreen]
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.c...og_shipping.asp
But I can't do real log shipping because I am on standard.
Detaching and Attaching Standby Database
I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> > I need to be able to detach my standby database so I can back it up.
> > How do I reattach it and keep it from going through a recover (thus
> > breaking my log consistency with the production database)?
> >
> > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > START MSSQLSERVER, but I'm not sure if that will work.
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
> >
> > "Brad" <brad@.seesigifthere.com> wrote in message
> > news:MPG.1b7bedee1a217f72989689@.news...
> > > I need to be able to detach my standby database so I can back it up.
> > > How do I reattach it and keep it from going through a recover (thus
> > > breaking my log consistency with the production database)?
> > >
> > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > START MSSQLSERVER, but I'm not sure if that will work.
> >
> > Have you read the article in the BOL
> > "How to set up, maintain, and bring online a standby server
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> > In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> > says...
> > >
> > > "Brad" <brad@.seesigifthere.com> wrote in message
> > > news:MPG.1b7bedee1a217f72989689@.news...
> > > > I need to be able to detach my standby database so I can back it up.
> > > > How do I reattach it and keep it from going through a recover (thus
> > > > breaking my log consistency with the production database)?
> > > >
> > > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > > START MSSQLSERVER, but I'm not sure if that will work.
> > >
> > > Have you read the article in the BOL
> > > "How to set up, maintain, and bring online a standby server
> (Transact-SQL)"?
> >
> > Yes, I've read that and I have all of that working. The problem is that
> > I really want to just keep restoring logs on this server, but I want to
> > have a local backup of the standby database (45GB production database is
> > on the west coast and I am on the east with only a T1 between the two).
> > Yesterday I had some kind of disk hiccup during a log restore and the
> > standby database was hosed so I have to go back to a 1.5 week old full
> > backup and a ton of log restores. If I could just automatically backup
> > (full) the standby database each night my restore issue would be much
> > smaller. I understand that I can't do an actual backup, but I should be
> > able to detach, copy and attach or something like that.
> I see what you mean
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
But I can't do real log shipping because I am on standard.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> > I need to be able to detach my standby database so I can back it up.
> > How do I reattach it and keep it from going through a recover (thus
> > breaking my log consistency with the production database)?
> >
> > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > START MSSQLSERVER, but I'm not sure if that will work.
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
> >
> > "Brad" <brad@.seesigifthere.com> wrote in message
> > news:MPG.1b7bedee1a217f72989689@.news...
> > > I need to be able to detach my standby database so I can back it up.
> > > How do I reattach it and keep it from going through a recover (thus
> > > breaking my log consistency with the production database)?
> > >
> > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > START MSSQLSERVER, but I'm not sure if that will work.
> >
> > Have you read the article in the BOL
> > "How to set up, maintain, and bring online a standby server
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> > In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> > says...
> > >
> > > "Brad" <brad@.seesigifthere.com> wrote in message
> > > news:MPG.1b7bedee1a217f72989689@.news...
> > > > I need to be able to detach my standby database so I can back it up.
> > > > How do I reattach it and keep it from going through a recover (thus
> > > > breaking my log consistency with the production database)?
> > > >
> > > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > > START MSSQLSERVER, but I'm not sure if that will work.
> > >
> > > Have you read the article in the BOL
> > > "How to set up, maintain, and bring online a standby server
> (Transact-SQL)"?
> >
> > Yes, I've read that and I have all of that working. The problem is that
> > I really want to just keep restoring logs on this server, but I want to
> > have a local backup of the standby database (45GB production database is
> > on the west coast and I am on the east with only a T1 between the two).
> > Yesterday I had some kind of disk hiccup during a log restore and the
> > standby database was hosed so I have to go back to a 1.5 week old full
> > backup and a ton of log restores. If I could just automatically backup
> > (full) the standby database each night my restore issue would be much
> > smaller. I understand that I can't do an actual backup, but I should be
> > able to detach, copy and attach or something like that.
> I see what you mean
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
But I can't do real log shipping because I am on standard.
Friday, March 9, 2012
Detach database greyed out
I'm trying to detach a database and reattach to another server. When trying to detach it using Enterprise Manager, the command is greyed out. I've tried taking the database offline first but the command is still not enabled. I'm logged in as sysadmin. What gives?Why not use SP_DETACHDB From query analyzer?
And its better to use such admin. statements using QA than depending upon GUI>|||Is it one on the default dbs you are trying to detach? If it is, I don't think you can! There are ways around it though(copy the data and log files to where you want the new db), not supported though I suspect.|||Originally posted by Satya
Why not use SP_DETACHDB From query analyzer?
And its better to use such admin. statements using QA than depending upon GUI>
Satya,
I've tried this (SQL 2000) and I get an error message:
Database is in use. I can't figure out how to get it "out of use" ?
The actual database is on SQL 7 though...
..no, it's not one of the system databases but thanks for the tip!|||Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.|||Originally posted by Satya
Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.
DBO use only...you mean single user mode? SQL 7 doesn't support dbo only or, at least I can't find how to do that.|||Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.|||Originally posted by Satya
Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.
OK, the database is set to DBO use only. I'm working with the 2000 version of the database since the attach/detach doesn't seem to be supported at all in 7. Anyway, even with the DBO use only set, the cmd is still greyed out.
And its better to use such admin. statements using QA than depending upon GUI>|||Is it one on the default dbs you are trying to detach? If it is, I don't think you can! There are ways around it though(copy the data and log files to where you want the new db), not supported though I suspect.|||Originally posted by Satya
Why not use SP_DETACHDB From query analyzer?
And its better to use such admin. statements using QA than depending upon GUI>
Satya,
I've tried this (SQL 2000) and I get an error message:
Database is in use. I can't figure out how to get it "out of use" ?
The actual database is on SQL 7 though...
..no, it's not one of the system databases but thanks for the tip!|||Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.|||Originally posted by Satya
Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.
DBO use only...you mean single user mode? SQL 7 doesn't support dbo only or, at least I can't find how to do that.|||Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.|||Originally posted by Satya
Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.
OK, the database is set to DBO use only. I'm working with the 2000 version of the database since the attach/detach doesn't seem to be supported at all in 7. Anyway, even with the DBO use only set, the cmd is still greyed out.
Subscribe to:
Posts (Atom)