How to change the compatibility level to 9.0

I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL? any help is greatly appreciated.

Emad

I don't know if it can be called easy, but the following script should do the work, by using the stored procedure sp_dbcmptlevel , to update all databases whose compatibility_level is not equal to 90.

Hope this Helps!!!

Roberto Hernandez-Pou
http://community.rhpconsulting.net

DECLARE DATABASES_80 CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '90'

DECLARE @.DATABASENAME NVARCHAR(255)
DECLARE @.COUNTER INT

-- INITIALIZE VARIABLES
SET @.COUNTER = 1

-- OPEN CURSOR
OPEN DATABASES_80

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN

-- CHANGE DATABASE COMPATIBILITY
EXECUTE sp_dbcmptlevel @.DATABASENAME , '90'

-- PRINT
PRINT '(' + RIGHT(('000' + CAST(@.COUNTER AS NVARCHAR(255))),3) + ') - CHANGED DATABASE COMPATIBILITY LEVEL FOR: ' + @.DATABASENAME

-- INCREASE COUNTER
SET @.COUNTER = @.COUNTER + 1

END

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
END

CLOSE DATABASES_80
DEALLOCATE DATABASES_80

GO

sql

How to change Database name in SQLServer?

I want to change some Databases' name. But the only way I know is backup the
database, and change another name when recovering it. Is there any other way
that is easier than that below? thanks
zlf
You can do it using ALTER DATABASE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>I want to change some Databases' name. But the only way I know is backup the
> database, and change another name when recovering it. Is there any other way
> that is easier than that below? thanks
> zlf
>
|||Thank you!
zlf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> д?
news:#vyABZ4LFHA.3852@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> You can do it using ALTER DATABASE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
> news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
way
>
|||sp_renamedb
"news.microsoft.com" wrote:

> I want to change some Databases' name. But the only way I know is backup the
> database, and change another name when recovering it. Is there any other way
> that is easier than that below? thanks
> zlf
>
>

How to change Database name in SQLServer?

I want to change some Databases' name. But the only way I know is backup the
database, and change another name when recovering it. Is there any other way
that is easier than that below? thanks
zlfYou can do it using ALTER DATABASE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>I want to change some Databases' name. But the only way I know is backup the
> database, and change another name when recovering it. Is there any other way
> that is easier than that below? thanks
> zlf
>|||Thank you!
zlf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> дÈëÏûÏ¢
news:#vyABZ4LFHA.3852@.tk2msftngp13.phx.gbl...
> You can do it using ALTER DATABASE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
> news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >I want to change some Databases' name. But the only way I know is backup
the
> > database, and change another name when recovering it. Is there any other
way
> > that is easier than that below? thanks
> >
> > zlf
> >
> >
>|||sp_renamedb
"news.microsoft.com" wrote:
> I want to change some Databases' name. But the only way I know is backup the
> database, and change another name when recovering it. Is there any other way
> that is easier than that below? thanks
> zlf
>
>

How to change Database name in SQLServer?

I want to change some Databases' name. But the only way I know is backup the
database, and change another name when recovering it. Is there any other way
that is easier than that below? thanks
zlfYou can do it using ALTER DATABASE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>I want to change some Databases' name. But the only way I know is backup th
e
> database, and change another name when recovering it. Is there any other w
ay
> that is easier than that below? thanks
> zlf
>|||Thank you!
zlf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> д?
news:#vyABZ4LFHA.3852@.tk2msftngp13.phx.gbl...
> You can do it using ALTER DATABASE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "news.microsoft.com" <zlfcn@.hotmail.com> wrote in message
> news:OsHwjN4LFHA.3500@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
way[vbcol=seagreen]
>|||sp_renamedb
"news.microsoft.com" wrote:

> I want to change some Databases' name. But the only way I know is backup t
he
> database, and change another name when recovering it. Is there any other w
ay
> that is easier than that below? thanks
> zlf
>
>sql

