Backup to Network Drive: users

Showing posts with label users. Show all posts

Tuesday, March 20, 2012

Bandwidth required for remote sites to use Sharepoint Portal se

If I have a sharepoint installation at my head office and remote
sites with users from about 20-80 need to access this Intranet.
How much bandwith is suggested to accomodate users of that size ? At
two sites I have a 768k frame relay and at the largest remote site with
about 80 users there is a 10MB wan connection to head office.
This question should be posted to the sharepoint newsgroup.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:A335E9FE-5EF5-4416-8E89-6E6137A9F90A@.microsoft.com...
> If I have a sharepoint installation at my head office and remote
> sites with users from about 20-80 need to access this Intranet.
> How much bandwith is suggested to accomodate users of that size ?
> At
> two sites I have a 768k frame relay and at the largest remote site
> with
> about 80 users there is a 10MB wan connection to head office.
>

Monday, March 19, 2012

bad performance, help needed

Hi,
We have a working application that has been ok for some time but recently as
our data and users increased we started to get timeouts as some queries
take more than minute to execute. This happens only occasionally on a very
specific query that joins couple of big tables.
Now the queries are fairly complex and unoptimized but that is not what
bothers me. (also changing queries didn't help, may be something to do with
amounts of data involved?)
If I run the same query as SA it runs in a second. If I run it as a normal
user that has been given select permissions on the relevant tables it takes
ages. Are there any settings for normal users regarding limits on resource
or something like that?
We only support two users SA and TheUser as authentication is done server
side and everything is done through stored procedures spare the read
queries. TheUser is granted permissions to read some tables and to execute
the stored procedures nothing else.
Using the SQL Trace thing... I can see that the offending query does approx
350000 reads and 20 writes. I don't know what that means but thought it
might help you help me ;)
Thank you,
YordanIt is impossible to say without actually seeing the query and the DDL for
the tables involved but it sounds liek it is not using an index and scanning
the table at least once, probably more. Can you post the other info?
Andrew J. Kelly SQL MVP
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan,
Are you logging into Query Analyzer using SA and TheUser to make the
comparisons in time? Same T-SQL code? Are there any differences in the
execution plans chosen?
HTH
Jerry
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||If timeouts occur "only occasionally", then perhaps your problem is with
blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Use SQL Server Profiler to verify that your SP is making good use of
indexes. Also, as your table gets larger, fragmentation may become an issue
that needs periodic maintenance.
How To: Use SQL Profiler
http://msdn.microsoft.com/library/d...
ethowto15.asp
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan...
How often do you reindex your database tables?
Yosh
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Tables are not indexed... (yeah, I know)
I'll probably index to improve but I don't understand the difference the
actual user makes.Why would it work with SA? Is there any quota on resources
spared on one user? Session? I'd like to understand that...
Also same query runs fine most of the time but occasionally is taking >
minute.
its something of the sort (the actual query is too long):
select some stuff from table1 outer left join (select * from table 2) ON
(conditions) where other conditions
table1 is approx 40,000 rows
table2 is approx 600,000 rows
-Yordan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OQ1SjFdzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> It is impossible to say without actually seeing the query and the DDL for
> the tables involved but it sounds liek it is not using an index and
> scanning the table at least once, probably more. Can you post the other
> info?
> --
> Andrew J. Kelly SQL MVP
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Thanks for the links. I'll try this tomorrow as I'm home now.
To be honest it does feel like a block because I've seen few before. Its
just I don't see how it can be this time.
Thanks,
Yordan
"JT" <someone@.microsoft.com> wrote in message
news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
> If timeouts occur "only occasionally", then perhaps your problem is with
> blocking:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Use SQL Server Profiler to verify that your SP is making good use of
> indexes. Also, as your table gets larger, fragmentation may become an
> issue that needs periodic maintenance.
> How To: Use SQL Profiler
> http://msdn.microsoft.com/library/d...enethowto15.asp
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||I make my app dump the query in text. (so T-SQL code is the same).
Then I use the Query Analyzer to log-in and execute it. Using TheUser
outcomes are identical. With SA I've tried only the Analyser.
The difference in time is obvious. The exact times I get from the server
manager - using Trace (not sure about the name).
Thanks,
Yordan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yordan,
> Are you logging into Query Analyzer using SA and TheUser to make the
> comparisons in time? Same T-SQL code? Are there any differences in the
> execution plans chosen?
> HTH
> Jerry
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Here is an easy way to tell if it is a blocking problem. The next time a
query seems to be hung up, then run sp_who2. If a process has another
processes's SPID listed in the [blkby] column, then it is being blocked.
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%23u6dfXdzFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Thanks for the links. I'll try this tomorrow as I'm home now.
> To be honest it does feel like a block because I've seen few before. Its
> just I don't see how it can be this time.
> Thanks,
> Yordan
> "JT" <someone@.microsoft.com> wrote in message
> news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
>|||Is it possible that since you run the same exact query as SA the execution
plan is already cached from the app and SQL server returns the result much
faster a second time
if the app user runs the same query twice does it take a long time both time
s?
http://sqlservercode.blogspot.com/
"Yordan Gyurchev" wrote:

