|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Recovery Model Question.Hello to everyone,
I want to ask if there is a way ,maybe through a strored procedure or sql script to change massive in 200 databases for example the recovery model from full to simple ?? If anybody has an idea or knows i will appreciate it. Thanx a lot. Cipher. See ALTER DATABASE in BOL
Show quote "Cipher" <cip***@aol.com> wrote in message news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... > Hello to everyone, > > I want to ask if there is a way ,maybe through a strored procedure or sql > script > to change massive in 200 databases for example the recovery model from > full to simple ?? > If anybody has an idea or knows i will appreciate it. > > Thanx a lot. > Cipher. > Are you asking for a way to change this value against 200 different
databases? Show quote "Cipher" <cip***@aol.com> wrote in message news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... > Hello to everyone, > > I want to ask if there is a way ,maybe through a strored procedure or sql > script > to change massive in 200 databases for example the recovery model from > full to simple ?? > If anybody has an idea or knows i will appreciate it. > > Thanx a lot. > Cipher. > Yes,exactly.
I want to change massive all the 200 databases recovery model option from full to simple but i dont want to achieve this manually because of the huge overhead. For that reason i asked ,if theres a more quicker and better way to achive this. Thnx a lot. Cipher. Show quote "Immy" <therealasianb***@hotmail.com> wrote in message news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... > Are you asking for a way to change this value against 200 different > databases? > > "Cipher" <cip***@aol.com> wrote in message > news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >> Hello to everyone, >> >> I want to ask if there is a way ,maybe through a strored procedure or sql >> script >> to change massive in 200 databases for example the recovery model from >> full to simple ?? >> If anybody has an idea or knows i will appreciate it. >> >> Thanx a lot. >> Cipher. >> > > No version posted, assuming 2005:
Modify below to your liking, run it, take the result, tidy up and verify, and execute it. SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases WHERE name NOT IN('master', 'tempdb') -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Morgan" <mor***@heaven.com> wrote in message news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... > Yes,exactly. > I want to change massive all the 200 databases recovery > model option from full to simple but i dont want to achieve this manually > because of the huge overhead. > For that reason i asked ,if theres a more quicker and better way to achive > this. > > Thnx a lot. > Cipher. > > > "Immy" <therealasianb***@hotmail.com> wrote in message > news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >> Are you asking for a way to change this value against 200 different >> databases? >> >> "Cipher" <cip***@aol.com> wrote in message >> news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>> Hello to everyone, >>> >>> I want to ask if there is a way ,maybe through a strored procedure or sql >>> script >>> to change massive in 200 databases for example the recovery model from >>> full to simple ?? >>> If anybody has an idea or knows i will appreciate it. >>> >>> Thanx a lot. >>> Cipher. >>> >> >> > > Tibor,
I will even keep MSDB in simple recovery mode and takes the differential backup twice a day. SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases WHERE name NOT IN('master', 'tempdb','MSDB') Thanks Hari Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... > No version posted, assuming 2005: > > Modify below to your liking, run it, take the result, tidy up and verify, > and execute it. > > SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases > WHERE name NOT IN('master', 'tempdb') > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Morgan" <mor***@heaven.com> wrote in message > news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >> Yes,exactly. >> I want to change massive all the 200 databases recovery >> model option from full to simple but i dont want to achieve this manually >> because of the huge overhead. >> For that reason i asked ,if theres a more quicker and better way to >> achive this. >> >> Thnx a lot. >> Cipher. >> >> >> "Immy" <therealasianb***@hotmail.com> wrote in message >> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>> Are you asking for a way to change this value against 200 different >>> databases? >>> >>> "Cipher" <cip***@aol.com> wrote in message >>> news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>> Hello to everyone, >>>> >>>> I want to ask if there is a way ,maybe through a strored procedure or >>>> sql script >>>> to change massive in 200 databases for example the recovery model from >>>> full to simple ?? >>>> If anybody has an idea or knows i will appreciate it. >>>> >>>> Thanx a lot. >>>> Cipher. >>>> >>> >>> >> First,thnx a lot guys for your support.
I run this sql script through Query Analyzer : use master; select 'alter database' +name+ 'set recovery simple' from dbo.sysdatabases where name not in('master','tempdb','msdb','model') go but the weird is that the script runs succefully without errors ,but it didnt make any change to any databases!I check the recovery model and remains unfortunately full in all databases.. i dont understand.. Also because i forgot to mention it,the SQL version is SQL 2000 SP 4 Cipher. Show quote "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:eVZMFxpZHHA.1400@TK2MSFTNGP06.phx.gbl... > Tibor, > > I will even keep MSDB in simple recovery mode and takes the differential > backup twice a day. > > SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' > FROM sys.databases > WHERE name NOT IN('master', 'tempdb','MSDB') > > Thanks > Hari > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in message news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... >> No version posted, assuming 2005: >> >> Modify below to your liking, run it, take the result, tidy up and verify, >> and execute it. >> >> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases >> WHERE name NOT IN('master', 'tempdb') >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Morgan" <mor***@heaven.com> wrote in message >> news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >>> Yes,exactly. >>> I want to change massive all the 200 databases recovery >>> model option from full to simple but i dont want to achieve this >>> manually >>> because of the huge overhead. >>> For that reason i asked ,if theres a more quicker and better way to >>> achive this. >>> >>> Thnx a lot. >>> Cipher. >>> >>> >>> "Immy" <therealasianb***@hotmail.com> wrote in message >>> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>>> Are you asking for a way to change this value against 200 different >>>> databases? >>>> >>>> "Cipher" <cip***@aol.com> wrote in message >>>> news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>>> Hello to everyone, >>>>> >>>>> I want to ask if there is a way ,maybe through a strored procedure or >>>>> sql script >>>>> to change massive in 200 databases for example the recovery model from >>>>> full to simple ?? >>>>> If anybody has an idea or knows i will appreciate it. >>>>> >>>>> Thanx a lot. >>>>> Cipher. >>>>> >>>> >>>> >>> > The script generates your ALTER DATABASE commands for you. Take the result of the script, paste in
into a query window and execute it. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Cipher" <cip***@aol.com> wrote in message news:eZOTxetZHHA.2448@TK2MSFTNGP02.phx.gbl... > First,thnx a lot guys for your support. > I run this sql script through Query Analyzer : > > use master; > select 'alter database' +name+ 'set recovery simple' > from dbo.sysdatabases > where name not in('master','tempdb','msdb','model') > go > > but the weird is that the script runs succefully without errors ,but it didnt make any change > to any databases!I check the recovery model and remains unfortunately full in all databases.. > i dont understand.. > Also because i forgot to mention it,the SQL version is SQL 2000 SP 4 > > Cipher. > > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:eVZMFxpZHHA.1400@TK2MSFTNGP06.phx.gbl... >> Tibor, >> >> I will even keep MSDB in simple recovery mode and takes the differential backup twice a day. >> >> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' >> FROM sys.databases >> WHERE name NOT IN('master', 'tempdb','MSDB') >> >> Thanks >> Hari >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message >> news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... >>> No version posted, assuming 2005: >>> >>> Modify below to your liking, run it, take the result, tidy up and verify, and execute it. >>> >>> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases >>> WHERE name NOT IN('master', 'tempdb') >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Morgan" <mor***@heaven.com> wrote in message news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >>>> Yes,exactly. >>>> I want to change massive all the 200 databases recovery >>>> model option from full to simple but i dont want to achieve this manually >>>> because of the huge overhead. >>>> For that reason i asked ,if theres a more quicker and better way to achive this. >>>> >>>> Thnx a lot. >>>> Cipher. >>>> >>>> >>>> "Immy" <therealasianb***@hotmail.com> wrote in message >>>> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>>>> Are you asking for a way to change this value against 200 different databases? >>>>> >>>>> "Cipher" <cip***@aol.com> wrote in message news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>>>> Hello to everyone, >>>>>> >>>>>> I want to ask if there is a way ,maybe through a strored procedure or sql script >>>>>> to change massive in 200 databases for example the recovery model from full to simple ?? >>>>>> If anybody has an idea or knows i will appreciate it. >>>>>> >>>>>> Thanx a lot. >>>>>> Cipher. >>>>>> >>>>> >>>>> >>>> >> > > Yes,it works fine now.
The script achieve a massive recovery model change! Thx a lot Tibor! Cipher. Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uKlFswtZHHA.4808@TK2MSFTNGP04.phx.gbl... > The script generates your ALTER DATABASE commands for you. Take the result > of the script, paste in into a query window and execute it. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Cipher" <cip***@aol.com> wrote in message > news:eZOTxetZHHA.2448@TK2MSFTNGP02.phx.gbl... >> First,thnx a lot guys for your support. >> I run this sql script through Query Analyzer : >> >> use master; >> select 'alter database' +name+ 'set recovery simple' >> from dbo.sysdatabases >> where name not in('master','tempdb','msdb','model') >> go >> >> but the weird is that the script runs succefully without errors ,but it >> didnt make any change >> to any databases!I check the recovery model and remains unfortunately >> full in all databases.. >> i dont understand.. >> Also because i forgot to mention it,the SQL version is SQL 2000 SP 4 >> >> Cipher. >> >> >> >> "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message >> news:eVZMFxpZHHA.1400@TK2MSFTNGP06.phx.gbl... >>> Tibor, >>> >>> I will even keep MSDB in simple recovery mode and takes the differential >>> backup twice a day. >>> >>> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' >>> FROM sys.databases >>> WHERE name NOT IN('master', 'tempdb','MSDB') >>> >>> Thanks >>> Hari >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >>> in message news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... >>>> No version posted, assuming 2005: >>>> >>>> Modify below to your liking, run it, take the result, tidy up and >>>> verify, and execute it. >>>> >>>> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM >>>> sys.databases >>>> WHERE name NOT IN('master', 'tempdb') >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> "Morgan" <mor***@heaven.com> wrote in message >>>> news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >>>>> Yes,exactly. >>>>> I want to change massive all the 200 databases recovery >>>>> model option from full to simple but i dont want to achieve this >>>>> manually >>>>> because of the huge overhead. >>>>> For that reason i asked ,if theres a more quicker and better way to >>>>> achive this. >>>>> >>>>> Thnx a lot. >>>>> Cipher. >>>>> >>>>> >>>>> "Immy" <therealasianb***@hotmail.com> wrote in message >>>>> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>>>>> Are you asking for a way to change this value against 200 different >>>>>> databases? >>>>>> >>>>>> "Cipher" <cip***@aol.com> wrote in message >>>>>> news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>>>>> Hello to everyone, >>>>>>> >>>>>>> I want to ask if there is a way ,maybe through a strored procedure >>>>>>> or sql script >>>>>>> to change massive in 200 databases for example the recovery model >>>>>>> from full to simple ?? >>>>>>> If anybody has an idea or knows i will appreciate it. >>>>>>> >>>>>>> Thanx a lot. >>>>>>> Cipher. >>>>>>> >>>>>> >>>>>> >>>>> >>> >> >> > Hi Hari,
We all feel differently about msdb. That is why I said "Modify below to your liking". I prefer to do log backups for msdb, and have msdb as the last database for each backup execution. This way I always have backup of the most recent backup history. I might not be the one who is doing the restore, and if somebody restore msdb and then the other databases based on backuphistory, they won't get the databases as up to date as the backups would allow. Many find this overkill, though. :-) -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:eVZMFxpZHHA.1400@TK2MSFTNGP06.phx.gbl... > Tibor, > > I will even keep MSDB in simple recovery mode and takes the differential backup twice a day. > > SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' > FROM sys.databases > WHERE name NOT IN('master', 'tempdb','MSDB') > > Thanks > Hari > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... >> No version posted, assuming 2005: >> >> Modify below to your liking, run it, take the result, tidy up and verify, and execute it. >> >> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases >> WHERE name NOT IN('master', 'tempdb') >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Morgan" <mor***@heaven.com> wrote in message news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >>> Yes,exactly. >>> I want to change massive all the 200 databases recovery >>> model option from full to simple but i dont want to achieve this manually >>> because of the huge overhead. >>> For that reason i asked ,if theres a more quicker and better way to achive this. >>> >>> Thnx a lot. >>> Cipher. >>> >>> >>> "Immy" <therealasianb***@hotmail.com> wrote in message >>> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>>> Are you asking for a way to change this value against 200 different databases? >>>> >>>> "Cipher" <cip***@aol.com> wrote in message news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>>> Hello to everyone, >>>>> >>>>> I want to ask if there is a way ,maybe through a strored procedure or sql script >>>>> to change massive in 200 databases for example the recovery model from full to simple ?? >>>>> If anybody has an idea or knows i will appreciate it. >>>>> >>>>> Thanx a lot. >>>>> Cipher. >>>>> >>>> >>>> >>> > Hi Tibor,
I agree with you... Thanks Hari Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:OJpY2gtZHHA.1508@TK2MSFTNGP06.phx.gbl... > Hi Hari, > > We all feel differently about msdb. That is why I said "Modify below to > your liking". I prefer to do log backups for msdb, and have msdb as the > last database for each backup execution. This way I always have backup of > the most recent backup history. I might not be the one who is doing the > restore, and if somebody restore msdb and then the other databases based > on backuphistory, they won't get the databases as up to date as the > backups would allow. Many find this overkill, though. :-) > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:eVZMFxpZHHA.1400@TK2MSFTNGP06.phx.gbl... >> Tibor, >> >> I will even keep MSDB in simple recovery mode and takes the differential >> backup twice a day. >> >> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' >> FROM sys.databases >> WHERE name NOT IN('master', 'tempdb','MSDB') >> >> Thanks >> Hari >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:eSAEbjnZHHA.5044@TK2MSFTNGP05.phx.gbl... >>> No version posted, assuming 2005: >>> >>> Modify below to your liking, run it, take the result, tidy up and >>> verify, and execute it. >>> >>> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM >>> sys.databases >>> WHERE name NOT IN('master', 'tempdb') >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Morgan" <mor***@heaven.com> wrote in message >>> news:%23V1czMnZHHA.1400@TK2MSFTNGP06.phx.gbl... >>>> Yes,exactly. >>>> I want to change massive all the 200 databases recovery >>>> model option from full to simple but i dont want to achieve this >>>> manually >>>> because of the huge overhead. >>>> For that reason i asked ,if theres a more quicker and better way to >>>> achive this. >>>> >>>> Thnx a lot. >>>> Cipher. >>>> >>>> >>>> "Immy" <therealasianb***@hotmail.com> wrote in message >>>> news:%23DV67OlZHHA.348@TK2MSFTNGP02.phx.gbl... >>>>> Are you asking for a way to change this value against 200 different >>>>> databases? >>>>> >>>>> "Cipher" <cip***@aol.com> wrote in message >>>>> news:eIh22lkZHHA.4772@TK2MSFTNGP05.phx.gbl... >>>>>> Hello to everyone, >>>>>> >>>>>> I want to ask if there is a way ,maybe through a strored procedure or >>>>>> sql script >>>>>> to change massive in 200 databases for example the recovery model >>>>>> from full to simple ?? >>>>>> If anybody has an idea or knows i will appreciate it. >>>>>> >>>>>> Thanx a lot. >>>>>> Cipher. >>>>>> >>>>> >>>>> >>>> >> >  |
|||||||||||||||||||||||
Other interesting topics