correct way to backup?

hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"rtn" wrote:

> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a file
s
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it back
up
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new databas
e
> (empty) by the same name in a different machine and then restore it from t
he
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:

> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get
.
> Document the process and any extra recovery steps you figure out. You don
't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:

> Look up sp_change_users_login in BOL to relink users and logins. The user
s
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environme
nt
> to replace a failed server? That is what you have to document. Restoring
a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>

correct way to backup?

hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> > hi,
> >
> > to backup my sql server 2000 databases, I right click on the database name
> > (from enterprise mgr),select the backup database option, choose 'database
> > complete' , and then add a filename as the destination. I backup to a
> > files
> > on the harddisk and then backs up those files to a tape.
> >
> > Is this a good backup process? I mean, the way I am doing it, will it
> > backup
> > the complete database, the users on that database, etc? And will I have
> > problems while restoring? By restoring, I mean I will create a new
> > database
> > (empty) by the same name in a different machine and then restore it from
> > the
> > backup file.Will the users in that database also get restored?
> >
> > Your suggestions are very much appreciated.
> >
> > thanks!!
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>> This is a good start. You need to automate the process somehow. The
>> database maintenance wizard offers some help or you can write scripts and
>> use the SQL agent to schedule them. The most important thing is to test
>> your recovery process. Restore the databases somewhere. See what you
>> get.
>> Document the process and any extra recovery steps you figure out. You
>> don't
>> want to be figuring this out under the time crunch of a down system.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "rtn" <rtn@.discussions.microsoft.com> wrote in message
>> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>> > hi,
>> >
>> > to backup my sql server 2000 databases, I right click on the database
>> > name
>> > (from enterprise mgr),select the backup database option, choose
>> > 'database
>> > complete' , and then add a filename as the destination. I backup to a
>> > files
>> > on the harddisk and then backs up those files to a tape.
>> >
>> > Is this a good backup process? I mean, the way I am doing it, will it
>> > backup
>> > the complete database, the users on that database, etc? And will I have
>> > problems while restoring? By restoring, I mean I will create a new
>> > database
>> > (empty) by the same name in a different machine and then restore it
>> > from
>> > the
>> > backup file.Will the users in that database also get restored?
>> >
>> > Your suggestions are very much appreciated.
>> >
>> > thanks!!
>>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> > thanks for your reply.
> >
> > I tried a test recovery. All goes well, expect the fact that users that
> > are
> > present in the original database does not get restored. I think everything
> > except the users get restored.
> >
> > any suggestions on this would be greatly appreciated.
> >
> > thanks!
> >
> > "Geoff N. Hiten" wrote:
> >
> >> This is a good start. You need to automate the process somehow. The
> >> database maintenance wizard offers some help or you can write scripts and
> >> use the SQL agent to schedule them. The most important thing is to test
> >> your recovery process. Restore the databases somewhere. See what you
> >> get.
> >> Document the process and any extra recovery steps you figure out. You
> >> don't
> >> want to be figuring this out under the time crunch of a down system.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >>
> >> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> >> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> >> > hi,
> >> >
> >> > to backup my sql server 2000 databases, I right click on the database
> >> > name
> >> > (from enterprise mgr),select the backup database option, choose
> >> > 'database
> >> > complete' , and then add a filename as the destination. I backup to a
> >> > files
> >> > on the harddisk and then backs up those files to a tape.
> >> >
> >> > Is this a good backup process? I mean, the way I am doing it, will it
> >> > backup
> >> > the complete database, the users on that database, etc? And will I have
> >> > problems while restoring? By restoring, I mean I will create a new
> >> > database
> >> > (empty) by the same name in a different machine and then restore it
> >> > from
> >> > the
> >> > backup file.Will the users in that database also get restored?
> >> >
> >> > Your suggestions are very much appreciated.
> >> >
> >> > thanks!!
> >>
> >>
> >>
>
>sql

correct way to backup?

hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"rtn" wrote:

> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:

> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>
|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:

> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>

correct syntax for this select in SQL Server?

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.

But Enterprise Manager barfs at the final bracket. Can anyone help
please?

select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)

In fact, I can reduce it to :-

select * from
(SELECT * from test)

with the same effect - clearly I just need telling :-)

cheers,
Jim
--
Jim
a Yorkshire polymothJim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.
>
But Enterprise Manager barfs at the final bracket. Can anyone help
please?
>
select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)
>
In fact, I can reduce it to :-
>
select * from
(SELECT * from test)
>
with the same effect - clearly I just need telling :-)

In SQL Server, you need to provide an alias for the derived table:

SELECT * FROM (SELECT *FROM test) AS x

--
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|||Derived tables must be assigned an alias.

