managing XML with a GUI

Perhaps I'm missing something, but I'm going through a cert book for the MCTS 70-431 exam and doing the XML examples. All the examples are in T-SQL -- when I go to find the XML Schemas I've registered I can't seem to find a way to do this in SSMS ... is there a way to graphically manage XML Schema documents in SS05?

I have not done much th the xsl side of things but you can view the registerd collections under the programabilty tab and types for each database.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!
Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
These are ways to add ntext, text, or image values to a row:
"Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
"Use the WRITETEXT statement. For more information, see WRITETEXT.
"ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
"OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
"ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
"DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.

Managing XML field with Enterprise Manager

With SQL Server 2000, I have a table with a field called fldhistory, defined
as a ntext [16] field.
This column is intended for storing some archived history data in XML format
.
I have an example XML which is valid and:
1) has <1300 characters, including spaces
2) Wel-formed, readable by IE
3) <10 lines
However, I can't put anything more than say a few hundred charaters in this
column under Enterprise Manager (for testing purposes), the past option is
simply disabled and if I try ctrl-V, I get a Windows warning tone.
why is this and how can I fix this? the ntext column should be capable of
handling >1300 characters!Hello,
Please refer to the following information in SQL server Books Online(BOL):
Topic: Adding ntext, text, or image Data to Inserted Rows
---
These are ways to add ntext, text, or image values to a row:
" Specify relatively short amounts of data in an INSERT statement in the
same way char, nchar, or binary data is.
" Use the WRITETEXT statement. For more information, see WRITETEXT.
" ADO applications can use the AppendChunk method to specify long amounts
of ntext, text, or image data. For more information, see Managing Long Data
Types.
" OLE DB applications can use the ISequentialStream interface to write new
ntext, text, or image values. For more information, see BLOBs and OLE
Objects.
" ODBC applications can use the data-at-execution form of SQLPutData to
write new ntext, text, or image values. For more information, see Managing
text and image Columns.
" DB-Library applications can use the dbwritetext function. For more
information, see Text and Image Functions.
---
You can use above ways to insert ntext data. Please also refer to the
following topics in BOL:
"Using text and image Data"
"Managing ntext, text, and image Data"
"text, ntext, and image Data When text in row Is Set to ON"
You can also refer to the following articles which provide good information:
194975 How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/?id=194975
258038 How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
http://support.microsoft.com/?id=258038
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks
* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>
|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.
|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>
|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in desig
n
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.
4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>> Hi,
>> We have a need to manage about 900 million rows/ year of data of one
>> kind. I am looking for suggestions on how best to design a database
>> table(s)
>> to handle this.
>> These data is essentially information - across various markets and time.
>> We
>> need the ability to search across markets/ time and update across markets
>> and time.
>> I did some basic tests and extrapolating the data would lead me to
>> believe
>> that if I were have this as a single table the table size will be over
>> 100
>> GB.
>> Thanks
>>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:
>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>>Hire a pro to do the design and spec work. To do anything other is to set
>>yourself up for extreme disappointment. The cost now will be a LOT less
>>than in the future when your system is live and non-performant!! :-)
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

managing users

Hello,

Has anyone ever come across a reason why someone would manually create a
user table incl. permission flags and not use the inbuilt user/roles
provided by that database? The only reason that stands out for me is to
make the database that bit more portable?

Thanks,
Craig.Craig, it would depend on exactly how the table in question is
constructed and used but some applications provide application based
security. The application might connect to the database using one ID
that is the database owner or has both datareader and datawriter but
via the application limit what end-users can do.

HTH -- Mark D Powell --|||Craig H. (spam@.thehurley.com) writes:
> Has anyone ever come across a reason why someone would manually create a
> user table incl. permission flags and not use the inbuilt user/roles
> provided by that database? The only reason that stands out for me is to
> make the database that bit more portable?

The database-level can be a bit heavy-handed. In our application, our
security scheme on SQL Server is dead simple. All users are added to
a group, and that group is granted execute access on all stored procedures
and select access on most tables.

Then our database includes tables to control access to application
functions, and also access to which accounts and customers a user may
see.

True, our system started its life in the days of 4.x when the permission
system in SQL Server was far less sophistcated than today. But since
our securable entities are not SQL Server entities, I can't see how
SQL Server could help us, even if we were to make a complete restart.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Managing user-defined properties for report items

[crossposted to MS Community forums as well]
I have defined a user-defined property for several reports. The coding
for GetProperty/SetProperty is straightforward, but it looks as if
there is no management/admin capability through Report Manager or SQL
Management Studio for user-defined properties. Is this correct?
If that capability does not exist, Is there any plan for future
versions of RM/SqlMgtStudio to support getting/setting user-defined
properties?
Thanks!
Regards,
MichaelJust to follow up on this - I've submitted this as a product request to
Microsoft - they've basically postponed any action on it and said
they'll look at it for a future release. *shrug*
Michael

Managing user rights

Hi,
how can I change user right to specific DB? This must be done without stopping and restarting the service! I know this is just a basic thing to do, but I′n newbie so.... And if you are so kind that you answer, can you be quite specific with your answer.
Otherwise my boss will be behind my desk and asking me, why the whole company cant sale any products... ;() Thank You.
Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but In newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Managing user rights

Hi
how can I change user right to specific DB? This must be done without stopping and restarting the service! I know this is just a basic thing to do, but I´n newbie so.... And if you are so kind that you answer, can you be quite specific with your answer. Otherwise my boss will be behind my desk and asking me, why the whole company cant sale any products... ;() Thank You.Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but I´n newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Managing user rights