how to change database at runtime?

Hello,
Our customers often have 2 or more databases with the same structure.
How can we pass the database as a parameter at runtime in Reporting Services
2005?
Thank you,
LoreacaIn 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
Design the report using a static connection string. A static connection
string refers to a connection string that is not set through an expression
(for example, when you follow the steps for creating a report-specific or
shared data source, you are defining a static connection string). Using a
static connection string allows you to connect to the data source in Report
Designer so that you can get the query results you need to create the
report.
When defining the data source connection, do not use a shared data source.
You cannot use a data source expression in a shared data source. You must
define a report-specific data source for the report.
Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
Add a report parameter to specify a data source. For parameter values, you
can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
Be sure that the list of data sources share the same database schema. All
report design begins with schema information. If there is a mismatch between
the schema used to define the report and the actual schema used by the
report at run time, the report might not run.
Before publishing the report, replace the static connection string with an
expression. Wait until you are finished designing the report before you
replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
news:eXsv4cceGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Our customers often have 2 or more databases with the same structure.
> How can we pass the database as a parameter at runtime in Reporting
> Services 2005?
> Thank you,
> Loreaca
>|||Perfect.
Thank you!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eUgGHreeGHA.1320@.TK2MSFTNGP04.phx.gbl...
> In 2005 you will be able to have dynamic datasources. From 2005 help:
> Data Source Expressions
> You can put an expression into a connection string to allow users to
> select the data source at run time. For example, suppose a multinational
> firm has data servers in several countries. With an expression-based
> connection string, a user who is running a sales report can select a data
> source for a particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a
> report parameter named ServerName:
> Copy Code
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
>
>
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the
> following guidelines when defining a data source expression:
>>>>>>>>
> Design the report using a static connection string. A static connection
> string refers to a connection string that is not set through an expression
> (for example, when you follow the steps for creating a report-specific or
> shared data source, you are defining a static connection string). Using a
> static connection string allows you to connect to the data source in
> Report Designer so that you can get the query results you need to create
> the report.
> When defining the data source connection, do not use a shared data source.
> You cannot use a data source expression in a shared data source. You must
> define a report-specific data source for the report.
> Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
> Add a report parameter to specify a data source. For parameter values, you
> can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
> Be sure that the list of data sources share the same database schema. All
> report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
> Before publishing the report, replace the static connection string with an
> expression. Wait until you are finished designing the report before you
> replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
>>>>>>>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
> news:eXsv4cceGHA.1324@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Our customers often have 2 or more databases with the same structure.
>> How can we pass the database as a parameter at runtime in Reporting
>> Services 2005?
>> Thank you,
>> Loreaca
>

How to change collation on MODEL ?

I have a 2005 SP1 server with the wrong server collation.
All user databases on this server have the right collation.
I am getting collation conflict errors when I use temp tables.
I believe this is because tempdb is created based on "model", and model has
the server collation.
I have tried to change the "model" collation using ALTER DATABASE, but I get
the error message "Cannot alter the database 'model' because it is a system
database.
"
How can I force tempdb to have a specific collation, instead of the same
collation as the server collation?> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different collations across the system
databases. This is done using the SETUP program in 2005. This is described in
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325335
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The doc mentions that all service packs and hotfixes are lost when rebuilding
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
> You have to rebuild the system databases, as you cannot have different collations across the system
> databases. This is done using the SETUP program in 2005. This is described in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
> >I have a 2005 SP1 server with the wrong server collation.
> > All user databases on this server have the right collation.
> >
> > I am getting collation conflict errors when I use temp tables.
> > I believe this is because tempdb is created based on "model", and model has
> > the server collation.
> >
> > I have tried to change the "model" collation using ALTER DATABASE, but I get
> > the error message "Cannot alter the database 'model' because it is a system
> > database.
> > "
> >
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the collation during
installation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...
> The doc mentions that all service packs and hotfixes are lost when rebuilding
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>> You have to rebuild the system databases, as you cannot have different collations across the
>> system
>> databases. This is done using the SETUP program in 2005. This is described in
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Pat" <pat@.online.nospam> wrote in message
>> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>> >I have a 2005 SP1 server with the wrong server collation.
>> > All user databases on this server have the right collation.
>> >
>> > I am getting collation conflict errors when I use temp tables.
>> > I believe this is because tempdb is created based on "model", and model has
>> > the server collation.
>> >
>> > I have tried to change the "model" collation using ALTER DATABASE, but I get
>> > the error message "Cannot alter the database 'model' because it is a system
>> > database.
>> > "
>> >
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>>|||"Pat" wrote:
> I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
In addition to the other replies to your query, there may be another way to
do this.
If you create a new temporary table in a stored procedure, use the good
collation.
example:
Create Table #Status
(
State nvarchar(128) collate database_default
)
This might be a good solution if there are not too many places where
temporary tables are created.
hope this helps.
kind regards,
Bart.sql

