Creating a new login with limited rights

what combonation do you use to create a user with rights to add alertsand to
create backup jobs. This user should be able to modify databases or other u
sers.Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.|||-- Hari wrote: --
Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.
Bummer, Since I'm new at this my role as DBA needs to be limited until I lea
rn more. What is the posibility of creating a user on our live server that c
an creat backup jobs, assign notification in the jobs created, and to view t
he event long on the s
erver itself, not the SQL log. Also, I want to limit the ability to change a
ny settings as far as the databases are concerned.
My login I created contains the following.
No server roles are selected
All databases are selected
Public-is checked
db_securityadmin is checked
db_backupoperator is checked

creating a login with only backup and alert rights

What is the combination of rights to give a user access to only backup and a
lerts.Hi,
Backup the database, You can assign the database wise role
"db_backupoperator"
How to assign the backup operator role to test user
1. Use database name
2. Execute sp_addrolemember db_backupoperator ,test
To create Alerts , the user should be the member of SYSADMIN fixed server
role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:5190A3C1-E640-4F1C-BB84-8C242569ABFA@.microsoft.com...
> What is the combination of rights to give a user access to only backup and
alerts.

Creating a login after the backup was restored with existent users

Is there a way to create the login id after the database backup with users
was restored into this new SQL Server. The problem is that I am trying to
create the login id for the user that owns practically all the database
objects.
So I can't drop that user to execute sp_grantdbaccess after creating the
login id.
thanks
Cathy B
If you already created the login on the server and this is a SQL Server
user, then you need to go into each of the database and run:
EXEC sp_change_user_login 'REPORT.
Any users that show up in this list need to be have the following command
executed for them:
EXEC sp_change_users_login 'AUTO_FIX','user'
If you haven't added the logins to the new server yet (which is
preferrable), you run sp_help_revlogin on the old server. You then run the
resulting scrip on the new server to create the logins. You run this AFTER
the restores.
"Cathy Boehm" <CathyBoehm@.discussions.microsoft.com> wrote in message
news:EF2EFDC8-328C-410A-908E-FAA0E49B9220@.microsoft.com...
> Is there a way to create the login id after the database backup with users
> was restored into this new SQL Server. The problem is that I am trying to
> create the login id for the user that owns practically all the database
> objects.
> So I can't drop that user to execute sp_grantdbaccess after creating the
> login id.
> thanks
> --
> Cathy B

Creating a login after the backup was restored with existent users

Is there a way to create the login id after the database backup with users
was restored into this new SQL Server. The problem is that I am trying to
create the login id for the user that owns practically all the database
objects.
So I can't drop that user to execute sp_grantdbaccess after creating the
login id.
thanks
--
Cathy BIf you already created the login on the server and this is a SQL Server
user, then you need to go into each of the database and run:
EXEC sp_change_user_login 'REPORT.
Any users that show up in this list need to be have the following command
executed for them:
EXEC sp_change_users_login 'AUTO_FIX','user'
If you haven't added the logins to the new server yet (which is
preferrable), you run sp_help_revlogin on the old server. You then run the
resulting scrip on the new server to create the logins. You run this AFTER
the restores.
"Cathy Boehm" <CathyBoehm@.discussions.microsoft.com> wrote in message
news:EF2EFDC8-328C-410A-908E-FAA0E49B9220@.microsoft.com...
> Is there a way to create the login id after the database backup with users
> was restored into this new SQL Server. The problem is that I am trying to
> create the login id for the user that owns practically all the database
> objects.
> So I can't drop that user to execute sp_grantdbaccess after creating the
> login id.
> thanks
> --
> Cathy B

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a DB by restore - problems

Hi:

I have a file that I have been told is a SQL Server backup from a
server somewhere. The file is about 200MB in size

I am trying to create the database on my local server using RESTORE. I
created the backup device, associated it with a backup name etc.,
copied the file into the backup dir.

When I run the RESTORE command, Query Analyzer tells me the database
needs 31 GB of space and the RESTORE aborts. I've tried this several
times, get the same result every time.

Anybody ever seen anything like this? Is there another way to create a
DB in a server using a backup file?

