backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980s
There isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s
|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
David G.
|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s
|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backup
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
sql

backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980sThere isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
--
David G.|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:
> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backup
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
> > Hello:
> >
> > Could someone please share with me the exact syntax to use within Query
> > Analyzer for backing up a single table, rather than a whole database?
> >
> > Thanks!
> >
> > childofthe1980s

backing up a single table

Hello:
Could someone please share with me the exact syntax to use within Query
Analyzer for backing up a single table, rather than a whole database?
Thanks!
childofthe1980sThere isn't a T-SQL syntax for backing up a single table. May I ask why?
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||childofthe1980s wrote:
> Hello:
> Could someone please share with me the exact syntax to use within
> Query Analyzer for backing up a single table, rather than a whole
> database?
> Thanks!
> childofthe1980s
You can't backup a table, but you can backup a file or file group. See
"Using File Backups" in BOL for more information.
The other option is to schedule a DTS export on the table on a periodic
basis if you're more concerned with just the data in the table.
David G.|||Hi,
If you need to backup this table frequently then,
1. Create a new file group
2. Put this table inside the new file group
3. Backup the file group. See file group backup in books online (see Backup
Database command)
If you need to backup this table only once then:-
1. Create a new database
2. From query analyzer run the below command
select * into newdb..tablename from sourcedb..tablename
3. Backup the new database.
Thanks
Hari
SQL Server MVP
"childofthe1980s" wrote:

> Hello:
> Could someone please share with me the exact syntax to use within Query
> Analyzer for backing up a single table, rather than a whole database?
> Thanks!
> childofthe1980s|||Thanks, Hari! That last section about backing up the table once worked
perfectly!!!
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> If you need to backup this table frequently then,
> 1. Create a new file group
> 2. Put this table inside the new file group
> 3. Backup the file group. See file group backup in books online (see Backu
p
> Database command)
> If you need to backup this table only once then:-
> 1. Create a new database
> 2. From query analyzer run the below command
> select * into newdb..tablename from sourcedb..tablename
> 3. Backup the new database.
> Thanks
> Hari
> SQL Server MVP
>
> "childofthe1980s" wrote:
>

back everything up in a single file?

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1icnk$v0b$1@.news.iastate.edu...
> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing

In the code above, DBBackup is not a file, it's a backup device, so in
theory it's possible that your DBA used to point the device at different
physical files to provide some sort of rotation, although from your
description that sounds unlikely. In fact, personally, I can't see how it
would be possible to manage backups effectively using her approach.

In any case, if you need a quick solution, I would suggest creating one or
more database maintenance plans, and back up all your databases to a
convenient disk location. After that, use your standard backup software or
methods to copy the backup files to tape and/or another physical server. The
maintenance plan wizard (Enterprise Manager, Tools menu) should be easy
enough to use that you can set this up fairly quickly. It will back up
directly to files, and can also remove old backups after a period you
specify.

I'm not saying that that is the best or only approach, and some
functionality (eg differential backups) isn't available from the wizard, but
as a way of quickly putting something manageable in place, it should be
fine.

After that, find a new DBA and/or start reading the Books Online information
on "Recovery Models" and "Backup and Restore Operations".

Simon|||Thanks much for your response, Simon. I'll check out the online
information you mentioned. I understand DBBackup is not a file. It's a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:

==========
Name Physical Location Device Type

DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========

So, I don't think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I'm wrong.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"comp.databases.ms-sqlserver" <anonymous@.devdex.com> wrote in message
news:403cfe5e$0$197$75868355@.news.frii.net...
> Thanks much for your response, Simon. I'll check out the online
> information you mentioned. I understand DBBackup is not a file. It's a
> logic device that can be changed to point to different physical devices.
> But at the same time, each backup device can only point to one physical
> device, either disk file or tape, right? Our SQL Server Enterprise
> Manager->Management->Backup shows:
> ==========
> Name Physical Location Device Type
> DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
> ===========
> So, I don't think this DBBackup.BAK which contains full, differential
> and log backups would work in restore. Please correct me if I'm wrong.
> Bing
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

A single file or device can contain multiple backups, and you can restore
any individual backup from it, as long as it makes sense in the context of
what you're restoring (you can't restore database A logs to database B, for
example, or restore logs out of order). It doesn't matter if you mix backup
types and databases in one file, except from a management perspective.

You can select the individual backup to restore in the Enterprise Manage
restore dialogue using the "First backup to restore" drop down. This may be
a clearer way to demonstrate what you can do (on a test server only, of
course), assuming you have sysadmin permissions:

