I know i can recover a standby database by restoring the same with recovery
option
I want to try out something and want to know which system table all this
gets recorded so that instead of restoring the database with recovery
option, can I just update a column in a system table to have it recovered. I
tried to change the status column in sysdatabases but that didnt seem to do
it. It just took the status away from within EM that states its a read only
to have nothing displayed and make it look like its available but upon
modifying tables it still gave me an error message that the database is in
load state or something of that sort.
Does anyone know what else I need to update ?You don't need to execute the enture restore again to change the status.
You can issue a RESTORE from Query Analyzer with only the RECOVERY option
(omitting the FROM clause).
RESTORE DATABASE MyDatabase
WITH RECOVERY
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
>I know i can recover a standby database by restoring the same with recovery
> option
> I want to try out something and want to know which system table all this
> gets recorded so that instead of restoring the database with recovery
> option, can I just update a column in a system table to have it recovered.
> I
> tried to change the status column in sysdatabases but that didnt seem to
> do
> it. It just took the status away from within EM that states its a read
> only
> to have nothing displayed and make it look like its available but upon
> modifying tables it still gave me an error message that the database is in
> load state or something of that sort.
> Does anyone know what else I need to update ?
>|||Yes I am aware of this.. Just wanted to know if theres any other way by
internally hacking a system table that would make the database available..
i.e. assuming I do not wish to change anything on this standby database. I
would like to make this database available by updating some system table
values and then putting it back in standby state by resetting that system
table value.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23EwGLRe%23EHA.2032@.tk2msftngp13.phx.gbl...
> You don't need to execute the enture restore again to change the status.
> You can issue a RESTORE from Query Analyzer with only the RECOVERY option
> (omitting the FROM clause).
> RESTORE DATABASE MyDatabase
> WITH RECOVERY
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> >I know i can recover a standby database by restoring the same with
recovery
> > option
> >
> > I want to try out something and want to know which system table all this
> > gets recorded so that instead of restoring the database with recovery
> > option, can I just update a column in a system table to have it
recovered.
> > I
> > tried to change the status column in sysdatabases but that didnt seem to
> > do
> > it. It just took the status away from within EM that states its a read
> > only
> > to have nothing displayed and make it look like its available but upon
> > modifying tables it still gave me an error message that the database is
in
> > load state or something of that sort.
> >
> > Does anyone know what else I need to update ?
> >
> >
>|||Not possible. The recovery process will apply the UNDO logic based on the transaction log, and once
that is done, you cannot restore any further backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OFfF1of%23EHA.3368@.TK2MSFTNGP15.phx.gbl...
> Yes I am aware of this.. Just wanted to know if theres any other way by
> internally hacking a system table that would make the database available..
> i.e. assuming I do not wish to change anything on this standby database. I
> would like to make this database available by updating some system table
> values and then putting it back in standby state by resetting that system
> table value.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23EwGLRe%23EHA.2032@.tk2msftngp13.phx.gbl...
>> You don't need to execute the enture restore again to change the status.
>> You can issue a RESTORE from Query Analyzer with only the RECOVERY option
>> (omitting the FROM clause).
>> RESTORE DATABASE MyDatabase
>> WITH RECOVERY
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >I know i can recover a standby database by restoring the same with
> recovery
>> > option
>> >
>> > I want to try out something and want to know which system table all this
>> > gets recorded so that instead of restoring the database with recovery
>> > option, can I just update a column in a system table to have it
> recovered.
>> > I
>> > tried to change the status column in sysdatabases but that didnt seem to
>> > do
>> > it. It just took the status away from within EM that states its a read
>> > only
>> > to have nothing displayed and make it look like its available but upon
>> > modifying tables it still gave me an error message that the database is
> in
>> > load state or something of that sort.
>> >
>> > Does anyone know what else I need to update ?
>> >
>> >
>>
>|||To add to Tibor's response, the log restore process requires that the
database to be in exactly the state it was in at the time of the log backup.
Permitting database updates before a log restore will introduce the
potential for conflicting database modifications that the restore process
cannot handle. If you were able to circumvent the database status check,
you could very well end up with a corrupt database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OFfF1of%23EHA.3368@.TK2MSFTNGP15.phx.gbl...
> Yes I am aware of this.. Just wanted to know if theres any other way by
> internally hacking a system table that would make the database available..
> i.e. assuming I do not wish to change anything on this standby database. I
> would like to make this database available by updating some system table
> values and then putting it back in standby state by resetting that system
> table value.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23EwGLRe%23EHA.2032@.tk2msftngp13.phx.gbl...
>> You don't need to execute the enture restore again to change the status.
>> You can issue a RESTORE from Query Analyzer with only the RECOVERY option
>> (omitting the FROM clause).
>> RESTORE DATABASE MyDatabase
>> WITH RECOVERY
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >I know i can recover a standby database by restoring the same with
> recovery
>> > option
>> >
>> > I want to try out something and want to know which system table all
>> > this
>> > gets recorded so that instead of restoring the database with recovery
>> > option, can I just update a column in a system table to have it
> recovered.
>> > I
>> > tried to change the status column in sysdatabases but that didnt seem
>> > to
>> > do
>> > it. It just took the status away from within EM that states its a read
>> > only
>> > to have nothing displayed and make it look like its available but upon
>> > modifying tables it still gave me an error message that the database is
> in
>> > load state or something of that sort.
>> >
>> > Does anyone know what else I need to update ?
>> >
>> >
>>
>|||Oops, should have been:
" The RESTORE process will apply the UNDO... "
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%234UaCli%23EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Not possible. The recovery process will apply the UNDO logic based on the transaction log, and
> once that is done, you cannot restore any further backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:OFfF1of%23EHA.3368@.TK2MSFTNGP15.phx.gbl...
>> Yes I am aware of this.. Just wanted to know if theres any other way by
>> internally hacking a system table that would make the database available..
>> i.e. assuming I do not wish to change anything on this standby database. I
>> would like to make this database available by updating some system table
>> values and then putting it back in standby state by resetting that system
>> table value.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:%23EwGLRe%23EHA.2032@.tk2msftngp13.phx.gbl...
>> You don't need to execute the enture restore again to change the status.
>> You can issue a RESTORE from Query Analyzer with only the RECOVERY option
>> (omitting the FROM clause).
>> RESTORE DATABASE MyDatabase
>> WITH RECOVERY
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >I know i can recover a standby database by restoring the same with
>> recovery
>> > option
>> >
>> > I want to try out something and want to know which system table all this
>> > gets recorded so that instead of restoring the database with recovery
>> > option, can I just update a column in a system table to have it
>> recovered.
>> > I
>> > tried to change the status column in sysdatabases but that didnt seem to
>> > do
>> > it. It just took the status away from within EM that states its a read
>> > only
>> > to have nothing displayed and make it look like its available but upon
>> > modifying tables it still gave me an error message that the database is
>> in
>> > load state or something of that sort.
>> >
>> > Does anyone know what else I need to update ?
>> >
>> >
>>
>>
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OFfF1of%23EHA.3368@.TK2MSFTNGP15.phx.gbl...
> Yes I am aware of this.. Just wanted to know if theres any other way by
> internally hacking a system table that would make the database available..
> i.e. assuming I do not wish to change anything on this standby database. I
> would like to make this database available by updating some system table
> values and then putting it back in standby state by resetting that system
> table value.
Why not use WITH STANDBY?
This will make it a READ-ONLY database that you can later RECOVER further if
need be.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23EwGLRe%23EHA.2032@.tk2msftngp13.phx.gbl...
> > You don't need to execute the enture restore again to change the status.
> > You can issue a RESTORE from Query Analyzer with only the RECOVERY
option
> > (omitting the FROM clause).
> >
> > RESTORE DATABASE MyDatabase
> > WITH RECOVERY
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:u3Zamya%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > >I know i can recover a standby database by restoring the same with
> recovery
> > > option
> > >
> > > I want to try out something and want to know which system table all
this
> > > gets recorded so that instead of restoring the database with recovery
> > > option, can I just update a column in a system table to have it
> recovered.
> > > I
> > > tried to change the status column in sysdatabases but that didnt seem
to
> > > do
> > > it. It just took the status away from within EM that states its a read
> > > only
> > > to have nothing displayed and make it look like its available but upon
> > > modifying tables it still gave me an error message that the database
is
> in
> > > load state or something of that sort.
> > >
> > > Does anyone know what else I need to update ?
> > >
> > >
> >
> >
>
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
Change SQL Server Recovery Model Simple To Full
I would like to change the SQL Server 2000 database recovery model from
simple to full.
I need to executing a log backup immediately before the change permits
recovery to that point. After switching to the simple model, stop executing
log backups.
This log backup is required by the application.
Please help me create T-SQL to backup the log before and after changing the
recovery model from simple to full.
Thank YouCheck sql book online, there are some good examples
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0989F351-4D39-4E11-8B57-FBF82AF5B681@.microsoft.com...
> I would like to change the SQL Server 2000 database recovery model from
> simple to full.
> I need to executing a log backup immediately before the change permits
> recovery to that point. After switching to the simple model, stop
executing
> log backups.
> This log backup is required by the application.
> Please help me create T-SQL to backup the log before and after changing
the
> recovery model from simple to full.
> Thank You
>
simple to full.
I need to executing a log backup immediately before the change permits
recovery to that point. After switching to the simple model, stop executing
log backups.
This log backup is required by the application.
Please help me create T-SQL to backup the log before and after changing the
recovery model from simple to full.
Thank YouCheck sql book online, there are some good examples
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0989F351-4D39-4E11-8B57-FBF82AF5B681@.microsoft.com...
> I would like to change the SQL Server 2000 database recovery model from
> simple to full.
> I need to executing a log backup immediately before the change permits
> recovery to that point. After switching to the simple model, stop
executing
> log backups.
> This log backup is required by the application.
> Please help me create T-SQL to backup the log before and after changing
the
> recovery model from simple to full.
> Thank You
>
Change SQL Server Recovery Model Simple To Full
I would like to change the SQL Server 2000 database recovery model from
simple to full.
I need to executing a log backup immediately before the change permits
recovery to that point. After switching to the simple model, stop executing
log backups.
This log backup is required by the application.
Please help me create T-SQL to backup the log before and after changing the
recovery model from simple to full.
Thank YouCheck sql book online, there are some good examples
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0989F351-4D39-4E11-8B57-FBF82AF5B681@.microsoft.com...
> I would like to change the SQL Server 2000 database recovery model from
> simple to full.
> I need to executing a log backup immediately before the change permits
> recovery to that point. After switching to the simple model, stop
executing
> log backups.
> This log backup is required by the application.
> Please help me create T-SQL to backup the log before and after changing
the
> recovery model from simple to full.
> Thank You
>
simple to full.
I need to executing a log backup immediately before the change permits
recovery to that point. After switching to the simple model, stop executing
log backups.
This log backup is required by the application.
Please help me create T-SQL to backup the log before and after changing the
recovery model from simple to full.
Thank YouCheck sql book online, there are some good examples
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0989F351-4D39-4E11-8B57-FBF82AF5B681@.microsoft.com...
> I would like to change the SQL Server 2000 database recovery model from
> simple to full.
> I need to executing a log backup immediately before the change permits
> recovery to that point. After switching to the simple model, stop
executing
> log backups.
> This log backup is required by the application.
> Please help me create T-SQL to backup the log before and after changing
the
> recovery model from simple to full.
> Thank You
>
Thursday, February 16, 2012
Change Recovery Model: Without GUI
I need a script that I can change the SQL Server 2000
database from Recovery Model: Full to Simple.
Please help me with this task?
Dan
alter database FOO set recovery simple
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan
|||alter database [your database] set recovery full
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan
|||Oops, should be set recovery simple
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
database from Recovery Model: Full to Simple.
Please help me with this task?
Dan
alter database FOO set recovery simple
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan
|||alter database [your database] set recovery full
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan
|||Oops, should be set recovery simple
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
Change Recovery Model: Without GUI
I need a script that I can change the SQL Server 2000
database from Recovery Model: Full to Simple.
Please help me with this task?
Danalter database FOO set recovery simple
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx
.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||alter database [your database] set recovery full
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||Oops, should be set recovery simple
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
database from Recovery Model: Full to Simple.
Please help me with this task?
Danalter database FOO set recovery simple
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx
.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||alter database [your database] set recovery full
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||Oops, should be set recovery simple
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
Change Recovery Model: Without GUI
I need a script that I can change the SQL Server 2000
database from Recovery Model: Full to Simple.
Please help me with this task?
Danalter database FOO set recovery simple
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||alter database [your database] set recovery full
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||Oops, should be set recovery simple
--
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
database from Recovery Model: Full to Simple.
Please help me with this task?
Danalter database FOO set recovery simple
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||alter database [your database] set recovery full
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||Oops, should be set recovery simple
--
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>
Change recovery model using TSQL
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Change recovery model using TSQL
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Try;
alter database MyDB
set recovery full
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Try;
alter database MyDB
set recovery full
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Change recovery model using TSQL
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Change Recovery model through T-SQL
I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.
You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.
You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Change Recovery model through T-SQL
I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRI
NT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRI
NT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Change Recovery model through T-SQL
I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
Change recovery model
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
GracieWhat happens when you try to change it? And how are you doing that? Any
error messages?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie|||What error message do you get?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 2000
> server databases. I can change it for the other user databases but just not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
> > What error message do you get?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "gracie" <gracie@.discussions.microsoft.com> wrote in message
> > news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> > I can't change the recovery model from full to simple on one of my SQL
2000
> > server databases. I can change it for the other user databases but just
not
> > the one I want to change. Any idea how to fix this problem?
> >
> > Thanks,
> > Gracie
> >
> >|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> > I'm trying it through EM. Not error msg the ok button is greyed out/
> >
> > "Tom Moreau" wrote:
> >
> > > What error message do you get?
> > >
> > > --
> > > Tom
> > >
> > > ---
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com/sql
> > >
> > >
> > > "gracie" <gracie@.discussions.microsoft.com> wrote in message
> > > news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> > > I can't change the recovery model from full to simple on one of my SQL
> 2000
> > > server databases. I can change it for the other user databases but just
> not
> > > the one I want to change. Any idea how to fix this problem?
> > >
> > > Thanks,
> > > Gracie
> > >
> > >
>
>
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
GracieWhat happens when you try to change it? And how are you doing that? Any
error messages?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie|||What error message do you get?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 2000
> server databases. I can change it for the other user databases but just not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
> > What error message do you get?
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "gracie" <gracie@.discussions.microsoft.com> wrote in message
> > news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> > I can't change the recovery model from full to simple on one of my SQL
2000
> > server databases. I can change it for the other user databases but just
not
> > the one I want to change. Any idea how to fix this problem?
> >
> > Thanks,
> > Gracie
> >
> >|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> > I'm trying it through EM. Not error msg the ok button is greyed out/
> >
> > "Tom Moreau" wrote:
> >
> > > What error message do you get?
> > >
> > > --
> > > Tom
> > >
> > > ---
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com/sql
> > >
> > >
> > > "gracie" <gracie@.discussions.microsoft.com> wrote in message
> > > news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> > > I can't change the recovery model from full to simple on one of my SQL
> 2000
> > > server databases. I can change it for the other user databases but just
> not
> > > the one I want to change. Any idea how to fix this problem?
> > >
> > > Thanks,
> > > Gracie
> > >
> > >
>
>
Change recovery model
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie
What happens when you try to change it? And how are you doing that? Any
error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
|||What error message do you get?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie
|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 2000
> server databases. I can change it for the other user databases but just not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>
|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>
|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...[vbcol=seagreen]
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
2000[vbcol=seagreen]
not[vbcol=seagreen]
|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> 2000
> not
>
>
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie
What happens when you try to change it? And how are you doing that? Any
error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
|||What error message do you get?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie
|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 2000
> server databases. I can change it for the other user databases but just not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>
|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>
|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...[vbcol=seagreen]
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
2000[vbcol=seagreen]
not[vbcol=seagreen]
|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> 2000
> not
>
>
Change recovery model
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
GracieWhat happens when you try to change it? And how are you doing that? Any
error messages?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie|||What error message do you get?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 200
0
> server databases. I can change it for the other user databases but just no
t
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...[vbcol=seagreen]
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
>
2000[vbcol=seagreen]
not[vbcol=seagreen]|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> 2000
> not
>
>
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
GracieWhat happens when you try to change it? And how are you doing that? Any
error messages?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie|||What error message do you get?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
I can't change the recovery model from full to simple on one of my SQL 2000
server databases. I can change it for the other user databases but just not
the one I want to change. Any idea how to fix this problem?
Thanks,
Gracie|||I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL 200
0
> server databases. I can change it for the other user databases but just no
t
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Try running it through QA with an ALTER DATABASE. Also, are you a member of
the appropriate roles?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
I'm trying it through EM. Not error msg the ok button is greyed out/
"Tom Moreau" wrote:
> What error message do you get?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1A5B440C-1163-40C6-A312-B7B467FC4095@.microsoft.com...
> I can't change the recovery model from full to simple on one of my SQL
2000
> server databases. I can change it for the other user databases but just
not
> the one I want to change. Any idea how to fix this problem?
> Thanks,
> Gracie
>|||Use Query Analyzer
ALTER DATABASE databasename SET RECOVERY SIMPLE
If this doesn't work, tell us what error you get...
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...[vbcol=seagreen]
> I'm trying it through EM. Not error msg the ok button is greyed out/
> "Tom Moreau" wrote:
>
2000[vbcol=seagreen]
not[vbcol=seagreen]|||Worked, thanks!
"Aaron [SQL Server MVP]" wrote:
> Use Query Analyzer
> ALTER DATABASE databasename SET RECOVERY SIMPLE
> If this doesn't work, tell us what error you get...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:06E1EDD1-27D4-4C12-B652-037B9B220A5B@.microsoft.com...
> 2000
> not
>
>
change recovery mode
Hi Guys,
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? Thanks
Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>
|||Thank you guys.
"Hari Prasad" wrote:
> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, this
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup the
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? Thanks
Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>
|||Thank you guys.
"Hari Prasad" wrote:
> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, this
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup the
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>
change recovery mode
Hi Guys,
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? ThanksNope..
VT
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Thank you guys.
"Hari Prasad" wrote:
> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, th
is
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup t
he
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? ThanksNope..
VT
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Thank you guys.
"Hari Prasad" wrote:
> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, th
is
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup t
he
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>
Change publisher server
I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??
ThanX.Yes and no.
Before we begin, a cluster might be a better solution for this case. Either active-active or active-passive will allow you to avoid the down time and the hassle of the move. There is a trade off in terms of the complexity of the cluster, but that still seems like a better choice to me for your situation.
I'd suggest that you check KB #822400 (http://support.microsoft.com/default.aspx?scid=kb;en-us;822400) for more details on Disaster Recovery for SQL Server.
-PatP|||First of all thank you for answering.
Now i have cluster in my environment (Active / Passive ) .I am talking about real disaster - cluster have gone , so i need to move the publisher during minimal time to another location.
Thanx
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??
ThanX.Yes and no.
Before we begin, a cluster might be a better solution for this case. Either active-active or active-passive will allow you to avoid the down time and the hassle of the move. There is a trade off in terms of the complexity of the cluster, but that still seems like a better choice to me for your situation.
I'd suggest that you check KB #822400 (http://support.microsoft.com/default.aspx?scid=kb;en-us;822400) for more details on Disaster Recovery for SQL Server.
-PatP|||First of all thank you for answering.
Now i have cluster in my environment (Active / Passive ) .I am talking about real disaster - cluster have gone , so i need to move the publisher during minimal time to another location.
Thanx
Change publisher of replication
I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ?
ThanX.
This isn't quite so simple. How are you synchronizing with the standby publisher? Are you intending to use 'sync with backup' which ensures the publisher backup is never behind the distributor backup. Will you be restoring earlier copies of the subscriber
to ensure they are not ahead of the distributor? have a look in BOL at "Strategies for Backing Up and Restoring Transactional Replication".
HTH,
Paul Ibison
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ?
ThanX.
This isn't quite so simple. How are you synchronizing with the standby publisher? Are you intending to use 'sync with backup' which ensures the publisher backup is never behind the distributor backup. Will you be restoring earlier copies of the subscriber
to ensure they are not ahead of the distributor? have a look in BOL at "Strategies for Backing Up and Restoring Transactional Replication".
HTH,
Paul Ibison
Subscribe to:
Posts (Atom)