I am running SqlServer 2000, Developer edition on a machine running
Windows Server 2003 OS.

I would appreciate any help/suggestions.

WazA restored database is the same size as the original database so you'll need
that amount of free space for the restore. You can determine the amount of
space required with RESTORE FILELISTONLY. For example:

RESTORE FILELISTONLY
FROM DISK='C:\MyBackupFile.bak'

Note that the backup file may be considerably smaller since unused data
pages are not backed up.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<EggsAckley@.Last.com> wrote in message
news:mbt9915ei2t78eueqcmudmfdaomv68oujv@.4ax.com...
> Hi:
> I have a file that I have been told is a SQL Server backup from a
> server somewhere. The file is about 200MB in size
> I am trying to create the database on my local server using RESTORE. I
> created the backup device, associated it with a backup name etc.,
> copied the file into the backup dir.
> When I run the RESTORE command, Query Analyzer tells me the database
> needs 31 GB of space and the RESTORE aborts. I've tried this several
> times, get the same result every time.
> Anybody ever seen anything like this? Is there another way to create a
> DB in a server using a backup file?
> I am running SqlServer 2000, Developer edition on a machine running
> Windows Server 2003 OS.
> I would appreciate any help/suggestions.
> Waz|||On Wed, 25 May 2005 23:55:55 GMT, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>A restored database is the same size as the original database so you'll need
>that amount of free space for the restore. You can determine the amount of
>space required with RESTORE FILELISTONLY. For example:
>RESTORE FILELISTONLY
>FROM DISK='C:\MyBackupFile.bak'
>Note that the backup file may be considerably smaller since unused data
>pages are not backed up.

Dan:

Thanks for your response. RESTORE FILELISTONLY says the data file
needs 1.2 gig but the log file needs 31 gig. How can I restore the
data without the log, or can I?

Thanks, Waz|||<EggsAckley@.Last.com> wrote in message
news:mbt9915ei2t78eueqcmudmfdaomv68oujv@.4ax.com...
> Hi:
> I have a file that I have been told is a SQL Server backup from a
> server somewhere. The file is about 200MB in size
> I am trying to create the database on my local server using RESTORE. I
> created the backup device, associated it with a backup name etc.,
> copied the file into the backup dir.
> When I run the RESTORE command, Query Analyzer tells me the database
> needs 31 GB of space and the RESTORE aborts. I've tried this several
> times, get the same result every time.
> Anybody ever seen anything like this? Is there another way to create a
> DB in a server using a backup file?

Sounds like they may have had a huge database that only contained 200 MB of
data.

Other than havnig 31 GB free, not sure what you can do.

Can you post the results of a RESTORE FILEHEADERSONLY command?

> I am running SqlServer 2000, Developer edition on a machine running
> Windows Server 2003 OS.
> I would appreciate any help/suggestions.
> Waz|||On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.com> wrote:

>Sounds like they may have had a huge database that only contained 200 MB of
>data.
>Other than havnig 31 GB free, not sure what you can do.
>Can you post the results of a RESTORE FILEHEADERSONLY command?
BackupName
BackupDescription
BackupType ExpirationDate
Compressed Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate
BackupFinishDate SortOrder
CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel
SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor
SoftwareVersionBuild MachineName
Flags BindingId RecoveryForkId
Collation
---------------------------------------
----------------------------------------------------------------------------
---- ----------------
---- --- ----
---------------------------------------
---------------------------------------
---------------------------------------
----- ----------------
------- --------
-------- --------
--------
----------------
---------------- ---
--- ----- ------- ------
------ ------- -------
-------
---------------------------------------
---- ------------
------------
---------------------------------------
Scheduled xx Test backup
Scheduled xx Test Backup
1 NULL 0
1 102 TESTSERVER\Administrator
TESTSERVER
xx Test
539 2003-08-06 17:38:53.000
206685184 3597000023310200001 3597000023311100001
3597000023310200003 3596000007489300003 2004-01-12
18:55:39.000 2004-01-12 18:56:14.000
52 228 1033 196609 80
4608 8 0 194
TESTSERVER
0 {007ED5AD-104E-452D-xxxx-512A2B3C700A}
{43356D09-0597-4AD4-xxxx-6D38CAD81F5D} SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