/* Create a test DB */
create database foo
go

/* Back up the DB twice, and the log once, to the same file,
** with an msdb backup in the middle to show that we can
** mix backups.
*/
backup database foo to disk = 'c:\foo.bak'
go
backup database foo to disk = 'c:\foo.bak'
go
backup database msdb to disk = 'c:\foo.bak'
go
backup log foo to disk = 'c:\foo.bak'
go

/" View the backups available in the file */
restore headeronly from disk = 'c:\foo.bak'
go

/* Restore the 1st backup and make the DB available */
restore database foo from disk = 'c:\foo.bak' with file = 1, recovery
go
/* Restore the 2nd backup, but do not recover (so we can apply the log) */
restore database foo from disk = 'c:\foo.bak' with file = 2, norecovery
go
/* Restore the log and make the DB available */
restore log foo from disk = 'c:\foo.bak' with file = 4, recovery
go

/* Clean up */
drop database foo
go
exec master..xp_cmdshell 'del c:\foo.bak', no_output
go

Simon|||Great. Thanks a lot again for the lucid explanations with examples!
Very helpful. Seems one file is not a big problem. I need to read
more to work out a best solution for our situation.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
if the backup made on s1 can be restored on s2. Briefly, what hit me
was the backup file made on s1 could not be viewed on s2.

On server s1, back up (both full and differential) all the databases and
logs into a backup device which points to the disk file
e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
DBBackup.BAK on s1.

Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
DBBackup.BAK as a file in My Documents fine. But in the Enterprise
Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
error 'This device does not contain any backup sets'.

How so?

Bing

Bing Du wrote:

> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing|||Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

Bing Du wrote:

> I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
> if the backup made on s1 can be restored on s2. Briefly, what hit me
> was the backup file made on s1 could not be viewed on s2.
> On server s1, back up (both full and differential) all the databases and
> logs into a backup device which points to the disk file
> e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
> DBBackup.BAK on s1.
> Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
> DBBackup.BAK as a file in My Documents fine. But in the Enterprise
> Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
> error 'This device does not contain any backup sets'.
> How so?
> Bing
> Bing Du wrote:
>> Greetings,
>>
>> Our former SQL Server 2000 DBA backed up everything in a single disk
>> file. By everything I mean, full backup, differential backup and
>> transaction logs. See below for details of how the backup is
>> currently set up. When I did 'view contents' of DBBackup, I saw it
>> contained the mixture of log, full and diff backups of verious dates.
>> She has quit. Other coworkers said (in a not-sure tone) she was able
>> to restore the databases from such a single file, although nobody ever
>> saw or knew how she did recovery. My knowledge about SQL Server,
>> especially regarding its backup/restore is limited. But I've ever
>> worked with other databases, e.g. Oracle, MySQL and Postgres. I think
>> log backups, full backups and differential backups should be
>> completely separated. Also, in each type of backups, each database
>> should have its own backup file. Please advise.
>>
>> =====
>> Log backup:
>>
>> CREATE PROCEDURE sp_lbackup AS
>> Backup log AGEP To DBBackup
>> Backup log careerfairs To DBBackup
>> Backup log CoEdocuments To DBBackup
>> Backup log committee To DBBackup
>> Backup log conference To DBBackup
>> GO
>>
>> Full backup:
>>
>> CREATE PROCEDURE dbo.sp_fullbackup AS
>> Backup database AGEP To DBBackup WITH INIT
>> Backup database CoEdocuments To DBBackup
>> Backup database careerfairs To DBBackup
>> Backup database committee To DBBackup
>> Backup database conference To DBBackup
>> GO
>>
>> Diff backup:
>>
>> CREATE PROCEDURE sp_diffbackup AS
>> Backup database AGEP To DBBackup with differential
>> Backup database careerfairs To DBBackup with differential
>> Backup database CoEdocuments To DBBackup with differential
>> Backup database committee To DBBackup with differential
>> Backup database conference To DBBackup with differential
>> GO
>> =====
>>
>> Thanks in advance for any help,
>>
>> Bing
>|||"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1l794$aui$1@.news.iastate.edu...
> Ok, seems like only local disks show up when a new backup device is
> defined. After I copied the backup from the network shared directory to
> a local directory on s2, restore worked.
> Bing

<snip
FYI, it is possible to backup and restore from UNC paths, provided that the
account used to run MSSQL has access to the path:

backup database foo to disk = '\\MyServer\MyBackups\foo.bak'