Hi,
how can I change user right to specific DB? This must be done without stoppi
ng and restarting the service! I know this is just a basic thing to do, but
I′n newbie so.... And if you are so kind that you answer, can you be quite
specific with your answer.
Otherwise my boss will be behind my desk and asking me, why the whole compan
y cant sale any products... ;() Thank You.Hi,
You can use Enterprise manager or syetem stored procedures to change user
rights of a database.
For changing the user rights (Adding database fixed Roles / Grant prev) do
not require a service restart.
Look into the Database Fixed roles and Grant / Revoke / Deny statements in
Books online for more informations on setting previlages to users.
Since you are beginner you can perform this using the enterprise manager --
Security Option or using Enterprise manager --
Expand Databases -- Users to add or remove database fixed roles.
Thanks
Hari
MCDBA
"Atom" <anonymous@.discussions.microsoft.com> wrote in message
news:D83E0E35-F1DE-4255-81D9-8AFB9B5F7CB1@.microsoft.com...
> Hi,
> how can I change user right to specific DB? This must be done without
stopping and restarting the service! I know this is just a basic thing to
do, but In newbie so.... And if you are so kind that you answer, can you
be quite specific with your answer. Otherwise my boss will be behind my desk
and asking me, why the whole company cant sale any products... ;() Thank
You.

Managing Triggers

How can I query to see all of the triggers that have been disabled or
enabled' Is there a way to do this using query analyzer or using a gui tool
?
THanks RichardTry,
select
object_name(parent_obj) as table_name,
[name] as trigger_name
from
sysobjects
where
xtype = 'TR'
and objectproperty([id], 'ExecIsTriggerDisabled') = 1
go
AMB
"Richard" wrote:

> How can I query to see all of the triggers that have been disabled or
> enabled' Is there a way to do this using query analyzer or using a gui to
ol?
> THanks Richard

Managing transactions on dual cores

Hi,

I have a question.

Suppose two "Insert" or "update" instructions are run on the same table ..(in case of "Update" same row ...column).. at the same time....

can the two operations run at the same time on dual core/ quad core processors?

What is the role of dual/quad cores in such case..?

Since these processors can run two or four threads simultaneously... how will the sql server execute the two instructions ie (parallel or interleaved).

Or the Insert , Update or Delete instructions are run one after another just using "interleaving"?

I hope that you understand what i am asking...!

or i just confused you.

With regards,

Girish Pawar

I thinking you're misunderstanding parellelism versus sql transaction. When you start a transaction, the system can use 1 or more thread to perform the work. Depending on the what resource (rows, pages, or table) get locked the second transaction will have to wait until the first transaction release the lock. This works the same whether the system has 1 or more core.|||

what if i say ... WITH NO LOCK

on my stored procedure..?

Are all the commands in sql server executed in a queue...?

so that the machine being multicore has no affect what so ever?

|||

At very very low level it is not possible, all the actions in sql server is a log based. Which ever first open the log file will write the contents there. There is a very minimal lock should be there for any actions.

Note:

If you try with date value to verify this action you may get wrong info.

Check this thread for more details.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630363&SiteID=1

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

Managing Transactions in Stored Procedure (Nested)

Hi Everyone:

I have a master sp that calls 5 different sps. I would like to incorporate transaction(COMMIT and ROLLBACK) into my master sp. If do that, is that enough, or do I need to add some transaction code to the 5 sps that are being called. I would appreciate if you provide me with code, syntax and steps on how to do this for a specific situation like mine. I have read some articles on nested sps and transactions, but most are very complex examples, I just need a simple approach/advise. Thanks.

Rollback and Commit action should apply to your nested stored procedure calls. When you call a stored procedure within a transaction it executes within the context of that transaction. However, be wary about errors from the stored procedures you are calling from the master stored procedure. Review error handling within stored procedures to make sure you are equipt to handle nested errors and apply the proper transaction method.

Managing transactional replication

Hi everybody,

I wish to manage the process of transactional replication between two SQL databases using a c# (or any other possible language, does not differ) written tool. More accurately, I have a database in my server, and I want to have the program to be run from any other computer and does the replication between the local pc(where the program is run) and my server. More to say is that the connection between the server and pc(s) is over the internet.

I would be so thankful if anyone would help me to solve the issue,

with regards

farshad

Hi FArshad.

Look at: http://msdn2.microsoft.com/fr-fr/library/ms146966.aspx