>> Waz|||<EggsAckley@.Last.com> wrote in message
news:tjba91d1qsh4anh0otm6772h27l4f1ju48@.4ax.com...
> On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
> <mooregr_deleteth1s@.greenms.com> wrote:
> >Sounds like they may have had a huge database that only contained 200 MB
of
> >data.
> >Other than havnig 31 GB free, not sure what you can do.
> >Can you post the results of a RESTORE FILEHEADERSONLY command?

Thanks. unfortunately I meant FILELISTONLY as Dan had correctly said.

And no, unfortunately I don't know of anyway to restore a database w/o the
log file.|||Hi:

I still need help with this. I tried the following command in Query
Analyzer:

RESTORE DATABASE x
from y
with NORECOVERY,
MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;

I make no reference to the log file 'x_log' which is also present in
the backup file, as demonstrated by the results of RESTORE
FILELISTONLY.

It seems content with the data, but then it complains about the
logfile. Of course if I specify a location for the log, it complains
that there is insufficient space on the disk (which is true, it wants
31 GB).

Is there a way to restore only the data portion? Or is there another
way to create a DB from a backup file, when the DB doesn't already
exist on my server?

I very much appreciate anyone's help with this.

Eggs

On Wed, 25 May 2005 18:07:55 -0400, EggsAckley@.Last.com wrote:

>Hi:
>I have a file that I have been told is a SQL Server backup from a
>server somewhere. The file is about 200MB in size
>I am trying to create the database on my local server using RESTORE. I
>created the backup device, associated it with a backup name etc.,
>copied the file into the backup dir.
>When I run the RESTORE command, Query Analyzer tells me the database
>needs 31 GB of space and the RESTORE aborts. I've tried this several
>times, get the same result every time.
>Anybody ever seen anything like this? Is there another way to create a
>DB in a server using a backup file?
>I am running SqlServer 2000, Developer edition on a machine running
>Windows Server 2003 OS.
>I would appreciate any help/suggestions.
>Waz|||(EggsAckley@.Last.com) writes:
> I still need help with this. I tried the following command in Query
> Analyzer:
> RESTORE DATABASE x
> from y
> with NORECOVERY,
> MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;
> I make no reference to the log file 'x_log' which is also present in
> the backup file, as demonstrated by the results of RESTORE
> FILELISTONLY.
> It seems content with the data, but then it complains about the
> logfile. Of course if I specify a location for the log, it complains
> that there is insufficient space on the disk (which is true, it wants
> 31 GB).
> Is there a way to restore only the data portion? Or is there another
> way to create a DB from a backup file, when the DB doesn't already
> exist on my server?
> I very much appreciate anyone's help with this.

The by far easiest solution in this case, is to add a new disk to the
machine. Look at the price for a 40 GB disk and compare with what you
cost your employer/client per hour. You don't have much time to look
for shortcut solution, before your work is more expensive than the disk.

If this happens on a machine which require special expensive disks
on some sort, just find another computer where you can restore,
shrink the log, and then move the database to where you want it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 26 May 2005 22:24:20 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>The by far easiest solution in this case, is to add a new disk to the
>machine. Look at the price for a 40 GB disk and compare with what you
>cost your employer/client per hour. You don't have much time to look
>for shortcut solution, before your work is more expensive than the disk.
>If this happens on a machine which require special expensive disks
>on some sort, just find another computer where you can restore,
>shrink the log, and then move the database to where you want it.

Thanks very much for the reality check. I used my USB external drive
and was able to do the restore in a straightforward manner. The
initial stumbling point was that I couldn't see the USB drive when I
installed it under Windows 2003 Server, then I realized W2003Svr
requires you to manually assign drive letters, so I did.

Can you (or anyone) suggest general guidelines for improving the
performance and/or efficiency of stored procedures under SqlServer
2000? Again, I very much appreciate any guidance.

Eggs|||(EggsAckley@.Last.com) writes:
> Can you (or anyone) suggest general guidelines for improving the
> performance and/or efficiency of stored procedures under SqlServer
> 2000? Again, I very much appreciate any guidance.

The question is a bit open-ended. But here are some general points
from the top of my head:

o Try as much as possible to avoid iterative solution, and use set-
based solutions.

o When using temp tables, create them as the first executable statement
in the procedure. (DECLARE @.local is not an executable statement.) If
you create temp tables in the middle of it all, you will get a
recompile.

o Share your graces between temp tables and table variables. Sometimes
ons is right, and sometimes the other. My general suggestion is that
you start with a temp table, but if you find that you get performance
problems because of recompiles, switch to temp tables. (Keep in mind
that those recompiles can just as well be life-savers!)

o And while it's sometimes it's a good idea to keep a temp table/table
variable for storage of intermediate results, it can also sometimes
be more effecient with one big query from hell that does it all in
one statement.

o Don't do this:
CREATE PROCEDURE some_sp ... @.startdate = NULL, ... AS
IF @.startdate IS NULL
SELECT @.startdate = convert(char(8), getdate(), 112)
since SQL Server sniffs the parameter value, it will build query
plans assuming that @.startdate is NULL. It's better to copy to
a local variable, of which SQL Server makes no assumption at all
about the value. Even more effecient is to move processing to an
inner procedure once all defaults have been filled in.

o Microsoft recommends that you always use two-part notation, for
instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
and claims this is more effecient. I claim that if the procedure
is owned by dbo, there should be no difference, and if there is,
that's a bug. One of these days, I will have to benchmark it.
Anyway, since MS recommends it, I thought I should mention it.

If you have particular issues you want to dicsuss, you are welcome.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 27 May 2005 20:46:09 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (EggsAckley@.Last.com) writes:
>> Can you (or anyone) suggest general guidelines for improving the
>> performance and/or efficiency of stored procedures under SqlServer
>> 2000? Again, I very much appreciate any guidance.
>The question is a bit open-ended. But here are some general points
>from the top of my head:
>o Try as much as possible to avoid iterative solution, and use set-
> based solutions.

>>Please elaborate a little on what exactly you mean by
set-based solutions vs iterative.

>>Also what do you think about cursors?
>o When using temp tables, create them as the first executable statement
> in the procedure. (DECLARE @.local is not an executable statement.) If
> you create temp tables in the middle of it all, you will get a
> recompile.
>o Share your graces between temp tables and table variables. Sometimes
> ons is right, and sometimes the other. My general suggestion is that
> you start with a temp table, but if you find that you get performance
> problems because of recompiles, switch to temp tables. (Keep in mind
> that those recompiles can just as well be life-savers!)
>o And while it's sometimes it's a good idea to keep a temp table/table
> variable for storage of intermediate results, it can also sometimes
> be more effecient with one big query from hell that does it all in
> one statement.
>o Don't do this:
> CREATE PROCEDURE some_sp ... @.startdate = NULL, ... AS
> IF @.startdate IS NULL
> SELECT @.startdate = convert(char(8), getdate(), 112)
> since SQL Server sniffs the parameter value, it will build query
> plans assuming that @.startdate is NULL. It's better to copy to
> a local variable, of which SQL Server makes no assumption at all
> about the value. Even more effecient is to move processing to an
> inner procedure once all defaults have been filled in.
>o Microsoft recommends that you always use two-part notation, for
> instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
> and claims this is more effecient. I claim that if the procedure
> is owned by dbo, there should be no difference, and if there is,
> that's a bug. One of these days, I will have to benchmark it.
> Anyway, since MS recommends it, I thought I should mention it.
>If you have particular issues you want to dicsuss, you are welcome.

Thanks very much.

EA|||(EggsAckley@.Last.com) writes:
>>o Try as much as possible to avoid iterative solution, and use set-
>> based solutions.
> >>Please elaborate a little on what exactly you mean by
> set-based solutions vs iterative.
> >>Also what do you think about cursors?

A cursor is an iterative solution. Some people hear that cursors are
evil, so they go home and replace the cursor with a WHILE loop where
they do SELECT MIN from a table or somesuch. That's typiclally even
worse.

In a set-based solution you work on all data in one statement. I steal an
example from another thread, where a poster had a trigger like this:

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @.TEMP_ID AS INT,
@.COUNTER AS INT,
@.P_ID AS INT