Enteprise Manager only displays local drives, but I believe you can type in
a UNC path in the backup/restore dialogues, although I don't use EM much, so
I'm not 100% sure about that.

Simon|||I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

Bing

Simon Hayes wrote:

> "Bing Du" <bdu@.iastate.edu> wrote in message
> news:c1l794$aui$1@.news.iastate.edu...
>>Ok, seems like only local disks show up when a new backup device is
>>defined. After I copied the backup from the network shared directory to
>>a local directory on s2, restore worked.
>>
>>Bing
>>
>
> <snip>
> FYI, it is possible to backup and restore from UNC paths, provided that the
> account used to run MSSQL has access to the path:
> backup database foo to disk = '\\MyServer\MyBackups\foo.bak'
> Enteprise Manager only displays local drives, but I believe you can type in
> a UNC path in the backup/restore dialogues, although I don't use EM much, so
> I'm not 100% sure about that.
> Simon|||Bing Du (bdu@.iastate.edu) writes:
> I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

And what does "did not work" mean?

As Simon says, it depends on the account under which SQL Server is running.
If SQL Server is installed to run as Local Service, then you cannot access
network resources from SQL Server.

A word of caution, though, about backing up or restore from network
devices. I don't think is fully supported. That is, you can do it, but
you will be safe if you back up to local disk, and copy of the network.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949C56828223Yazorman@.127.0.0.1...
> Bing Du (bdu@.iastate.edu) writes:
> > I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.
> And what does "did not work" mean?
> As Simon says, it depends on the account under which SQL Server is
running.
> If SQL Server is installed to run as Local Service, then you cannot access
> network resources from SQL Server.
> A word of caution, though, about backing up or restore from network
> devices. I don't think is fully supported. That is, you can do it, but
> you will be safe if you back up to local disk, and copy of the network.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

According to Books Online (under BACKUP):

"If using a network server with a Uniform Naming Convention (UNC) name or
using a redirected drive letter, specify a device type of disk."

But anyway, a backup to a UNC path may fail because of network issues, so
it's fair to say that a local backup is a safer option.

Simon

What is Query Analyzer's replacement in SQL Server 2005?

Both Query Analyzer and Enterprise Manager are gone from SQL Server 2005. In their place is a single tool, SQL Server Management Studio. This tool has most of the features of its predecessors, but with an upgraded UI and a lot of improved functionality. I think that most DBAs will find it to be a good upgrade. If you'd like more information on some of the new features with this tool, read my article on SQL Server Management Studio client tool enhancements.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1146801,00.htmlPAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?
|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:

> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>
>
|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...[vbcol=seagreen]
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> "Andrew J. Kelly" wrote:
|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:

> AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>
>
|||boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/de...onfig_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
[vbcol=seagreen]
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:

> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...onfig_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>
|||The other way around. 3 GB for the application and 1 GB for the system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...[vbcol=seagreen]
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
|||Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...nfig_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:

AWE questions

We are looking to take advantage of AWE in a single instance of SQL Server
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
MarkYou don't need the /awe switch but other than you are right :-)
Also, note this KB about AWE + cluster failover Pending Timeout
http://support.microsoft.com/default.aspx?scid=kb;en-us;329914
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mark Sullivan" <msullivan@.getconnected.com> wrote in message
news:eDBCz8RuDHA.2360@.TK2MSFTNGP09.phx.gbl...
We are looking to take advantage of AWE in a single instance of SQL Server
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
Mark|||Thanks for the reply. Has AWE been a positive experience?
Ken

avoid warning

We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
> > We use a script in our build process to create a database. A single
> > script creates entire database, including stored procedures. We
> > occasionally
> > get a warning message "cannot create entry in sysdepends as .....
> > the procedure will still be created". I believe this is a harmless
> > warning
> > and happens because of the order of creation of stord procedure. Is
> > there a way to disable this warning.
> >
> >|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>

avoid cursor to get variables