select * from
(SELECT * from test) as X

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 21:19:31 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>
>In fact, I can reduce it to :-
>
>select * from
>(SELECT * from test)
>
>with the same effect - clearly I just need telling :-)
>
>
>cheers,
>Jim

|||On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>>
>In fact, I can reduce it to :-
>>
>select * from
>(SELECT * from test)
>>
>with the same effect - clearly I just need telling :-)

>
>In SQL Server, you need to provide an alias for the derived table:
>
SELECT * FROM (SELECT *FROM test) AS x

Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
><esquel@.sommarskog.sewrote:
>

Quote:

Originally Posted by

>>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>>This (demo) statement is fine in Access, and so far as I can see, should
>>be OK in SQL Server.
>>>
>>But Enterprise Manager barfs at the final bracket. Can anyone help
>>please?
>>>
>>select sum(field1) as sum1, sum(field2) as sum2 from
>>(SELECT * from test where id < 3
>>union
>>SELECT * from test where id 2)
>>>
>>In fact, I can reduce it to :-
>>>
>>select * from
>>(SELECT * from test)
>>>
>>with the same effect - clearly I just need telling :-)

>>
>>In SQL Server, you need to provide an alias for the derived table:
>>
> SELECT * FROM (SELECT *FROM test) AS x

>

And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth

Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
Roy Love
SupportOnline.BIZ
Roy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ
|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:

> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> support a 3-GB virtual address space, you must add the /3gb parameter to the
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>
>
|||Roy,
Might take a look at:
http://support.microsoft.com/default...274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>
|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:

> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>
|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Windows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory manager. Here's a start:
http://support.microsoft.com/default...b;en-us;274750
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:

Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
--
Roy Love
SupportOnline.BIZRoy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
--
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:
> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> support a 3-GB virtual address space, you must add the /3gb parameter to the
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> > Hello all,
> >
> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> > system
> > memory. SQL never uses more than 2GB of system memory. What is the
> > correct
> > configuration of the 3GB and PAE switches in the boot.ini file?
> > --
> > Roy Love
> > SupportOnline.BIZ
>
>|||Roy,
Might take a look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server
>> to
>> support a 3-GB virtual address space, you must add the /3gb parameter to
>> the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
>> support a 3-GB virtual address space, you must add the /3gb parameter to the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
>> setting AWE to the amount of memory in the server. You mentioned
>> virtural
>> memory, I am talking about physical memory.
>> thanks,
>> --
>> Roy Love
>> SupportOnline.BIZ
>>
>> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter
>> Server to
>> support a 3-GB virtual address space, you must add the /3gb parameter to
>> the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>
>|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
--
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
> >I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> > setting AWE to the amount of memory in the server. You mentioned virtural
> > memory, I am talking about physical memory.
> >
> > thanks,
> > --
> > Roy Love
> > SupportOnline.BIZ
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Roy,
> >>
> >> From the SQL BOL:
> >>
> >> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> >> support a 3-GB virtual address space, you must add the /3gb parameter to the
> >> boot.ini file. This allows user applications to address 3 GB of virtual
> >> memory and reserves 1 GB of virtual memory for the operating system.
> >>
> >> HTH
> >>
> >> Jerry
> >> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> >> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> >> > Hello all,
> >> >
> >> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> >> > system
> >> > memory. SQL never uses more than 2GB of system memory. What is the
> >> > correct
> >> > configuration of the 3GB and PAE switches in the boot.ini file?
> >> > --
> >> > Roy Love
> >> > SupportOnline.BIZ
> >>
> >>
> >>
>|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Windows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory manager. Here's a start:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:
>> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb
>> is
>> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
>> *possibly* you could tweak out a bit more using PAE and AWE:
>> Today (roughly):
>> BP: 1.7 GB
>> MemToLeave: 0.3 GB
>> With AWE and PAE:
>> BP: 2 GB
>> MemToLeave 0.3 GB
>> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
>> http://blogs.msdn.com/slavao/
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>> >I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
>> > setting AWE to the amount of memory in the server. You mentioned virtural
>> > memory, I am talking about physical memory.
>> >
>> > thanks,
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Roy,
>> >>
>> >> From the SQL BOL:
>> >>
>> >> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
>> >> support a 3-GB virtual address space, you must add the /3gb parameter to the
>> >> boot.ini file. This allows user applications to address 3 GB of virtual
>> >> memory and reserves 1 GB of virtual memory for the operating system.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> >> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> >> > Hello all,
>> >> >
>> >> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> >> > system
>> >> > memory. SQL never uses more than 2GB of system memory. What is the
>> >> > correct
>> >> > configuration of the 3GB and PAE switches in the boot.ini file?
>> >> > --
>> >> > Roy Love
>> >> > SupportOnline.BIZ
>> >>
>> >>
>> >>
>>

Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
--
Roy Love
SupportOnline.BIZRoy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
--
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:

> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server
to
> support a 3-GB virtual address space, you must add the /3gb parameter to t
he
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>
>|||Roy,
Might take a look at:
http://support.microsoft.com/defaul...;274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memo
ry. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is o
utside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.
:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
--
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:

> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB me
mory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is
outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE et
c.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Wi
ndows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory man
ager. Here's a start:
http://support.microsoft.com/defaul...kb;en-us;274750
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:
>

copying views

I have created several views via Enterprise Manager in a
SQL database that supports software provided by an outside
source. These views will be effected during software
version upgrades and may have some nagative impact on the
execution of upgrades.
Is there some simple way to preserve these views during a
software upgrade?
I hoped that I would just be able to cut and paste the
views (perhaps into another database where they didn't
really belong) temporarily during an upgrade and then move
them back. I don't see any way to do this. I can right
click on the views and copy them, but I don't see any way
to paste it anywhere for temporary storage.
I would prefer not to copy the query text from each view,
save it somewhere temporarily, and then recreate each view
and paste the query text back into it. I have a lot of
views created and this would be a time consuming solution.
Thanks.generate scripts and run those after the update
"allen" <anonymous@.discussions.microsoft.com> wrote in message
news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
> I have created several views via Enterprise Manager in a
> SQL database that supports software provided by an outside
> source. These views will be effected during software
> version upgrades and may have some nagative impact on the
> execution of upgrades.
> Is there some simple way to preserve these views during a
> software upgrade?
> I hoped that I would just be able to cut and paste the
> views (perhaps into another database where they didn't
> really belong) temporarily during an upgrade and then move
> them back. I don't see any way to do this. I can right
> click on the views and copy them, but I don't see any way
> to paste it anywhere for temporary storage.
> I would prefer not to copy the query text from each view,
> save it somewhere temporarily, and then recreate each view
> and paste the query text back into it. I have a lot of
> views created and this would be a time consuming solution.
> Thanks.|||Thanks!
>--Original Message--
>generate scripts and run those after the update
>"allen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
>> I have created several views via Enterprise Manager in a
>> SQL database that supports software provided by an
outside
>> source. These views will be effected during software
>> version upgrades and may have some nagative impact on
the
>> execution of upgrades.
>> Is there some simple way to preserve these views during
a
>> software upgrade?
>> I hoped that I would just be able to cut and paste the
>> views (perhaps into another database where they didn't
>> really belong) temporarily during an upgrade and then
move
>> them back. I don't see any way to do this. I can right
>> click on the views and copy them, but I don't see any
way
>> to paste it anywhere for temporary storage.
>> I would prefer not to copy the query text from each
view,
>> save it somewhere temporarily, and then recreate each
view
>> and paste the query text back into it. I have a lot of
>> views created and this would be a time consuming
solution.
>> Thanks.
>
>.
>sql

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copying a table from one database to another "last" doesnt appear the same

In enterprise manager I am copying a table from one database to
another. I am using the dts wizard to import the data. After I
successfully import the data, I open both tables to compare the
records to make sure they are the same. I right click on a field and
click "last" for both tables. However, the record is different for
both. If I do a query the record is still there but they do not show
up in the same order. Why does'nt the import wizard import the
records in the same order? Any help would be greatly appreciated.Rows in a table are not ordered. The Wizard does an INSERT... SELECT from
one table to another and unless there's a clustered index on the table
there's a good chance that the rows will be physically stored in a different
order.

Don't worry about the physical order - just use SELECT... ORDER BY if you
want to see the rows ordered.

--
David Portas
----
Please reply only to the newsgroup
--|||Great! Thank you very much for your help! After submitting this
question I noticed that microsoft mentions this on their site:

"Because SQL Server can use parallel scans to retrieve data, the data
bulk copied from an instance of SQL Server is not guaranteed to be in
any specific order unless you bulk copy from a query and specify an
ORDER BY clause."

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||But note that even if you manage to insert the rows in a specific order
there is no guarantee that they will be sorted in that order when you view
the data. If you require a specific order then you need to use ORDER BY
every time you want to view the data.

--
David Portas
----
Please reply only to the newsgroup
--

Copying a table from one database to another

Hi,
Could someone please tell me if this can be done in a query? I know how to
accomplish this using Enterprise Manager, but what I need to do is have it
done programmatically.
Thanks,
Stevecreate database a
go
create database b
go
create table a.dbo.mytable(id int)
go
create table b.dbo.mytable(id int)
go
insert b.dbo.mytable
select * from a.dbo.mytable
go
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:OCQ1BjHVEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could someone please tell me if this can be done in a query? I know how
to
> accomplish this using Enterprise Manager, but what I need to do is have it
> done programmatically.
> Thanks,
> Steve
>|||I forgot to show you this other way:
select *
into b.dbo.mytable2
from a.dbo.mytable
go
Note that this won't copy indexes, constraints, etc.
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:OCQ1BjHVEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could someone please tell me if this can be done in a query? I know how
to
> accomplish this using Enterprise Manager, but what I need to do is have it
> done programmatically.
> Thanks,
> Steve
>