It provides more links to create your publication, subscription and synchronization using RMO (C#).

For Transaction replication, you cannot just use Internet to do your synchronization. You would need to have VPN established. However you dont need VPN and can just synchronization over the Internet using Merge replication. If you are interested, search for eb Synchronization on msdn.

Hope that helps.

|||

Hi Mahesh and thanks for answering,

The exact thing I'm looking for is a way to start the replication in a subscriber machine; where replication is transactional and subscriber can be any machine that has sql server 2000 installed and has my database and its schema configured. Because I will have new entries in database and subscribers must not change their own databases, and also to save time, I'm going to use transactional replication. For now, I know how to generate snapshot and then synchronize the subscriber with publisher's data, but as you know, after a while when database becomes larger, generating snapshot would take a lot of time, so I'm searching a better solution. Is there any way to do so ?

Cheers,

farshad

|||

If I understand right you are saying that you do not want the snapshot to be donloaded but start the subscriber with a database and data.

If so search for 'Initialize from Backup' in BOL.

|||

Thanks Mahesh,

I think I didn't say what I meant, of course this was a good help, but not exactly the solution to my problem. Thanks a lot again,

cheers

farshad

managing Transaction Log file

Hi,
My problem is dealing with the size of Transaction log
file of our main database. It's very huge like 14GB. I
have tried the backing the log file up then DBCC
SHRINKFILE command on the log file. It's still the same
size I started out with. So far whatever I've done didn't
make difference on the size of the log file. Is there an
effective procedure to deal with this issue?
Thank you in advance for your help.
OryFirst, it's important to understand how transactions are
entered into the transaction log file. The log file is
essentially a circular queue. It wraps around. Think of
it as a donut. Within the log file there is a marker that
points to the active portion of the log. That marker
could be at the beginning of the file, in the middle, or
at the end. If the active portion of the log is at the
end of the file, SQL Server will not allow you shrink the
file. Somehow, we have to move that marker to the
beginning of the file so that we can successfully shrink
the file.
It is a very good KB article on this subject. The article
number is Q256650(How to Shrink the SQL Server 7.0
Transaction Log). You can do a search on www.microsoft.com
for this article. This article has a nice discussion on
various reasons why your attempts to shrink a log file
might not succeed. One of the reasons discussed is the
one I described above. The article provides a method for
identifying the problem and the solution to the problem in
the form of a Transact-SQL script. Identification of the
problem is accomplished by running a DBCC command called
LOGINFO. The syntax is DBCC LOGINFO (database_name). The
article describes what to look for in the output. The
script provided in the article for resolving the problem
does the following, in general terms:
. It creates a dummy table in the database for which
you are trying to shrink the log file.
. It then inserts a bunch of rows into this dummy
table. This has the effect of forcing the pointer to the
active portion of the log to wrap around to the beginning
of the log file.
. It then shrinks the log file to the size you want.
. Then it drops the dummy table.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi,
>My problem is dealing with the size of Transaction log
>file of our main database. It's very huge like 14GB. I
>have tried the backing the log file up then DBCC
>SHRINKFILE command on the log file. It's still the same
>size I started out with. So far whatever I've done didn't
>make difference on the size of the log file. Is there an
>effective procedure to deal with this issue?
>Thank you in advance for your help.
> Ory
>.
>|||Refer to whichever applies to your version of SQL Server:
INF: How to Shrink the SQL Server 7.0 Transaction Log
(Q256650)
http://support.microsoft.com/?id=256650
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
wrote:
>Hi,
>My problem is dealing with the size of Transaction log
>file of our main database. It's very huge like 14GB. I
>have tried the backing the log file up then DBCC
>SHRINKFILE command on the log file. It's still the same
>size I started out with. So far whatever I've done didn't
>make difference on the size of the log file. Is there an
>effective procedure to deal with this issue?
>Thank you in advance for your help.
> Ory|||Thanks Sue but I had tried the step in that article but
didn't resolve my problem. So I tried the easiest method
in the KB which is to detach from the database then delete
the log file (or rename) in the server then use
sp_attach_using_single_file to the database name and
physical path of the data file. Finally it creates a new
transaction log file. Sums it up for me.
>--Original Message--
>Refer to whichever applies to your version of SQL Server:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>(Q256650)
>http://support.microsoft.com/?id=256650
>INF: Shrinking the Transaction Log in SQL Server 2000 with
>DBCC SHRINKFILE
>http://support.microsoft.com/?id=272318
>-Sue
>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>.
>|||Thank you for your useful answer, I've ran the
shrink_database script in the article but it didn't shrink
the log file still. So I tried the easiesst method in the
book to detach the database then delete the log file then
db_attach_using_single_file pointing at the physical file
path and logical db name. This worked well because it
created a new minimum sized log file (or empty one you may
think as). I realize I need to have a more permanent
solution than this. This is not an ideal dba procedure.
Thank you again. Bye now:). Ory.
>--Original Message--
>First, it's important to understand how transactions are
>entered into the transaction log file. The log file is
>essentially a circular queue. It wraps around. Think of
>it as a donut. Within the log file there is a marker
that
>points to the active portion of the log. That marker
>could be at the beginning of the file, in the middle, or
>at the end. If the active portion of the log is at the
>end of the file, SQL Server will not allow you shrink the
>file. Somehow, we have to move that marker to the
>beginning of the file so that we can successfully shrink
>the file.
>It is a very good KB article on this subject. The
article
>number is Q256650(How to Shrink the SQL Server 7.0
>Transaction Log). You can do a search on
www.microsoft.com
>for this article. This article has a nice discussion on
>various reasons why your attempts to shrink a log file
>might not succeed. One of the reasons discussed is the
>one I described above. The article provides a method for
>identifying the problem and the solution to the problem
in
>the form of a Transact-SQL script. Identification of the
>problem is accomplished by running a DBCC command called
>LOGINFO. The syntax is DBCC LOGINFO (database_name).
The
>article describes what to look for in the output. The
>script provided in the article for resolving the problem
>does the following, in general terms:
>.. It creates a dummy table in the database for which
>you are trying to shrink the log file.
>.. It then inserts a bunch of rows into this dummy
>table. This has the effect of forcing the pointer to the
>active portion of the log to wrap around to the beginning
>of the log file.
>.. It then shrinks the log file to the size you want.
>.. Then it drops the dummy table.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>>--Original Message--
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>.
>|||I don't remember those articles or the KB recommending this
approach for shrinking a log file. As dbcc shrinkfile is a
bit different on SQL 7 and SQL 2000, it's hard to say what
the problem could be as you didn't post the version of SQL
Server. On SQL 7, it's a deferred operation so that can play
a part in what you experienced.
-Sue
On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
wrote:
>Thanks Sue but I had tried the step in that article but
>didn't resolve my problem. So I tried the easiest method
>in the KB which is to detach from the database then delete
>the log file (or rename) in the server then use
>sp_attach_using_single_file to the database name and
>physical path of the data file. Finally it creates a new
>transaction log file. Sums it up for me.
>
>>--Original Message--
>>Refer to whichever applies to your version of SQL Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000 with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the same
>>size I started out with. So far whatever I've done
>didn't
>>make difference on the size of the log file. Is there an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.|||Consider yourself lucky you didn't get a corrupt/suspect database! :-)
(I do understand that you did a database backup first.)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ory" <Ory@.nomail.org> wrote in message news:0a7c01c391e5$45c6f170$a301280a@.phx.gbl...
> Thanks Sue but I had tried the step in that article but
> didn't resolve my problem. So I tried the easiest method
> in the KB which is to detach from the database then delete
> the log file (or rename) in the server then use
> sp_attach_using_single_file to the database name and
> physical path of the data file. Finally it creates a new
> transaction log file. Sums it up for me.
>
> >--Original Message--
> >Refer to whichever applies to your version of SQL Server:
> >
> >INF: How to Shrink the SQL Server 7.0 Transaction Log
> >(Q256650)
> >http://support.microsoft.com/?id=256650
> >
> >INF: Shrinking the Transaction Log in SQL Server 2000 with
> >DBCC SHRINKFILE
> >http://support.microsoft.com/?id=272318
> >
> >-Sue
> >
> >On Mon, 13 Oct 2003 13:00:13 -0700, "Ory" <Ory@.nomail.org>
> >wrote:
> >
> >>Hi,
> >>
> >>My problem is dealing with the size of Transaction log
> >>file of our main database. It's very huge like 14GB. I
> >>have tried the backing the log file up then DBCC
> >>SHRINKFILE command on the log file. It's still the same
> >>size I started out with. So far whatever I've done
> didn't
> >>make difference on the size of the log file. Is there an
> >>effective procedure to deal with this issue?
> >>
> >>Thank you in advance for your help.
> >>
> >> Ory
> >
> >.
> >|||Hi Sue,
I'm using SQL Server 2000. You're right that it wasn't in
KB the article it was
in //evolvedcode.net/content/code_sqllogshrink/ . The
format that I used for shrinking before that didn't work
was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in Query
Analyzer. I get a result set on Actual and estimated size
of the file which doen't help much. Anyways, thank you for
your reply. I post a message as a developer/dba_to_be
every two to three months or so.
Bye.
>--Original Message--
>I don't remember those articles or the KB recommending
this
>approach for shrinking a log file. As dbcc shrinkfile is a
>bit different on SQL 7 and SQL 2000, it's hard to say what
>the problem could be as you didn't post the version of SQL
>Server. On SQL 7, it's a deferred operation so that can
play
>a part in what you experienced.
>-Sue
>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
>wrote:
>>Thanks Sue but I had tried the step in that article but
>>didn't resolve my problem. So I tried the easiest method
>>in the KB which is to detach from the database then
delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
<Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is there
an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>.
>|||There are plenty of posts on sites about deleting logs,
rebuilding logs, etc. It's just a totally bad practice - it
risks the ability to maintain transactional consistency in
your database. Unfortunately, someone follows the advice,
doesn't see any harmful affects and then posts the solution
again. But there are plenty of people who have basically
ruined their databases by doing this. It may not even be
noticeable immediately. You could be in a situation where
you have a problem, can solve it using methods that don't
harm your database but you loose the ability to do this by
running some of these scripts. The transaction log is a
critically important piece of your database system and
that's something people seem to lose sight of sometimes.
-Sue
On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
<ory@.nomail.nospam.org> wrote:
>Hi Sue,
>I'm using SQL Server 2000. You're right that it wasn't in
>KB the article it was
>in //evolvedcode.net/content/code_sqllogshrink/ . The
>format that I used for shrinking before that didn't work
>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in Query
>Analyzer. I get a result set on Actual and estimated size
>of the file which doen't help much. Anyways, thank you for
>your reply. I post a message as a developer/dba_to_be
>every two to three months or so.
>Bye.
>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>this
>>approach for shrinking a log file. As dbcc shrinkfile is a
>>bit different on SQL 7 and SQL 2000, it's hard to say what
>>the problem could be as you didn't post the version of SQL
>>Server. On SQL 7, it's a deferred operation so that can
>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory" <Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article but
>>didn't resolve my problem. So I tried the easiest method
>>in the KB which is to detach from the database then
>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
><Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction log
>>file of our main database. It's very huge like 14GB. I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
>same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is there
>an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>>
>>.|||(MRS OR MS) Hoegemeier,
You're chewing me up?. Believe me I tried scripts and
commands from Microsoft sites mostly. Which I didn't get
anywhere. I don't have comfort of long time for these
problems I have to go on with running our application
scripts against the database. However, I acknowledge that
this is just a temporary solution. More I become
knowledgable more I will implement more permanent and
sustaining solutions. I'm below your experience and know-
how in SQL Server but in Real World shortcuts and relative
quick fixes keeps running the IT departments. And also
keep the problem solver image. That's all.
*\Ory
>--Original Message--
>There are plenty of posts on sites about deleting logs,
>rebuilding logs, etc. It's just a totally bad practice -
it
>risks the ability to maintain transactional consistency in
>your database. Unfortunately, someone follows the advice,
>doesn't see any harmful affects and then posts the
solution
>again. But there are plenty of people who have basically
>ruined their databases by doing this. It may not even be
>noticeable immediately. You could be in a situation where
>you have a problem, can solve it using methods that don't
>harm your database but you loose the ability to do this by
>running some of these scripts. The transaction log is a
>critically important piece of your database system and
>that's something people seem to lose sight of sometimes.
>-Sue
>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
Query
>>Analyzer. I get a result set on Actual and estimated
size
>>of the file which doen't help much. Anyways, thank you
for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
what
>>the problem could be as you didn't post the version of
SQL
>>Server. On SQL 7, it's a deferred operation so that can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
<Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
but
>>didn't resolve my problem. So I tried the easiest
method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>Hi,
>>My problem is dealing with the size of Transaction
log
>>file of our main database. It's very huge like 14GB.
I
>>have tried the backing the log file up then DBCC
>>SHRINKFILE command on the log file. It's still the
>>same
>>size I started out with. So far whatever I've done
>>didn't
>>make difference on the size of the log file. Is
there
>>an
>>effective procedure to deal with this issue?
>>Thank you in advance for your help.
>> Ory
>>.
>>
>>.
>.
>|||No, no...not chewing you up at all. Just the opposite - I
was just trying to warn you about using some of those
scripts. Of all the scripts I've seen, I think some of the
log scripts (as well some of the ones that mess with system
tables) are just the totally wrong solutions to the problem.
All too often I have seen where these "shortcuts" really
don't add up to any time, money, business saved and often
cause more problems than the original problem they were
meant to solve. I've seen places use the shortcuts and then
have problems with the database for weeks, downtime, data
loss etc. I really do understand what you are saying and
I've certainly been in the position where I have been told
to do things that are totally wrong. But they don't keep
things running sometimes as much as they keep us in crisis
mode, working later for days, etc. And a lot of times it's
just people who don't know any better who are going to
insist anyway. But it's important for you to know what's
right and what's wrong. If you warn them and they insist
(and they sign your paycheck) then often there isn't much
you can do. But if you know and you warn them, at least you
did what's right. The worse part is when we think some of
the bad practices are really the way it's suppose to be
done. But if you know better and take the time to understand
you can be in a better position to help clean up the mess.
That's why I was warning you about going down that road...
-Sue
On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
<ory@.nospam.please> wrote:
>(MRS OR MS) Hoegemeier,
>You're chewing me up?. Believe me I tried scripts and
>commands from Microsoft sites mostly. Which I didn't get
>anywhere. I don't have comfort of long time for these
>problems I have to go on with running our application
>scripts against the database. However, I acknowledge that
>this is just a temporary solution. More I become
>knowledgable more I will implement more permanent and
>sustaining solutions. I'm below your experience and know-
>how in SQL Server but in Real World shortcuts and relative
>quick fixes keeps running the IT departments. And also
>keep the problem solver image. That's all.
> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>it
>>risks the ability to maintain transactional consistency in
>>your database. Unfortunately, someone follows the advice,
>>doesn't see any harmful affects and then posts the
>solution
>>again. But there are plenty of people who have basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation where
>>you have a problem, can solve it using methods that don't
>>harm your database but you loose the ability to do this by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>Query
>>Analyzer. I get a result set on Actual and estimated
>size
>>of the file which doen't help much. Anyways, thank you
>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>what
>>the problem could be as you didn't post the version of
>SQL
>>Server. On SQL 7, it's a deferred operation so that can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
><Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
>but
>>didn't resolve my problem. So I tried the easiest
>method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
>new
>>transaction log file. Sums it up for me.
>>
>>--Original Message--
>>Refer to whichever applies to your version of SQL
>>Server:
>>INF: How to Shrink the SQL Server 7.0 Transaction Log
>>(Q256650)
>>http://support.microsoft.com/?id=256650
>>INF: Shrinking the Transaction Log in SQL Server 2000
>>with
>>DBCC SHRINKFILE
>>http://support.microsoft.com/?id=272318
>>-Sue
>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>log
>>>file of our main database. It's very huge like 14GB.
>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>didn't
>>>make difference on the size of the log file. Is
>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>.
>>
>>.
>>
>>.|||And with a last name like that, Sue is very much okay by me.
It's worse to pronounce than it is to type so you were lucky
you only had to type it!
-Sue
On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
<ory@.nospam.please> wrote:
>MRS OR MS) Hoegemeier,|||I see :). It was good having a frank exchange with a Lady
of significant professional experience. Your warnings and
opinions are appreciated. Thank you.
PS. Would you tell me where you work (company)? Just
curious.
>--Original Message--
>No, no...not chewing you up at all. Just the opposite - I
>was just trying to warn you about using some of those
>scripts. Of all the scripts I've seen, I think some of the
>log scripts (as well some of the ones that mess with
system
>tables) are just the totally wrong solutions to the
problem.
>All too often I have seen where these "shortcuts" really
>don't add up to any time, money, business saved and often
>cause more problems than the original problem they were
>meant to solve. I've seen places use the shortcuts and
then
>have problems with the database for weeks, downtime, data
>loss etc. I really do understand what you are saying and
>I've certainly been in the position where I have been told
>to do things that are totally wrong. But they don't keep
>things running sometimes as much as they keep us in crisis
>mode, working later for days, etc. And a lot of times it's
>just people who don't know any better who are going to
>insist anyway. But it's important for you to know what's
>right and what's wrong. If you warn them and they insist
>(and they sign your paycheck) then often there isn't much
>you can do. But if you know and you warn them, at least
you
>did what's right. The worse part is when we think some of
>the bad practices are really the way it's suppose to be
>done. But if you know better and take the time to
understand
>you can be in a better position to help clean up the mess.
>That's why I was warning you about going down that road...
>-Sue
>On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
><ory@.nospam.please> wrote:
>>(MRS OR MS) Hoegemeier,
>>You're chewing me up?. Believe me I tried scripts and
>>commands from Microsoft sites mostly. Which I didn't get
>>anywhere. I don't have comfort of long time for these
>>problems I have to go on with running our application
>>scripts against the database. However, I acknowledge
that
>>this is just a temporary solution. More I become
>>knowledgable more I will implement more permanent and
>>sustaining solutions. I'm below your experience and know-
>>how in SQL Server but in Real World shortcuts and
relative
>>quick fixes keeps running the IT departments. And also
>>keep the problem solver image. That's all.
>> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>>it
>>risks the ability to maintain transactional consistency
in
>>your database. Unfortunately, someone follows the
advice,
>>doesn't see any harmful affects and then posts the
>>solution
>>again. But there are plenty of people who have
basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation
where
>>you have a problem, can solve it using methods that
don't
>>harm your database but you loose the ability to do this
by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't
work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>>Query
>>Analyzer. I get a result set on Actual and estimated
>>size
>>of the file which doen't help much. Anyways, thank you
>>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB
recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>>what
>>the problem could be as you didn't post the version
of
>>SQL
>>Server. On SQL 7, it's a deferred operation so that
can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>Thanks Sue but I had tried the step in that article
>>but
>>didn't resolve my problem. So I tried the easiest
>>method
>>in the KB which is to detach from the database then
>>delete
>>the log file (or rename) in the server then use
>>sp_attach_using_single_file to the database name and
>>physical path of the data file. Finally it creates a
>>new
>>transaction log file. Sums it up for me.
>>
>>>--Original Message--
>>>Refer to whichever applies to your version of SQL
>>Server:
>>>
>>>INF: How to Shrink the SQL Server 7.0 Transaction
Log
>>>(Q256650)
>>>http://support.microsoft.com/?id=256650
>>>
>>>INF: Shrinking the Transaction Log in SQL Server
2000
>>with
>>>DBCC SHRINKFILE
>>>http://support.microsoft.com/?id=272318
>>>
>>>-Sue
>>>
>>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>>wrote:
>>>
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>>log
>>>file of our main database. It's very huge like
14GB.
>>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>didn't
>>>make difference on the size of the log file. Is
>>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>>
>>>.
>>>
>>.
>>
>>.
>.
>|||If I told you after posting these things, I'd probably never
get a job again! But I've worked at a lot of different
places actually. I think many places experience the same
problems we've discussed.
-Sue
On Tue, 14 Oct 2003 16:45:25 -0700, "Ory"
<anonymous@.discussions.microsoft.com> wrote:
>I see :). It was good having a frank exchange with a Lady
>of significant professional experience. Your warnings and
>opinions are appreciated. Thank you.
>PS. Would you tell me where you work (company)? Just
>curious.
>
>>--Original Message--
>>No, no...not chewing you up at all. Just the opposite - I
>>was just trying to warn you about using some of those
>>scripts. Of all the scripts I've seen, I think some of the
>>log scripts (as well some of the ones that mess with
>system
>>tables) are just the totally wrong solutions to the
>problem.
>>All too often I have seen where these "shortcuts" really
>>don't add up to any time, money, business saved and often
>>cause more problems than the original problem they were
>>meant to solve. I've seen places use the shortcuts and
>then
>>have problems with the database for weeks, downtime, data
>>loss etc. I really do understand what you are saying and
>>I've certainly been in the position where I have been told
>>to do things that are totally wrong. But they don't keep
>>things running sometimes as much as they keep us in crisis
>>mode, working later for days, etc. And a lot of times it's
>>just people who don't know any better who are going to
>>insist anyway. But it's important for you to know what's
>>right and what's wrong. If you warn them and they insist
>>(and they sign your paycheck) then often there isn't much
>>you can do. But if you know and you warn them, at least
>you
>>did what's right. The worse part is when we think some of
>>the bad practices are really the way it's suppose to be
>>done. But if you know better and take the time to
>understand
>>you can be in a better position to help clean up the mess.
>>That's why I was warning you about going down that road...
>>-Sue
>>On Tue, 14 Oct 2003 14:48:06 -0700, "Ory"
>><ory@.nospam.please> wrote:
>>(MRS OR MS) Hoegemeier,
>>You're chewing me up?. Believe me I tried scripts and
>>commands from Microsoft sites mostly. Which I didn't get
>>anywhere. I don't have comfort of long time for these
>>problems I have to go on with running our application
>>scripts against the database. However, I acknowledge
>that
>>this is just a temporary solution. More I become
>>knowledgable more I will implement more permanent and
>>sustaining solutions. I'm below your experience and know-
>>how in SQL Server but in Real World shortcuts and
>relative
>>quick fixes keeps running the IT departments. And also
>>keep the problem solver image. That's all.
>> *\Ory
>>--Original Message--
>>There are plenty of posts on sites about deleting logs,
>>rebuilding logs, etc. It's just a totally bad practice -
>>it
>>risks the ability to maintain transactional consistency
>in
>>your database. Unfortunately, someone follows the
>advice,
>>doesn't see any harmful affects and then posts the
>>solution
>>again. But there are plenty of people who have
>basically
>>ruined their databases by doing this. It may not even be
>>noticeable immediately. You could be in a situation
>where
>>you have a problem, can solve it using methods that
>don't
>>harm your database but you loose the ability to do this
>by
>>running some of these scripts. The transaction log is a
>>critically important piece of your database system and
>>that's something people seem to lose sight of sometimes.
>>-Sue
>>On Tue, 14 Oct 2003 11:58:52 -0700, "Ory"
>><ory@.nomail.nospam.org> wrote:
>>Hi Sue,
>>I'm using SQL Server 2000. You're right that it wasn't
>>in
>>KB the article it was
>>in //evolvedcode.net/content/code_sqllogshrink/ . The
>>format that I used for shrinking before that didn't
>work
>>was DBCC SHRINKFILE (xxx.log, target_size_in_MB) in
>>Query
>>Analyzer. I get a result set on Actual and estimated
>>size
>>of the file which doen't help much. Anyways, thank you
>>for
>>your reply. I post a message as a developer/dba_to_be
>>every two to three months or so.
>>Bye.
>>
>>--Original Message--
>>I don't remember those articles or the KB
>recommending
>>this
>>approach for shrinking a log file. As dbcc shrinkfile
>>is a
>>bit different on SQL 7 and SQL 2000, it's hard to say
>>what
>>the problem could be as you didn't post the version
>of
>>SQL
>>Server. On SQL 7, it's a deferred operation so that
>can
>>play
>>a part in what you experienced.
>>-Sue
>>On Mon, 13 Oct 2003 16:54:00 -0700, "Ory"
>><Ory@.nomail.org>
>>wrote:
>>>Thanks Sue but I had tried the step in that article
>>but
>>>didn't resolve my problem. So I tried the easiest
>>method
>>>in the KB which is to detach from the database then
>>delete
>>>the log file (or rename) in the server then use
>>>sp_attach_using_single_file to the database name and
>>>physical path of the data file. Finally it creates a
>>new
>>>transaction log file. Sums it up for me.
>>>
>>>
>>>--Original Message--
>>>Refer to whichever applies to your version of SQL
>>Server:
>>>
>>>INF: How to Shrink the SQL Server 7.0 Transaction
>Log
>>>(Q256650)
>>>http://support.microsoft.com/?id=256650
>>>
>>>INF: Shrinking the Transaction Log in SQL Server
>2000
>>with
>>>DBCC SHRINKFILE
>>>http://support.microsoft.com/?id=272318
>>>
>>>-Sue
>>>
>>>On Mon, 13 Oct 2003 13:00:13 -0700, "Ory"
>><Ory@.nomail.org>
>>>wrote:
>>>
>>>Hi,
>>>
>>>My problem is dealing with the size of Transaction
>>log
>>>file of our main database. It's very huge like
>14GB.
>>I
>>>have tried the backing the log file up then DBCC
>>>SHRINKFILE command on the log file. It's still the
>>same
>>>size I started out with. So far whatever I've done
>>>didn't
>>>make difference on the size of the log file. Is
>>there
>>an
>>>effective procedure to deal with this issue?
>>>
>>>Thank you in advance for your help.
>>>
>>> Ory
>>>
>>>.
>>>
>>.
>>
>>.
>>
>>.|||Dear Sue, or other friend:
I've read all your talks above, and want to say that I am just in that
trouble. My database log file's size is over 1.2G, and I detached it,
and delete the log file, but when I want to attach the mdf file, it
shows 'Error 1813, could not open new database, CREATE DATABASE is
abort, Device activation error, the physical file name xxx may be
incorrect.' The server version is 2000, database size is just 300M.
How can I do?
Thanks in advanced.
Wing1993
Posted via http://dbforums.com|||This is the very reason why folks shouldn't recommend deleting log files. I suggest you restore the
database from the backup I take it you did before this operation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"wing1993" <member44167@.dbforums.com> wrote in message news:3482440.1066189506@.dbforums.com...
> Dear Sue, or other friend:
>
> I've read all your talks above, and want to say that I am just in that
> trouble. My database log file's size is over 1.2G, and I detached it,
> and delete the log file, but when I want to attach the mdf file, it
> shows 'Error 1813, could not open new database, CREATE DATABASE is
> abort, Device activation error, the physical file name xxx may be
> incorrect.' The server version is 2000, database size is just 300M.
>
> How can I do?
> Thanks in advanced.
>
> Wing1993
>
> --
> Posted via http://dbforums.com|||All,
If I still want to use this updated mdf file to restore the database,
how can i do, in fact, the last backup is over 3 days older than the bad
file. I don't want to loss the data.
But I will bear in mind that next time I shall backup database and log
file to truncate the long transaction file.
Wing1993
Posted via http://dbforums.com