SELECT @.TEMP_ID = CATEGORY_ID,
@.COUNTER = 1,
@.P_ID = PARENT_CATEGORY_ID
FROM INSERTED
IF @.P_ID IS NOT NULL
BEGIN
WHILE@.TEMP_ID IS NOT NULL
BEGIN
SELECT @.TEMP_ID = @.P_ID,
@.COUNTER = @.COUNTER + 1
FROM INSERTED
END
END
UPDATE CATEGORY
SET DEPTH = @.COUNTER

This is an example of an iterative solution. Here is my rewrite of this
into a set-based solution:

CREATE TRIGGER category_tri ON CATEGORY FOR INSERT AS
UPDATE c
SET DEPTH = coalesce(p.DEPTH, 0) + 1
FROM CATEGORY c
JOIN inserted i ON c.CATEGORY_ID = i.CATEGORY_ID
LEFT JOIN CATEGORY p ON i.PARENT_CATEGORY_ID = p.CATEGORY_ID

In this case, we cannot compare performance, as the iterative trigger
was incorrect, but it illustrates the two different approaches.

There are situations where iterative solutions are required, or at least
can be justified. But in many situations, there are magnitudes of
performance to gain by using a set-based solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Can you (or anyone) suggest general guidelines for improving the performance and/or efficiency of stored procedures under SqlServer 2000? <<

I have some general guidelines in SQL PROGRAMMING STYLE (chapters 8, 9
and 10). Chapter 8 is a review of the principles of software
engineering and point out that they sitll apply to T-SQL, PL/SQL,
SQL/PSM, Informix/4GL, and all the other proprietary 4GLs. Frankly, I
think that nobody is teaching SE any more from the code I see. Chapter
9 is "Heurtistics" and 10 is "Thinking in SQL"

In the case of T-SQL in particular:

1) One old and still good heuristic was not to write over 50 lines in a
proc. T-SQL is a simple one-pass compiler and was not designed to be
an application development language. Do not stress it.

2) The lower the McCabe number of the proc, the better it will run.
That means try to write a chain of pure SQL statements without whiles
or If-then flow control. Again, STYLE has example of this. You can do
a lot of if-thn logic in a CASE expression

3) Think in sets and not sequences of process steps. This is vague and
hard to teach. Remember learning recursion? You just have to bang
your head against it until you understand it.

4) Avoid materializing physical storage like the proprietary temp
tables and table variables. Use derived tables and CTEs instead, so the
optimizer can see everything. Most of the time, materializing physical
storage is the result of violating (3); they hold the results of a step
and pass it along to the next step in sequence, like we use to do with
mag tapes in the 1950's.

5) Test code for exceptions. Hey, sometimes the heurisitics are
wrong. Parameter sniff and re-compiling are particular to T-SQL and
can help or hurt in production environments.

6) If your procedure has to clean up data, then the DDL is probably
missing constraints. Think of the schema as a whole, not as procedures
here and data there. That is how we designed file systems.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) One old and still good heuristic was not to write over 50 lines in a
> proc. T-SQL is a simple one-pass compiler and was not designed to be
> an application development language. Do not stress it.

Ehum, a single SQL statement can easily exceed 50 lines - particulary if
one is to go by the advice and use not temp tables.

For the record, the longest stored procedure we have is some 3000 lines
of code. This procedure had a predecessor, which was shorter for the
simple reason that it called plenty of subprocedures. Those subprocedures
are now incorporated in the big one. Why?

Because the original procedure accepted scalar input in terms of
variables. Variables are easy to pass around as parameters. The new
version instead reads its input from a table, and make an extensive
use of table variables - there's 43 of them. Tables are difficult to
pass as parameters.

> 4) Avoid materializing physical storage like the proprietary temp
> tables and table variables. Use derived tables and CTEs instead, so the
> optimizer can see everything.

Sometimes this is a good idea. Sometimes it's better to store
intermediate data in a temp table/table variable. This is particularly
true if you need to repeat the same derived table in the query. The
optimizer computes it for each occurrence. The same applies to CTEs
in SQL 2005. But it can also be good to use a temp table for intermediate
storage, since a temp table has statistics, and this can help the
optimizer.