Copy tables between servers using SQL Server 2005 Enterprise Edition

Hi,

I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now!

Please can anybody guide me in the right direction?

Thanks a lot.

Now you can use SSIS package instead of DTS package, which can also be generated by using Import/Export Wizard as DTS Yes it is more complex than DTS, but much more powerful, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms141134(d=ide).aspx

When you talking about copying table between servers, do you mean to copy data as well or just copy table schema? If you only want to copy table schema, you can easily do this by generating scripts for the tables on the source server: simply right click a database->choose Tasks->Generate Scripts.

If you want to copy data as well, Import/Export Wizard is your good friend; or you can backup the databases on the source server then restore from the backups on the destination; or you can even use Copy Database Wizard.

|||

Thanks a lot lori_Jay, it's much easier when you know what you're looking for! It turned out I hadn't installed all the necessary components. Best to always do a full install if you have the space I guess.

Thanks again.

Copy Table structure only - not Data

We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?

I can't find any option in the Import Data Wizard that only copies the Table structure.

Any help is appreciated. Thanks

You can generate script for the table and compile it on the target server. Right click on the table in EM, All Tasks -> Generate SQL Script...

Copy Stored Procedures From 1 Database to Another Using Query

why are you set on using QA for this?
why not use DTS from Enterprise Manager? that would be much easier, as
you can do all of them at once.
you can do it from QA, but it would have to be done one at a time, e.g.
* open the object explorer
* expand your WebCentral database \ Stored Procedures
* right-click on the proc to copy
* select "Script Object to new window as \ [ Create | Alter ]" (create
or alter depending on if it's in the WebPPO database already)
* in the new window that pops up, switch to the WebPPO database using
the database drop-down, or the Ctrl+U dialog
* execute the query
RitaG wrote:
> Hi.
> I have some Stored Procedures (SPs) in one database (WebCentral).
> I have a table in another database (WebPPO) that holds the names of
> databases that I want to copy SPs from WebCentral to.
> I want to copy these SPs via code within Query Analyzer but have no idea h
ow
> to accomplish this.
> Any suggestions would be greatly appreciated!
> Thanks,
> Rita
>Hi Trey.
Thanks for the response.
I tested out using a DTS package and that worked well - thank you.
The only reason I would have preferred to use QA is so that it could be
executed dynamically. If another database was added that needed a copy of th
e
SPs then I wouldn't have to change the DTS package.
I'll play around with your suggestions on how to use QA and see if I can
come up with something.
Thanks,
Rita
"Trey Walpole" wrote:

> why are you set on using QA for this?
> why not use DTS from Enterprise Manager? that would be much easier, as
> you can do all of them at once.
> you can do it from QA, but it would have to be done one at a time, e.g.
> * open the object explorer
> * expand your WebCentral database \ Stored Procedures
> * right-click on the proc to copy
> * select "Script Object to new window as \ [ Create | Alter ]" (create
> or alter depending on if it's in the WebPPO database already)
> * in the new window that pops up, switch to the WebPPO database using
> the database drop-down, or the Ctrl+U dialog
> * execute the query
>
> RitaG wrote:
>

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attac
h
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my en
d
> that would be preferable. Can I somehow convert the 2005 database to run
on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the wa
y
> of data export etc.
> Angus

Copy SQL Server 2005 Express database to SQL Server 2000

Hello
I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
files. If I copy to my machine and in the Enterprise manager try to attach
the files I get error 602 - and apparently you cannot do this as the
database structure has changed so much.
Bearing in mind SQL Server 2005 is customersw so don't really want to
install too much software on their machine. so if I can do this all my end
that would be preferable. Can I somehow convert the 2005 database to run on
my SQL Server 2000? What would I need to install on my machine to achieve
this? The SQL Server 2005 Express does not seem to provide much in the way
of data export etc.
Angus> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
Well, you could create an empty database, script all the objects, create
them empty, and the populate them using DTS / SSIS. You would need a
management tool on one side (Enterprise Manager, Management Studio, not sure
if Management Studio Express has any of this).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Why don't you just install SQL Express on your machine?
The two versions live together very well.
On May 10, 12:43 pm, "Angus" <nos...@.gmail.com> wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to attach
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my end
> that would be preferable. Can I somehow convert the 2005 database to run on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the way
> of data export etc.
> Angus