Changing backup model before running reindex

Our txn log is ballooning after we re-index. I've see a recommendataion to
change to Bulk-logged before re-indexing and I'm wandering, will this break
my backup chain? Currently we are running full backups every Sun morning
and re-indexing Mon and Thurs.
Thanks.Are you doing log backups? If not, go to simple recovery model.
If you are doing log backup, then changing to bulk-logged will produce less
records in the ldf file,
but the following log backup can potentially be larger than if in full recov
ery. It will not
adversely affect your backup strategy, but two things to remember are:
If you have performed a bulk-logged operation, then you can't restore a log
backup to point-in time.
If db is in bulk-logged mode, you won't be able to backup log if a data file
crashes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin" <none@.none.com> wrote in message news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft
.com...
> Our txn log is ballooning after we re-index. I've see a recommendataion t
o change to Bulk-logged
> before re-indexing and I'm wandering, will this break my backup chain? Cu
rrently we are running
> full backups every Sun morning and re-indexing Mon and Thurs.
> Thanks.|||Addition to Tibor's comment: Take backup before and after changing your
Recovery Model to SIMPLE\BULK LOGGED for indexing operations.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8BF808EA-D953-4467-83BD-3CDC59691BC7@.microsoft.com...
> Are you doing log backups? If not, go to simple recovery model.
> If you are doing log backup, then changing to bulk-logged will produce
> less records in the ldf file, but the following log backup can potentially
> be larger than if in full recovery. It will not adversely affect your
> backup strategy, but two things to remember are:
> If you have performed a bulk-logged operation, then you can't restore a
> log backup to point-in time.
> If db is in bulk-logged mode, you won't be able to backup log if a data
> file crashes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kevin" <none@.none.com> wrote in message
> news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
>

Changing backup model before running reindex

Our txn log is ballooning after we re-index. I've see a recommendataion to
change to Bulk-logged before re-indexing and I'm wandering, will this break
my backup chain? Currently we are running full backups every Sun morning
and re-indexing Mon and Thurs.
Thanks.Are you doing log backups? If not, go to simple recovery model.
If you are doing log backup, then changing to bulk-logged will produce less records in the ldf file,
but the following log backup can potentially be larger than if in full recovery. It will not
adversely affect your backup strategy, but two things to remember are:
If you have performed a bulk-logged operation, then you can't restore a log backup to point-in time.
If db is in bulk-logged mode, you won't be able to backup log if a data file crashes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin" <none@.none.com> wrote in message news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
> Our txn log is ballooning after we re-index. I've see a recommendataion to change to Bulk-logged
> before re-indexing and I'm wandering, will this break my backup chain? Currently we are running
> full backups every Sun morning and re-indexing Mon and Thurs.
> Thanks.|||Addition to Tibor's comment: Take backup before and after changing your
Recovery Model to SIMPLE\BULK LOGGED for indexing operations.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8BF808EA-D953-4467-83BD-3CDC59691BC7@.microsoft.com...
> Are you doing log backups? If not, go to simple recovery model.
> If you are doing log backup, then changing to bulk-logged will produce
> less records in the ldf file, but the following log backup can potentially
> be larger than if in full recovery. It will not adversely affect your
> backup strategy, but two things to remember are:
> If you have performed a bulk-logged operation, then you can't restore a
> log backup to point-in time.
> If db is in bulk-logged mode, you won't be able to backup log if a data
> file crashes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kevin" <none@.none.com> wrote in message
> news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
>> Our txn log is ballooning after we re-index. I've see a recommendataion
>> to change to Bulk-logged before re-indexing and I'm wandering, will this
>> break my backup chain? Currently we are running full backups every Sun
>> morning and re-indexing Mon and Thurs.
>> Thanks.
>

Changes to MDX when upgrading to AS 2005

I was wondering if anybody has upgraded to AS 2005 without having to change
their stored MDX extensively to work with the new AS model. The company I
work for has 100's of reports with 100's of MDX queries that might
potentially need to be updated because 1) it's just not possible to do things
the old way and 2) we want to utilize the new features of SSAS 2005.
I'm looking for general advice or maybe some good things to know when
upgrading to SSAS 2005 while using SSRS 2005 as your enterprise reporting
framework.
Thanks,
Joshhavent got anything definitive, other than to say our mdx based reports
ported fine. Only issue was 2005 was stricter with having links in page
footers which caused a warning in 2000 but wouldnt work in 2005
"JSI" <JSI@.discussions.microsoft.com> wrote in message
news:B909920B-124C-4B09-B584-2EF40847F3B6@.microsoft.com...
>I was wondering if anybody has upgraded to AS 2005 without having to change
> their stored MDX extensively to work with the new AS model. The company I
> work for has 100's of reports with 100's of MDX queries that might
> potentially need to be updated because 1) it's just not possible to do
> things
> the old way and 2) we want to utilize the new features of SSAS 2005.
> I'm looking for general advice or maybe some good things to know when
> upgrading to SSAS 2005 while using SSRS 2005 as your enterprise reporting
> framework.
> Thanks,
> Josh

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
endBegs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model
.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
endThis is a multi-part message in MIME format.
--030201070507090507090504
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Begs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:
>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>
--030201070507090507090504
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Begs the question - how did it get in that state? The sid for
those DBs should always be 0x01, which correlates to the 'sa' login.Â
Both solutions you suggest are fairly questionable, but it's pretty
much a case of damned if you do, damned if you don't.<br>
<br>
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily.Â
That way you can be sure that all the other things that
sp_changedbowner does (tweaking sysusers, etc.) happens too. But
neither method would be particularly kosher - just make sure you backup
master & model before doing any of this, as you might shoot
yourself in the foot if you're not careful.<br>
<br>
Good luck.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
AussieBri wrote:
<blockquote cite="midC6A09B6D-DCCF-4E45-ADA4-17226F6BE6EF@.microsoft.com"
type="cite">
<pre wrap="">Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
</pre>
</blockquote>
</body>
</html>
--030201070507090507090504--

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
Begs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Change the location of the tempdb and the model databases

Is there a way to change the location of the tempdb and the model database files. I tried to do a restore with move with no luck and I cannot seem to get the syntax for an Alter database. Any suggestions?Look up Atricle Q224071 in Microsoft's knowledge base, it addresses your questions.

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
>

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
>

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
>

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
>

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
>

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

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

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

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.
>

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.
>

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.
>

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
> > >
> > >
>
>

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
>
>