Speaking of proprietary issues, here's another thing. Avoid the ANSI
way:

UPDATE tbl
SET col = (SELECT SUM(col2) FROM tbl2 WHERE tbl2.keycol1 = tbl.keycol

Instead use the proprietary MSSQL way:

UPDATE tbl
SET col = d.sum2
FROM tbl t
JOIN (SELECT keycol, sum2 = SUM(col2)
FROM tbl2
GROUP BY keycol) d ON t.keycol = d.keycol

My experience is that this gives better performance.

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

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

Creating a database backup

Hi,

I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this?

Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything.

Thanks for any help.

I have fixed the problem now.

Creating a Connection to Database that Does Not Yet Exist

Hello All!
I am in the process of developing a DTS package. My
package will behave as follows:
1. Backup my live database.
2. Using the backup from Step 1., the backup will be
restored using a DIFFERENT name (NewDatabase).
3. Manipulate NewDatabase database.
How can I create a connection in my DTS Package when, at
the time I am creating the package, the NewDatabase
database does not exist? Do I have to use Disconnected
Edits? If so, how? Or, do I use Microsoft Data Link?
If so, how? Or, do I use something else. (As you can
tell from my questions, I am not an expert at this :-)
Any suggestions are greatly appreciated!!You should be able to connect to the master database to do what you need to
do initially.
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
> Hello All!
> I am in the process of developing a DTS package. My
> package will behave as follows:
> 1. Backup my live database.
> 2. Using the backup from Step 1., the backup will be
> restored using a DIFFERENT name (NewDatabase).
> 3. Manipulate NewDatabase database.
> How can I create a connection in my DTS Package when, at
> the time I am creating the package, the NewDatabase
> database does not exist? Do I have to use Disconnected
> Edits? If so, how? Or, do I use Microsoft Data Link?
> If so, how? Or, do I use something else. (As you can
> tell from my questions, I am not an expert at this :-)
> Any suggestions are greatly appreciated!!|||I forgot to mention that I do have an initial connection,
the one to the original database. However, I need my DTS
package to create a connection to the NewDatabase
database once the task of restoring with a new name is
complete.
Thanks in advance!

>--Original Message--
>You should be able to connect to the master database to
do what you need to
>do initially.
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"ERR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
at
>
>.
>|||To do what? If it's only to run a sql script you can use workflow and
connect to the master database and then do the "use yournewdb" command after
you have created it
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
> I forgot to mention that I do have an initial connection,
> the one to the original database. However, I need my DTS
> package to create a connection to the NewDatabase
> database once the task of restoring with a new name is
> complete.
> Thanks in advance!
>
> do what you need to
> message
> at|||what you need to do as per Ray's suggestion is to create the dts package and
set it to connect to master, then create a dynamic task properties and a
global variable <dbname> to change the value of catalog property in
Disconnect Edit DE at run time.
then when you execute the dts package need to execute with the /A switch to
pass the <db> variable which u proide at run time see example below
dtsrun /S <servername> /E /N<packagename> /A <db>:8=newdatabase
this way u can connect to a database at runtime.
--
Olu Adedeji
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eR5zJ8y8DHA.3880@.tk2msftngp13.phx.gbl...
> To do what? If it's only to run a sql script you can use workflow and
> connect to the master database and then do the "use yournewdb" command
after
> you have created it
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "ERR" <anonymous@.discussions.microsoft.com> wrote in message
> news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
>

Creating a Backup Server software

Hi everyone,
A number of my clients keep talking about creating a backup server so that
they can quickly switch to the backup server in case the live database
server failed.
I was dabbling with the following idea.
If I could create a software that continuosly trapped SQL commands being
fired [or SPs being fired] on the live database [just like the Profiler] ,
filter out only the UPDATE syntaxes and fire them against a backup server,
couldn't I create a one-way replication programmatically?
Any ideas or suggestions from you guys?
Thanx
AbhayWhat about transaction log shipping? Then using applying them to the backup
database. In the event of failure, the backup system can be bought online
in a matter of minutes.
"Abhay Sobti" <stansoft@.vsnl.com> wrote in message
news:u$%23TZTyRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> A number of my clients keep talking about creating a backup server so that
> they can quickly switch to the backup server in case the live database
> server failed.
> I was dabbling with the following idea.
> If I could create a software that continuosly trapped SQL commands being
> fired [or SPs being fired] on the live database [just like the Profiler] ,
> filter out only the UPDATE syntaxes and fire them against a backup server,
> couldn't I create a one-way replication programmatically?
> Any ideas or suggestions from you guys?
> Thanx
> Abhay
>|||Bad idea. Why not use regular replication if that is what you want?
You'll have problems with things like GETDATE(), IDENTITY and GUID
columns because they will/may produce different results when the second
update is ran.

Creating a backup report on the report server prob.

Hi Everybody,

I hope that someone will be able to help me with that problem. I have a .rdl file on the server (report Server). Now I need to use this .rdl, make somes changes and create a new one on the same server in another directory.

Here are the codes that do all the work;

Dim streamData As New MemoryStream(rptDefinition)

Dim doc As XmlDocument = New XmlDocument()

doc.Load(streamData)

' do the changes process here

Dim rpt As Byte() = System.Text.Encoding.Unicode.GetBytes(doc.OuterXml)

Return rpt

I always got this error:

The report definition is not valid. Details: Name cannot begin with the '.' character, hexadecimal value 0x00.

How can I solve that?

Thanks

Hi,

I would check two things:

(1) The encoding you are using. The document might be using UTF-8, not unicode.
(2) The byte order mark. Check if the first two bytes of the array are [239] [187] (In decimal viewed through the debugger). If so remove these before returning the array.

-JonHP (MSFT)

|||

Hi JonHP,

It works now. I just replaced the unicode encoding with the UFT8.

Thanks a lot.

Creating A Backup & Recovery Strategy

I need to create a backup and recovery strategy for SQL
Server 2000 but I'm not a DBA and I'm not very familiar
with SQL Server.
Here is what I would like to do.
1. I want to back up all the databases on the server at
the same time (i.e. I don't want to do each database
individually).
2. I want to schedule automatic backups.
3. I amy need to restore some or all of the databases on
the server.
4. I may need to restore all the databases to a different
server (the new server would have the same setup as the
old server).
5. It doesn't matter whether the backup is done in
Enterprise Manager or by using SQL commands.
Any advice would be appreciated.Create a Database Maintenance Plan using the wizard...set the backup
location to a drive not on your server (use UNC path). Tape is also an
option, but I never use that...too many tapes have failed on me.
You have options in the wizard for scheduling, data base integrity checks,
etc. What you choose in these options will depend on your databases and how
"recoverable" you need them to be.
I strongly recommend doing a little reading in Books Online before you get
started.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:016901c39f19$9b7a05d0$a401280a@.phx.gbl...
> I need to create a backup and recovery strategy for SQL
> Server 2000 but I'm not a DBA and I'm not very familiar
> with SQL Server.
> Here is what I would like to do.
> 1. I want to back up all the databases on the server at
> the same time (i.e. I don't want to do each database
> individually).
> 2. I want to schedule automatic backups.
> 3. I amy need to restore some or all of the databases on
> the server.
> 4. I may need to restore all the databases to a different
> server (the new server would have the same setup as the
> old server).
> 5. It doesn't matter whether the backup is done in
> Enterprise Manager or by using SQL commands.
> Any advice would be appreciated.
>|||I'm having trouble finding detailed information on
restoring databases. I've found directions for restoring
databases in Enterprise Manager but those directions only
apply if the database is already on the server. I'm
looking for database recovery information for the
following scenario:
1. The database server, for whatever reason, is unusable.
2. I build a new database server.
3. I have to copy the database backups to the new server.
How do I restore a database in Enterprise Manager if the
database never existed on the server before?
Thanks in advance.
>--Original Message--
>Create a Database Maintenance Plan using the
wizard...set the backup
>location to a drive not on your server (use UNC path).
Tape is also an
>option, but I never use that...too many tapes have failed
on me.
>You have options in the wizard for scheduling, data base
integrity checks,
>etc. What you choose in these options will depend on
your databases and how
>"recoverable" you need them to be.
>I strongly recommend doing a little reading in Books
Online before you get
>started.
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>"Evan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:016901c39f19$9b7a05d0$a401280a@.phx.gbl...
>> I need to create a backup and recovery strategy for SQL
>> Server 2000 but I'm not a DBA and I'm not very familiar
>> with SQL Server.
>> Here is what I would like to do.
>> 1. I want to back up all the databases on the server at
>> the same time (i.e. I don't want to do each database
>> individually).
>> 2. I want to schedule automatic backups.
>> 3. I amy need to restore some or all of the databases on
>> the server.
>> 4. I may need to restore all the databases to a
different
>> server (the new server would have the same setup as the
>> old server).
>> 5. It doesn't matter whether the backup is done in
>> Enterprise Manager or by using SQL commands.
>> Any advice would be appreciated.
>
>.
>|||Right-click databases, All tasks, restore database. Type in the name you
want to restore and fill in all the other blanks.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:027401c39f36$8ecd1080$a601280a@.phx.gbl...
> I'm having trouble finding detailed information on
> restoring databases. I've found directions for restoring
> databases in Enterprise Manager but those directions only
> apply if the database is already on the server. I'm
> looking for database recovery information for the
> following scenario:
> 1. The database server, for whatever reason, is unusable.
> 2. I build a new database server.
> 3. I have to copy the database backups to the new server.
> How do I restore a database in Enterprise Manager if the
> database never existed on the server before?
> Thanks in advance.
> >--Original Message--
> >Create a Database Maintenance Plan using the
> wizard...set the backup
> >location to a drive not on your server (use UNC path).
> Tape is also an
> >option, but I never use that...too many tapes have failed
> on me.
> >
> >You have options in the wizard for scheduling, data base
> integrity checks,
> >etc. What you choose in these options will depend on
> your databases and how
> >"recoverable" you need them to be.
> >
> >I strongly recommend doing a little reading in Books
> Online before you get
> >started.
> >
> >--
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >
> >"Evan" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:016901c39f19$9b7a05d0$a401280a@.phx.gbl...
> >> I need to create a backup and recovery strategy for SQL
> >> Server 2000 but I'm not a DBA and I'm not very familiar
> >> with SQL Server.
> >>
> >> Here is what I would like to do.
> >> 1. I want to back up all the databases on the server at
> >> the same time (i.e. I don't want to do each database
> >> individually).
> >> 2. I want to schedule automatic backups.
> >> 3. I amy need to restore some or all of the databases on
> >> the server.
> >> 4. I may need to restore all the databases to a
> different
> >> server (the new server would have the same setup as the
> >> old server).
> >> 5. It doesn't matter whether the backup is done in
> >> Enterprise Manager or by using SQL commands.
> >>
> >> Any advice would be appreciated.
> >>
> >
> >
> >.
> >

Creating 1 backup for multiple DB

is there a way to create one backup from multiple databases ?

Hello,

If you mean being able to append multiple database backusp to a single backup file, then yes this is possible. You do this via NOINIT:

backup database Database_A to disk = 'C:\DBBackupFile.BAK'

backup database Database_B to disk = 'C:\DBBackupFile.BAK' WITH NOINIT

Then, the file C:\DBBackupFile.BAK will contain two backup sets. To reference either of the backup sets, you then need to use the FILE clause of the restore command as such:

restore filelistonly from disk = 'C:\DBBackupFile.BAK' with FILE=2 (this referrs to Database_B)

Cheers,

Rob

|||

If what you are looking for is a backup at one point in time for multiple databases, so that they can all be brought back to a common point in time, the way to accomplish that is with marked transactions.

There is not a mechanism to create a single backup spanning multiple databases.

You can, however use the full recovery model to roll a group of databases forward to a single point in time.

The mechanism for making sure that this point is exactly coordinated across databases is to use the

BEGIN TRANSACTION 'MyTransaction' WITH MARK

statement to begin a marked transaction. If that is a distributed transaction (accomlished by touching each of the databases within the transaction), then the mark will be saved in each database's log.

You can then restore each database independantly, and apply logs using the STOPATMARK = 'MyTransaction' clause to cause all databases to roll forward to exactly the same point in time.