How to calculate database Rate of Change?

I need to determine the rate of change (ROC) in bytes/day for my production
databases. You might ask why I'm doing this. We have a new NetApp SAN, and it
has the ability to perform SQL Server snapshots for amazingly fast short-term
backup and recovery. In order to implement this, however, I need to size the
volumes appropriately and not waste a lot of space. The formula I have been
provided is this:
Space for Snapshot copies = ROC in bytes per day * number of Snapshot copies
Does anybody have any ideas as to how I might calculate ROC? I think using
DCM (database change maps) would be perfect if I could somehow obtain that
information through a fairly simple sql query. Otherwise I thought of using
tran log backup sizes, but I'm not sure how accurately that reflects actual
data change in the databases.
Any help is appreciated.
Thanks,
Aaron Sentell
Sr. DBA
Compass Group USAI don't believe that for sql server ROC will be useful for properly
determining space needed for snapshots. Supposes you change one byte in one
row of one table and everything else is the same. What is key here for
snapshot size is how much physical disk space the snapshot mechanism marks
off as changed. I assure you it isn't 1 byte. Probably more like 8-64K or
possibly even more. Now, suppose you change 1 byte on every physically
minimum structure used in snapshotting. You may have changed say 100000
bytes but have to save off 6GB worth of space as changed. At least that is
my understanding.
To answer your question however, you can examine the output of sp_spaceused.
This is not guaranteed to be accurate however unless you first run dbcc
updateusage (which can be intensive on the server). On 2005 you can add up
pages on several DMVs related to storage (such as
sys.dm_db_index_physical_stats). Search the web for several canned queries
for this.
Tran log backups are not exactly equal to actual data change but can be a
gross estimate. The issue here is that the same data can get updated over
and over which will be logged entries but not necessarily be more physical
"ROC".
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>|||It depends in they increment multiple times for the same data. If not, you
could create a database snapshot. Average the size for several days to get a
good number. However, I would throughly test when it comes to relying on a
3rd party product for database recovery.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>|||Aaron,
An extra thought. Assuming that you are running differential backups is to
calculate from the size of the differentials. A differential marks an 8
page extent (64 KB) for backing up whenever anything changes on a page in
that extent. If the SAN is also snapshotting at that granularity, you might
get an idea.
Of course, once an extent is marked, it continues in the differential bit
map only once, although it may be updated every day. Perhaps you could get
the measure of the day, by doing something like the following for a little
while:
11:00 PM - Differential backup
12:00 AM - Full backup
That way, every 11:00 PM differential would only be one day's worth of
changes. You would still need some way of estimating the velocity of
changes per extent, but you might be able to examine your work load and make
a semi-educated guess.
FWIW,
RLF
"Aaron Sentell" <Aaron Sentell@.discussions.microsoft.com> wrote in message
news:4A5BA321-B82C-4F41-A94D-5F2B5B816600@.microsoft.com...
>I need to determine the rate of change (ROC) in bytes/day for my production
> databases. You might ask why I'm doing this. We have a new NetApp SAN, and
> it
> has the ability to perform SQL Server snapshots for amazingly fast
> short-term
> backup and recovery. In order to implement this, however, I need to size
> the
> volumes appropriately and not waste a lot of space. The formula I have
> been
> provided is this:
> Space for Snapshot copies = ROC in bytes per day * number of Snapshot
> copies
> Does anybody have any ideas as to how I might calculate ROC? I think using
> DCM (database change maps) would be perfect if I could somehow obtain that
> information through a fairly simple sql query. Otherwise I thought of
> using
> tran log backup sizes, but I'm not sure how accurately that reflects
> actual
> data change in the databases.
> Any help is appreciated.
> Thanks,
> Aaron Sentell
> Sr. DBA
> Compass Group USA
>