Hi all,
I would like to know if there is any other way of doing the following
without a cursor.
Normally I need to return a single record and load of variables with it (the
query is very costly, so I cannot afford running it again and again.
At the moment it is done with a cursor to retrieve the parameters in the
select clause, and right away it is closed. I thought it could be faster to
do directly a select statement and assign the values to the variables
directly, but as the query is complex and has parameters in the from part I
cannot do it.
The query is normally built on a string and accessed through a cursors:
@.sql = "declare c cursor fast_forward for"
@.sql = @.sql + "select field1, field2,... fieldN"
@.sql = @.sql + " from " @.tableParameter
@.sql = @.sql + "where condition1 =" @.condition1Parameter
exec(@.sql)
open c
fetch next from c into @.field1,
@.field2,
…
@.fieldn
close c
deallocate c
What I would like to do is something of the like this:
@.sql = “select @.field1 = field1, @.field2 = field2 … from @.tableParameter
where condition1 = @.condition1Parameter “
But it always fails unless I declare the variables within the string as
well, but in that case I cannot use that variables in the scrip further.
I have also tried to enclose this logic in a stored procedure, but it would
only work if the query to be executed has only the parameters in the select
part, and not the from part.
Anyone has any idea?
Thanks in advance,
TristanHi
You should be able to use sp_executesql to do this.
DECLARE @.sql nvarchar(4000)
DECLARE @.field1 varchar(10)
DECLARE @.field2 varchar(10)
DECLARE @.condition1Parameter varchar(10)
SET @.sql = N'select @.field1 = field1, @.field2 = field2 … from ' +
QUOTENAME(@.tableParameter) + N'where condition1 = @.condition1Parameter'
EXEC sp_executesql @.@.sql,
N'@.field1 vachar(10) OUTPUT, @.field2 vachar(10) OUTPUT,
@.condition1Parameter vachar(10)',
@.field1 OUTPUT, @.field2 OUTPUT, @.condition1Parameter
GO
See books online and http://www.sommarskog.se/dynamic_sql.html#sp_executesql
for other examples.
John
"Tristan" wrote:

> Hi all,
> I would like to know if there is any other way of doing the following
> without a cursor.
> Normally I need to return a single record and load of variables with it (t
he
> query is very costly, so I cannot afford running it again and again.
> At the moment it is done with a cursor to retrieve the parameters in the
> select clause, and right away it is closed. I thought it could be faster t
o
> do directly a select statement and assign the values to the variables
> directly, but as the query is complex and has parameters in the from part
I
> cannot do it.
> The query is normally built on a string and accessed through a cursors:
> @.sql = "declare c cursor fast_forward for"
> @.sql = @.sql + "select field1, field2,... fieldN"
> @.sql = @.sql + " from " @.tableParameter
> @.sql = @.sql + "where condition1 =" @.condition1Parameter
> exec(@.sql)
> open c
> fetch next from c into @.field1,
> @.field2,
> …
> @.fieldn
> close c
> deallocate c
>
> What I would like to do is something of the like this:
> @.sql = “select @.field1 = field1, @.field2 = field2 … from @.tableParamet
er
> where condition1 = @.condition1Parameter “
> But it always fails unless I declare the variables within the string as
> well, but in that case I cannot use that variables in the scrip further.
> I have also tried to enclose this logic in a stored procedure, but it woul
d
> only work if the query to be executed has only the parameters in the selec
t
> part, and not the from part.
> Anyone has any idea?
> Thanks in advance,
> Tristan
>|||Thanks a lot John, that did the job :-) !!!
"John Bell" wrote:
> Hi
> You should be able to use sp_executesql to do this.
> DECLARE @.sql nvarchar(4000)
> DECLARE @.field1 varchar(10)
> DECLARE @.field2 varchar(10)
> DECLARE @.condition1Parameter varchar(10)
> SET @.sql = N'select @.field1 = field1, @.field2 = field2 … from ' +
> QUOTENAME(@.tableParameter) + N'where condition1 = @.condition1Parameter'
> EXEC sp_executesql @.@.sql,
> N'@.field1 vachar(10) OUTPUT, @.field2 vachar(10) OUTPUT,
> @.condition1Parameter vachar(10)',
> @.field1 OUTPUT, @.field2 OUTPUT, @.condition1Parameter
> GO
> See books online and [url]http://www.sommarskog.se/dynamic_sql.html#sp_executesql[/ur
l]
> for other examples.
> John
>
> "Tristan" wrote:
>|||You really need to re-think your entire approach. A table variable is
your way of tellignthe world that your code lacks any cohesion
(remember that term from Software Engineering 101?). This procedure
might be for automobiles, might be squid, or who knows? Well, any
random future user is a better judge and designer than the programmer
who did this.
I will not even remark on using a cursor and the way that you seemto
confuse fields and columns.
You are not yet writing SQL; you are using SQL to fake 1950's
procedural language that you already know.
It may take you years to un-learn . your old habits. But when you do,
your code will run1 to 3 orders of magnitude faster, port to new
platforms, be readable and take up a fraction of the space you are
using now.