> I make my app dump the query in text. (so T-SQL code is the same).
> Then I use the Query Analyzer to log-in and execute it. Using TheUser
> outcomes are identical. With SA I've tried only the Analyser.
> The difference in time is obvious. The exact times I get from the server
> manager - using Trace (not sure about the name).
> Thanks,
> Yordan
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
>

Friday, February 24, 2012

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

create login companyA default_database 'customersA'

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

use CustomersA

sp_changedbowner 'CustomersA'

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

create login companyA default_database 'customersA'

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

use CustomersA

sp_changedbowner 'CustomersA'

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Thursday, February 16, 2012

Backup( thru DB Maintenace plan) to another server failing

We have 4 servers in a workgroup environment. I have created 2 users (
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fine
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
--
RKLook at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> > We have 4 servers in a workgroup environment. I have created 2 users (
> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> > the
> > servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> > Service.
> >
> > I have created a DBMaintenance Plan on 3 servers to backup the user
> > databases directly onto Server 4 ( Use this directory ->
> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> > fine
> > (for 2 months). All of a sudden the jobs are failing. From the Job History
> > I
> > see the following error ...
> >
> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> > (Error 22029). The step failed.
> >
> > I see the following error from SQLServer Logs ...
> >
> > BackupDiskFile::CreateMedia: Backup device
> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> > Operating system error = 1330(Logon failure: the specified account
> > password
> > has expired.).
> >
> > I verified the password for the user SQLAgent (the owner SQLServer Agent
> > service) on all the servers and it is same. SQLAgent user is setup as non
> > expiring account.
> >
> > When I tried to backup a single database (use this directory ->
> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> > verify backup file location. Do you want to use the backup file location
> > anyway ?"
> >
> >
> > --
> > RK
>
>|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
>> Look at
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> Walk through the troubleshooting steps at the bottom. It is likely a
>> permissions issue or an expured account issue.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> > We have 4 servers in a workgroup environment. I have created 2 users (
>> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
>> > the
>> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> > SQLSERVERAgent
>> > Service.
>> >
>> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> > databases directly onto Server 4 ( Use this directory ->
>> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> > running
>> > fine
>> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> > History
>> > I
>> > see the following error ...
>> >
>> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
>> > (Error 22029). The step failed.
>> >
>> > I see the following error from SQLServer Logs ...
>> >
>> > BackupDiskFile::CreateMedia: Backup device
>> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
>> > Operating system error = 1330(Logon failure: the specified account
>> > password
>> > has expired.).
>> >
>> > I verified the password for the user SQLAgent (the owner SQLServer
>> > Agent
>> > service) on all the servers and it is same. SQLAgent user is setup as
>> > non
>> > expiring account.
>> >
>> > When I tried to backup a single database (use this directory ->
>> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
>> > verify backup file location. Do you want to use the backup file
>> > location
>> > anyway ?"
>> >
>> >
>> > --
>> > RK
>>|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
>> The SQL Agent invokes the backup command. The SQL engine actually runs
>> the
>> command so you have to be concerned about the login account for the SQL
>> Server service, not the SQL Agent service.
>> GNH
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
>> > SQLOwner
>> > account on SQL 03 server ( where all servers are writing their backups)
>> > was
>> > expired. I changed it a non-expiring account
>> > I thought for backup jobs we are only concerned about the user running
>> > the
>> > SQLServerAgent service, which is SQLAgent user in my case.
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> Look at
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> >>
>> >> Walk through the troubleshooting steps at the bottom. It is likely a
>> >> permissions issue or an expured account issue.
>> >>
>> >> Geoff N. Hiten
>> >> Microsoft SQL Server MVP
>> >> Senior Database Administrator
>> >>
>> >>
>> >>
>> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> >> > We have 4 servers in a workgroup environment. I have created 2 users
>> >> > (
>> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
>> >> > all
>> >> > the
>> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> >> > SQLSERVERAgent
>> >> > Service.
>> >> >
>> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> >> > databases directly onto Server 4 ( Use this directory ->
>> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> >> > running
>> >> > fine
>> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> >> > History
>> >> > I
>> >> > see the following error ...
>> >> >
>> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
>> >> > 42000]
>> >> > (Error 22029). The step failed.
>> >> >
>> >> > I see the following error from SQLServer Logs ...
>> >> >
>> >> > BackupDiskFile::CreateMedia: Backup device
>> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
>> >> > create.
>> >> > Operating system error = 1330(Logon failure: the specified account
>> >> > password
>> >> > has expired.).
>> >> >
>> >> > I verified the password for the user SQLAgent (the owner SQLServer
>> >> > Agent
>> >> > service) on all the servers and it is same. SQLAgent user is setup
>> >> > as
>> >> > non
>> >> > expiring account.
>> >> >
>> >> > When I tried to backup a single database (use this directory ->
>> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
>> >> > to
>> >> > verify backup file location. Do you want to use the backup file
>> >> > location
>> >> > anyway ?"
>> >> >
>> >> >
>> >> > --
>> >> > RK
>> >>
>> >>
>> >>
>>|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is the
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a SQL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
> >I am backing up user databases on SQL04, but i am writing the backups to
> > SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> > account
> > on SQL03 expiring affect the backup job on SQL04 ?
> >
> > In my case SQL03 happened to be another SQL Server. What is if the
> > destination server (SQL03) does not even have a SQL Serer running on it ?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Agent invokes the backup command. The SQL engine actually runs
> >> the
> >> command so you have to be concerned about the login account for the SQL
> >> Server service, not the SQL Agent service.
> >>
> >> GNH
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> >> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> >> > SQLOwner
> >> > account on SQL 03 server ( where all servers are writing their backups)
> >> > was
> >> > expired. I changed it a non-expiring account
> >> > I thought for backup jobs we are only concerned about the user running
> >> > the
> >> > SQLServerAgent service, which is SQLAgent user in my case.
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> Look at
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >> >>
> >> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> >> permissions issue or an expured account issue.
> >> >>
> >> >> Geoff N. Hiten
> >> >> Microsoft SQL Server MVP
> >> >> Senior Database Administrator
> >> >>
> >> >>
> >> >>
> >> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> >> > We have 4 servers in a workgroup environment. I have created 2 users
> >> >> > (
> >> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
> >> >> > all
> >> >> > the
> >> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> >> > SQLSERVERAgent
> >> >> > Service.
> >> >> >
> >> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> >> > databases directly onto Server 4 ( Use this directory ->
> >> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> >> > running
> >> >> > fine
> >> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> >> > History
> >> >> > I
> >> >> > see the following error ...
> >> >> >
> >> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
> >> >> > 42000]
> >> >> > (Error 22029). The step failed.
> >> >> >
> >> >> > I see the following error from SQLServer Logs ...
> >> >> >
> >> >> > BackupDiskFile::CreateMedia: Backup device
> >> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
> >> >> > create.
> >> >> > Operating system error = 1330(Logon failure: the specified account
> >> >> > password
> >> >> > has expired.).
> >> >> >
> >> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> >> > Agent
> >> >> > service) on all the servers and it is same. SQLAgent user is setup
> >> >> > as
> >> >> > non
> >> >> > expiring account.
> >> >> >
> >> >> > When I tried to backup a single database (use this directory ->
> >> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
> >> >> > to
> >> >> > verify backup file location. Do you want to use the backup file
> >> >> > location
> >> >> > anyway ?"
> >> >> >
> >> >> >
> >> >> > --
> >> >> > RK
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

backup with users

Hi
one small question/
i need to take backup of one database from one server and
restore it to another server/destination. db may be same
or difft,but i need to do this so that all usernames
belonging to that particular db should also be transferred
to new db on other server.
how can we restore db with same users/login which belong
to source server/database.Hi Sanjay,
Take a look at this article, see if this is what you are looking for.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server ID:
246133.KB.EN-US
http://support.microsoft.com/?id=246133
Or you can use sp_change_users_login.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monday, February 13, 2012

Backup users

I am currently using the BACKUP DATABASE method to backup my database.
Everything works fine, except for users. I created various users with
sp_addlogin to access this database and they are located in the xlogins
table. The problem is for example, if I backup, remove some users and
do a restore, i won't have them back because i just backup the
database.

So the question is, how can I backup specific users and restore them ?
Do I have to do it manually ? It seems I can't alter the xlogin table
so I'm pretty confused.Kittikun (kittikun@.gmail.com) writes:
> I am currently using the BACKUP DATABASE method to backup my database.
> Everything works fine, except for users. I created various users with
> sp_addlogin to access this database and they are located in the xlogins
> table. The problem is for example, if I backup, remove some users and
> do a restore, i won't have them back because i just backup the
> database.
> So the question is, how can I backup specific users and restore them ?
> Do I have to do it manually ? It seems I can't alter the xlogin table
> so I'm pretty confused.

Not really sure that I understand your question. If you decide to drop
a login from the server, you have probably decided to do for good. Why
would you have thst login back if you reture a database?

If you restore the database on a different server, then there is a problem,
because the users in the database will not match the logins. In this case
you can use sp_change_users_login to map users back to logins. For details
on this procedure, please look in Books Online.

--
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|||Well, I am making an application that allow users to create new users
with different rights and privileges. Each new user have an sql user
account in the database and are registered in a custom table.

So when I make a backup, the custom table will contains all the logins.
If after I remove some users with my application, the sql user will be
removed and the table will be updated. But, if I restore the backup,
the table will contain the deleted users and the sql accounts won't
exist so they won't be usable.

I don't know the clear password so I can't use sp_change_users_login or
sp_add_user.

So to backup/restore sql user account I tried the following thing

1) Make a copy of my users with

use test
go

SELECT * INTO x FROM master.dbo.syslogins

2) To restore them afterward

use master
go

exec sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go

insert syslogins select * from test.dbo.x
exec sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

Thought it may have been a good idea but I get an "Update or insert of
view or function 'syslogins' failed because it contains a derived or
constant field." error.

Maybe I am looking on the wrong path...|||Kittikun (kittikun@.gmail.com) writes:
> Well, I am making an application that allow users to create new users
> with different rights and privileges. Each new user have an sql user
> account in the database and are registered in a custom table.
> So when I make a backup, the custom table will contains all the logins.
> If after I remove some users with my application, the sql user will be
> removed and the table will be updated. But, if I restore the backup,
> the table will contain the deleted users and the sql accounts won't
> exist so they won't be usable.
> I don't know the clear password so I can't use sp_change_users_login or
> sp_add_user.

First permit me to straighten up some terminology.

The entity that connects to SQL Server is a *login* (or in SQL 2005
speak a "server principal".)

A login can be given access to a database, and is then mapped to
*user* in that database. ("database principal" in SQL 2005.)

When you take a backup of the database, you will get the *users*,
because the users are stored in the database, more precisely in
the sysusers table.

Normally, a login "joe" maps to a user "joe" in a database, but
there is law about this. A very exceptions is when the login owns the
database. In this case the login's user in the database is "dBo".

Anyway, what I still don't understand is why you remove these logins,
if you think you may need them again.

But if you need to recreate them you can. The fact that you don't know
the password is not an issue - just invent one. Once you have the
login, you can use sp_change_users_login. (And there is no reason to
use sp_adduser.) Of course, if the dropped login what to retain their
passwords, they can't do that.

> exec sp_configure 'allow updates', 1
> RECONFIGURE WITH OVERRIDE
> go
> insert syslogins select * from test.dbo.x
> exec sp_configure 'allow updates', 0
> RECONFIGURE WITH OVERRIDE
> go
> Thought it may have been a good idea but I get an "Update or insert of
> view or function 'syslogins' failed because it contains a derived or
> constant field." error.

syslogins is a view, the table is sysxlogins. But I have no idea
whether it works to do this. It certainly isn't supported.

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

Tuesday, March 20, 2012

Bandwidth required for remote sites to use Sharepoint Portal se

Monday, March 19, 2012

bad performance, help needed

Friday, February 24, 2012

Backup/restore table belonging to one user/login

Backup/restore table belonging to one user/login

Thursday, February 16, 2012

Backup( thru DB Maintenace plan) to another server failing

Backup( thru DB Maintenace plan) to another server failing

Backup( thru DB Maintenace plan) to another server failing

backup with users

Monday, February 13, 2012

Backup users

Backup to Network Drive

Blog Archive

About Me