How to bring down a database

I have a need to shutdown a database on a server.
But it has other databases, I want to leave other
databases up and running.
How can I bring down a database and then how can
I bring it back up without affecting other databases
on the same server.
Thank you,
-LeOne way would be put the db in single user mode, then detach the db, and
then reattach it.
Quentin
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:2a00201c46536$90a1aad0$a301280a@.phx
.gbl...
> I have a need to shutdown a database on a server.
> But it has other databases, I want to leave other
> databases up and running.
> How can I bring down a database and then how can
> I bring it back up without affecting other databases
> on the same server.
> Thank you,
> -Le|||Assuming you have already logged out or killed all user processes you can
take the DB offline as follows:
EXEC sp_dboption 'DATABASE_NAME', 'offline', 'TRUE'
Perhaps more useful is to make the database single-user only:
USE DATABASE_NAME
EXEC sp_dboption 'DATABASE_NAME', 'single user', 'TRUE'
David Portas
SQL Server MVP
--|||Hi,
To make a database Offline:-
alter database <dbname> set single_user with rollback immediate -- turns
database to single user
go
alter database <dbname> set offline
-- To set database online and multi user
alter database <dbname> set online
go
alter database <dbname> set multi_user
go
THanks
Hari
MCDBA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:oZ-dncVuSMhJW3DdRVn-uw@.giganews.com...
> Assuming you have already logged out or killed all user processes you can
> take the DB offline as follows:
> EXEC sp_dboption 'DATABASE_NAME', 'offline', 'TRUE'
> Perhaps more useful is to make the database single-user only:
> USE DATABASE_NAME
> EXEC sp_dboption 'DATABASE_NAME', 'single user', 'TRUE'
> --
> David Portas
> SQL Server MVP
> --
>|||David,
Thank you,
-Le
>--Original Message--
>Assuming you have already logged out or killed all user
processes you can
>take the DB offline as follows:
>EXEC sp_dboption 'DATABASE_NAME', 'offline', 'TRUE'
>Perhaps more useful is to make the database single-user
only:
>USE DATABASE_NAME
>EXEC sp_dboption 'DATABASE_NAME', 'single user', 'TRUE'
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||Hari,
Thank you,
-Le

>--Original Message--
>Hi,
>To make a database Offline:-
>alter database <dbname> set single_user with rollback
immediate -- turns
>database to single user
>go
>alter database <dbname> set offline
>
>-- To set database online and multi user
>alter database <dbname> set online
>go
>alter database <dbname> set multi_user
>go
>THanks
>Hari
>MCDBA
>
>
>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
wrote in message
>news:oZ-dncVuSMhJW3DdRVn-uw@.giganews.com...
user processes you can[vbcol=seagreen]
user only:[vbcol=seagreen]
>
>.
>|||Quentin,
Thank you,
-Le

>--Original Message--
>One way would be put the db in single user mode, then
detach the db, and
>then reattach it.
>Quentin
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2a00201c46536$90a1aad0$a301280a@.phx
.gbl...
>
>.
>