managing the transaction log

Hello!

I'm working with an SQL database that someone else has set up and this is a learning experience for me.

I understand what the transaction log is and a little about it.

What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!

What is the best way to dump the old data?

thanks in advance?

RIMQ1 [i would like to do is shrink it because it is full]?

A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)

General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:

link (http://dbforums.com/showthread.php?s=&threadid=546372)

managing the same database in multiple places

I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
is this - I have a production database on a server. Occasionally, I need to
have an exact duplicate of this production database on my laptop. What I do
in this case is stop the SQLSERVER service, make a physical copy of the mdf
and log files, then copy those files over to my laptop and attach database.
Subsequent to that, I just transparently replace the folder without
attaching, and usually the server is none the wiser and everything works
fine.
However, a recurring problem I have is that logins and users don't seem to
match up with each other. I have a login 'test' and a user within my
database 'test'. Sometimes when I do this manual copy, it works without a
hitch. Other times, it tells me that access is denied for 'test' user. I
go into the Security folder on EM and open the login and sure enough, it
doesn't have my database checked under the "Database Access" tab. When I
check the database to give the login access, it gives me an "Error 21002:
[SQL-DMO]User 'test' already exists.
So two questions:
First, how do I resolve this specific problem with logins and users not
matching up short of deleting them and recreating them and having to
rescript the permissions from my server to my laptop?
Second, I'm sure the way I'm copying the files manually, having to stop the
server and all is a pretty kludgy way of doing it. Is there a better way to
duplicate a database from my server to my laptop which won't incur this
login/user communication problem (not including replication)?(1) If the logins are SQL Server logins, you should be able to use =sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a =database to a file and then restore the database from that file without =shutting down the server. There is much documentation on these topics =within Books Online as well.
-- Keith
"James" <capricorn@.nospam.com> wrote in message =news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My =situation
> is this - I have a production database on a server. Occasionally, I =need to
> have an exact duplicate of this production database on my laptop. =What I do
> in this case is stop the SQLSERVER service, make a physical copy of =the mdf
> and log files, then copy those files over to my laptop and attach =database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything =works
> fine.
> > However, a recurring problem I have is that logins and users don't =seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works =without a
> hitch. Other times, it tells me that access is denied for 'test' =user. I
> go into the Security folder on EM and open the login and sure enough, =it
> doesn't have my database checked under the "Database Access" tab. =When I
> check the database to give the login access, it gives me an "Error =21002:
> [SQL-DMO]User 'test' already exists.
> > So two questions:
> First, how do I resolve this specific problem with logins and users =not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> > Second, I'm sure the way I'm copying the files manually, having to =stop the
> server and all is a pretty kludgy way of doing it. Is there a better =way to
> duplicate a database from my server to my laptop which won't incur =this
> login/user communication problem (not including replication)?
> > >|||Keith,
Thanks for pointing me in the right direction. I happen to have figured out
issue 1 just after I posted the question. sp_change_users_login
'Update_One', 'test', 'test' did the trick.
I am also aware of backup and restore, but I haven't quite figured out how
to restore from a network drive location even after looking through the
books online. It says to use DISK if the location is a redirected drive,
but I keep getting a Device Offline error. I also can't figure out the
syntax for backing up to a remote folder as I can't get it to add the
device, or at least it won't recognize it. I try using paths such as
'\\servername\d$\backup' but it doesn't see these. Can you suggest a sample
syntax for how I can backup and restore to network locations? Do I have to
map the drives before I can use them as devices?
Thanks,
James
"Keith Kratochvil" <sqlguy@.comcast.net> wrote in message
news:%23S1hPXCjDHA.1940@.TK2MSFTNGP09.phx.gbl...
(1) If the logins are SQL Server logins, you should be able to use
sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a
database to a file and then restore the database from that file without
shutting down the server. There is much documentation on these topics
within Books Online as well.
--
Keith
"James" <capricorn@.nospam.com> wrote in message
news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
> is this - I have a production database on a server. Occasionally, I need
to
> have an exact duplicate of this production database on my laptop. What I
do
> in this case is stop the SQLSERVER service, make a physical copy of the
mdf
> and log files, then copy those files over to my laptop and attach
database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything works
> fine.
> However, a recurring problem I have is that logins and users don't seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works without a
> hitch. Other times, it tells me that access is denied for 'test' user. I
> go into the Security folder on EM and open the login and sure enough, it
> doesn't have my database checked under the "Database Access" tab. When I
> check the database to give the login access, it gives me an "Error 21002:
> [SQL-DMO]User 'test' already exists.
> So two questions:
> First, how do I resolve this specific problem with logins and users not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> Second, I'm sure the way I'm copying the files manually, having to stop
the
> server and all is a pretty kludgy way of doing it. Is there a better way
to
> duplicate a database from my server to my laptop which won't incur this
> login/user communication problem (not including replication)?
>
>|||Hi James,
Thanks for Keith's help. To backup up a database to a remote folder and
restore it, please try to perform the following article.
1. Share a folder on the remote machine.
2. Perform these codes using Query Analyzer on your original instance:
-- Create a logical backup device for the full EXCHANGETEST1 backup.
EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1',
'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
-- Back up the full EXCHANGETEXT1 database.
BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
3. Perform these codes using Query Analyzer on the remote instance to
restore the database EXCHANGETEST1.
--Restore the EXCHANGETEXT1 database.
RESTORE DATABASE EXCHANGETEST1 FROM
DISK='\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
WITH RECOVERY,
MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_data.mdf',
MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_log.ldf'
It works on my side. Does it work on your machine? Please feel free to let
me know if this solves your problem or if you would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||It is important to note that the account that SQL Server (and SQL Server =Agent) runs under has permissions to the share. If the account has the =appropriate permissions, it is also possible to backup to an admin share =(example: \\Machine\c$)
You can backup directly to the share/admin share with the backup command =(without adding the backup device):
BACKUP DATABASE master TO DISK =3D '\\SomeOtherMachine\c$\master.bak' =WITH INIT
-- Keith
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message =news:wrUppOLjDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi James,
> > Thanks for Keith's help. To backup up a database to a remote folder =and > restore it, please try to perform the following article. > > 1. Share a folder on the remote machine.
> > 2. Perform these codes using Query Analyzer on your original instance:
> > -- Create a logical backup device for the full EXCHANGETEST1 backup.
> > EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1', > '\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> > -- Back up the full EXCHANGETEXT1 database.
> BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
> > 3. Perform these codes using Query Analyzer on the remote instance to > restore the database EXCHANGETEST1.
> > --Restore the EXCHANGETEXT1 database.
> > RESTORE DATABASE EXCHANGETEST1 FROM > DISK=3D'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> WITH RECOVERY,
> MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_data.mdf', > MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_log.ldf'
> > It works on my side. Does it work on your machine? Please feel free to =let > me know if this solves your problem or if you would like further =assistance.
> > Regards, > > Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no =rights.
>