automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jj
jj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

Automating copy between 3 servers

I'm developing a small app (Access), based on a SQL Server 2000 DB.
Development occurs on 3 DBs:
- Office DB - (MS SQL server 2000)
- Customer DB - (MS SQL server 2000)
- Portable DB - (MSDE 2000)
Since moving continuosly, as a daily routine I have to copy the latest DB,
and currently I use Enterprise manager to do so (backup/restore); boring,
'cause db paths are different on the 3 machines hosting the db servers.
Is there an easy way to automate the exchange between the 3 servers?

if all of your data is moving only in one direction and all of your tables have PK's on them, think about transactional replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql

automating a database restoration

Is there anyway to automate a daily database restoration?
In short i'm looking to transfer a copy of a database from one site's server
to a 2nd sites each day.
Due to bandwidth limitaions on the link between sites, i cannot do this from
enterprise manager.
What i'm looking to do is to backup site #1's server each night, run a
scheduled task to rar up the database ( to reduce it's size) then run a
script to copy it to site #2.
I'm trying to find out if it's possible from within SQL to schedule a
restoration each day from the transfered & unrar'ed backup from site #1.
Any ideas if this is possible
TIA
Fred
1) Log shipping (database on the targer site is read-only)
2) Replicatiion (Snapshot) very easy to perform
3) Write a SP to restore given .BAK file on the target server, make sure
that old db on the target will be deleted first.
"Fred Bloggs" <fredb@.hotmailp.com> wrote in message
news:8805F959-42CF-48FC-85B3-5CB4931D8BD6@.microsoft.com...
> Is there anyway to automate a daily database restoration?
> In short i'm looking to transfer a copy of a database from one site's
> server to a 2nd sites each day.
> Due to bandwidth limitaions on the link between sites, i cannot do this
> from enterprise manager.
> What i'm looking to do is to backup site #1's server each night, run a
> scheduled task to rar up the database ( to reduce it's size) then run a
> script to copy it to site #2.
> I'm trying to find out if it's possible from within SQL to schedule a
> restoration each day from the transfered & unrar'ed backup from site #1.
> Any ideas if this is possible
> TIA
|||You also will use automated task of the operating system to create a shared
resource, and copy the backup from site #1 to Site #2. And then with sql
server create a job to restore a database from site#2
Regards,
"Zarko Jovanovic" wrote:

> Fred Bloggs wrote:
> it is possible. one idea:
> - make a shared folder on site B
> - make an account that will be used to access folder
> - set permissions on a share and on a folder to write
> - make a job on site A to:
> - backup
> - rar (using xp_cmdshell)
> - net use (xp_cmdshell) the share using the account
> - copy to share (xp_cmdshell)
> - net use /delete (xp_cmdshell)
> - make a job on site B to:
> - unRAR (xp_cmdshell)
> - restore
> schedule job on site B to appropriate time so you're sure job on site A
> is finished! or better use waitfor (search for "waitfor" on google) to
> signal job to start
>
sql

automating a database restoration

Is there anyway to automate a daily database restoration?
In short i'm looking to transfer a copy of a database from one site's server
to a 2nd sites each day.
Due to bandwidth limitaions on the link between sites, i cannot do this from
enterprise manager.
What i'm looking to do is to backup site #1's server each night, run a
scheduled task to rar up the database ( to reduce it's size) then run a
script to copy it to site #2.
I'm trying to find out if it's possible from within SQL to schedule a
restoration each day from the transfered & unrar'ed backup from site #1.
Any ideas if this is possible
TIAFred
1) Log shipping (database on the targer site is read-only)
2) Replicatiion (Snapshot) very easy to perform
3) Write a SP to restore given .BAK file on the target server, make sure
that old db on the target will be deleted first.
"Fred Bloggs" <fredb@.hotmailp.com> wrote in message
news:8805F959-42CF-48FC-85B3-5CB4931D8BD6@.microsoft.com...
> Is there anyway to automate a daily database restoration?
> In short i'm looking to transfer a copy of a database from one site's
> server to a 2nd sites each day.
> Due to bandwidth limitaions on the link between sites, i cannot do this
> from enterprise manager.
> What i'm looking to do is to backup site #1's server each night, run a
> scheduled task to rar up the database ( to reduce it's size) then run a
> script to copy it to site #2.
> I'm trying to find out if it's possible from within SQL to schedule a
> restoration each day from the transfered & unrar'ed backup from site #1.
> Any ideas if this is possible
> TIA|||Fred Bloggs wrote:
> Is there anyway to automate a daily database restoration?
> In short i'm looking to transfer a copy of a database from one site's
> server to a 2nd sites each day.
> Due to bandwidth limitaions on the link between sites, i cannot do this
> from enterprise manager.
> What i'm looking to do is to backup site #1's server each night, run a
> scheduled task to rar up the database ( to reduce it's size) then run a
> script to copy it to site #2.
> I'm trying to find out if it's possible from within SQL to schedule a
> restoration each day from the transfered & unrar'ed backup from site #1.
> Any ideas if this is possible
> TIA
it is possible. one idea:
- make a shared folder on site B
- make an account that will be used to access folder
- set permissions on a share and on a folder to write
- make a job on site A to:
- backup
- rar (using xp_cmdshell)
- net use (xp_cmdshell) the share using the account
- copy to share (xp_cmdshell)
- net use /delete (xp_cmdshell)
- make a job on site B to:
- unRAR (xp_cmdshell)
- restore
schedule job on site B to appropriate time so you're sure job on site A
is finished! or better use waitfor (search for "waitfor" on google) to
signal job to start|||You also will use automated task of the operating system to create a shared
resource, and copy the backup from site #1 to Site #2. And then with sql
server create a job to restore a database from site#2
Regards,
"Zarko Jovanovic" wrote:
> Fred Bloggs wrote:
> > Is there anyway to automate a daily database restoration?
> > In short i'm looking to transfer a copy of a database from one site's
> > server to a 2nd sites each day.
> >
> > Due to bandwidth limitaions on the link between sites, i cannot do this
> > from enterprise manager.
> > What i'm looking to do is to backup site #1's server each night, run a
> > scheduled task to rar up the database ( to reduce it's size) then run a
> > script to copy it to site #2.
> >
> > I'm trying to find out if it's possible from within SQL to schedule a
> > restoration each day from the transfered & unrar'ed backup from site #1.
> >
> > Any ideas if this is possible
> >
> > TIA
> it is possible. one idea:
> - make a shared folder on site B
> - make an account that will be used to access folder
> - set permissions on a share and on a folder to write
> - make a job on site A to:
> - backup
> - rar (using xp_cmdshell)
> - net use (xp_cmdshell) the share using the account
> - copy to share (xp_cmdshell)
> - net use /delete (xp_cmdshell)
> - make a job on site B to:
> - unRAR (xp_cmdshell)
> - restore
> schedule job on site B to appropriate time so you're sure job on site A
> is finished! or better use waitfor (search for "waitfor" on google) to
> signal job to start
>

automatically running sql query every week

I have a simple query (a select statement which retrieves results from 2 tables via join). I run this query every week and just copy the results from Management Studio (Ctrl-A) and then open Microsoft Excel and just paste it there.

Is there a way I can automatically run this every week and generate the Excel from the results? If so what steps I have to do?

I use SQL Server 2005 Express Edition.

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

http://support.microsoft.com/kb/319951

|||

Does SQL 2005 also have DTS? I didnt see it, may be its hidden somewhere else? Because I am using SQL Server 2005 express edition (the free one)

|||

Nope, Express edition does not have DTS.

|||

You can code a windows script (vb.net or c#) using ADO to grab the data and the Excel object model to write out the spreadsheet.

Then schedule the script with the Windows scheduler.

Company I worked for do all their reporting that way. The script can also email the spreadsheet to a suitable distribution list etc.

|||

Hidotnet001 ,

bullpit:

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

http://support.microsoft.com/kb/319951

bullpit is right. You can use DTS if you are using sql2000, or SSIS if you are using sql2005. Try to generate a SSIS package and put it under intergration service, after which you will be able to run it automatically.

But since you are using sql express, i would suggset you adopting the approachSalmonTraining suggested above. Use ADO.NET programming to write some programms and schedule it through windows schedule (you will have to handle pretty lots of more things in this case than in the first approach).

Hope my suggestion can help

sql

Automatically copying tables to a backup question...

Folks,
Hope you can help with this one please. I want to regulary copy the
contents of a particular table in a database to another database server
with an identical setup.
My question is: does SQL Server 2000 have the ability to do this
automatically?
Any comments/suggestions/user-experiences/things to watch out for
would me most appreciated.
Cheers,
Al.almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
DTS will be best option for this.
You can create a DTS Package and schedule it.
Look in BOL for more details.
Regards
Amish Shah
http://shahamishm.tripod.com|||almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
>
There are several options available to you:
- replication
- DTS
- a simple INSERT/SELECT over a linked server
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Automatically copying tables to a backup question...

Folks,
Hope you can help with this one please. I want to regulary copy the
contents of a particular table in a database to another database server
with an identical setup.
My question is: does SQL Server 2000 have the ability to do this
automatically?
Any comments/suggestions/user-experiences/things to watch out for
would me most appreciated.
Cheers,
Al.almurph@.altavista.com wrote:

> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
DTS will be best option for this.
You can create a DTS Package and schedule it.
Look in BOL for more details.
Regards
Amish Shah
http://shahamishm.tripod.com|||almurph@.altavista.com wrote:
> Folks,
>
> Hope you can help with this one please. I want to regulary copy the
> contents of a particular table in a database to another database server
> with an identical setup.
> My question is: does SQL Server 2000 have the ability to do this
> automatically?
> Any comments/suggestions/user-experiences/things to watch out for
> would me most appreciated.
> Cheers,
> Al.
>
There are several options available to you:
- replication
- DTS
- a simple INSERT/SELECT over a linked server
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

automatically copy database to another pc

When I create a setup program for my vb.net 1.0 app which has embedded sql express database, it successfully copies the database to the new machine alone with the upgraded app.

However, if I just copy the vb.exe app to the other pc and also copy the mdf, ldf files, I get an error opening the sql database.

I am presuming that the setup program does some kind of backup restore or detach, attach to copy in the .mdf.

If the user cannot do this from the management studio (or if they do not have the management studio installed), is there any way I can create some method to have this copying done automatically via some code by the user?

Thanks

SM Haig

Yes, you can run a restore script using the sqlcmd program. You can read more about the restore command here:

http://msdn2.microsoft.com/en-us/library/ms186858.aspx

Buck Woody

automate copying access table to sql server 2005 db

I have a table in MS Access that I would like to copy/import into SQL Server 2005. I would like to automate this process so that it copies the table over on a daily basis. Can someone tell me how this can be done?
Thank you in advance.Take a look at DTS packages which you can schedule as a job - nice and easy ;)|||Take a look at DTS packages which you can schedule as a job - nice and easy ;)
I'm sure you meant SSIS :)|||SSIS? I've not come across that acronym before...
I figured for someone new at SQL Server, a scheduled DTS would be the easiest solution...|||DTS is with 2000,
SSIS is with 2005|||In 2005 (Server Management Studio)
Select database
> Management
> Legact
> Data Transformation Services
*confused*|||key word is "Legacy" here. the new ETL component in 2005 is SSIS, it's been completely re-architected.|||How do I use SSIS exactly to accomplish my task (see orig question posted).

Thanks.|||How do I use SSIS exactly to accomplish my task (see orig question posted).

Thanks.

Use SSMS. Right-click the database to which you want the data imported. Select Tasks | Import Data. Follow through the wizard and be sure to save the SSIS package to your DB server.

Then you will need to create a scheduled job to run the SSIS package on whatever schedule you want.

Notes:
If the Access db is located on a server other than the DB server, you'll need to use UNC paths (and probably an ODBC link, but it's been a while since I've had to use Access)

SSIS is a separate component of the SQL 2005 installation. If you have not installed it, it won't be available to you.

Regards,

hmscott|||That was the answer I was looking for. I created a SSIS Package and created a job for it. Seems to work nicely. Thanks again!

Auto-Increment Primary key Sqlce Problem

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

|||

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

Autofill

Hi, Is there any way to copy functions from one textbox to another with auto
fill? There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.>>There are cases which the programmer spends enormous time over formulas
just to change textbox names into the same formulas.
View the RDL as code (XML) and do a Find and Replace?
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> Hi, Is there any way to copy functions from one textbox to another with
> auto
> fill? There are cases which the programmer spends enormous time over
> formulas
> just to change textbox names into the same formulas.|||Thank you but this still doesn't solve my problem...
What is the difference between imputing textbox values into visual studio or
the same into xml code (find/replace)... The effort is still remains the
same.
Microsoft should seriously consider an autofill (excel-like) option, in
future updates.
Actually surprises me that they haven't released a patch for this issue
yet... Iâ'm representing a huge amount of programmers that share my opinion.
"Lisa Slater Nicholls" wrote:
> >>There are cases which the programmer spends enormous time over formulas
> just to change textbox names into the same formulas.
> View the RDL as code (XML) and do a Find and Replace?
> >L<
> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
> > Hi, Is there any way to copy functions from one textbox to another with
> > auto
> > fill? There are cases which the programmer spends enormous time over
> > formulas
> > just to change textbox names into the same formulas.
>|||>>
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
<<
The effort is *not* the same because you can do a Replace All in
the XML, whereas you have to do the textboxes one at a time <shrug>.
You may not want to do this but you asked me what the difference is, so
that's what it is. Look: I was trying to help you. If you don't like the
idea, that's okay.
>> Iâ'm representing a huge amount of programmers that share my opinion.
That's nice. Maybe somebody else reading the thread will like my
suggestion, then ! <s>
FWIW... I have been writing a design-time utility for the Report
Designer. It's nothing very fancy. It's just what I do instead of waiting
for Microsoft to do something.
The premise of my utility is different from what you need, but could
potentially
be extended to what you are asking for. Again, not very fancily, and
probably
*not* to everybody's satisfaction.
Here's my point:
In writing this, and in writing other utilities for reporting environments,
I
have learned that it's quite difficult to do this type of thing and meet
everybody's expectations. In your particular case, the utility would have
to be very specific about what it decided to change (especially in custom
expressions) so that it worked in all cases without harming anybody's
report in a way that they might not realize until after they had saved the
changes. This is likely the reason why MS would have hesitated to do it at
this stage in the product's life.
I don't, personally, believe this is a "bug" requiring a "patch".
It is an underimplementation, certainly. I see lots of those in this
product.
But this particular underimplementation, no matter how much it bothers you,
should not be addressed without a great deal of thought IMHO, so it will
likely take some time to do correctly. I would hope that is of concern to
you --
because a botched implementation would make you a lot unhappier than
you are right now <s>.
Please don't shoot the messenger. And please realize that I am representing
*nobody* and asserting *nothing* about what body of programmers share my
opinions. This is just what I think.
>L<
"Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
message news:00725C4D-A1E6-44DC-B5E7-8DA741918B96@.microsoft.com...
> Thank you but this still doesn't solve my problem...
> What is the difference between imputing textbox values into visual studio
> or
> the same into xml code (find/replace)... The effort is still remains the
> same.
> Microsoft should seriously consider an autofill (excel-like) option, in
> future updates.
> Actually surprises me that they haven't released a patch for this issue
> yet... Iâ'm representing a huge amount of programmers that share my
> opinion.
>
> "Lisa Slater Nicholls" wrote:
>> >>There are cases which the programmer spends enormous time over formulas
>> just to change textbox names into the same formulas.
>> View the RDL as code (XML) and do a Find and Replace?
>> >L<
>> "Kostas Bourikas" <KostasBourikas@.discussions.microsoft.com> wrote in
>> message news:0B5E6D85-36BF-4E2C-994B-B5C49487A44A@.microsoft.com...
>> > Hi, Is there any way to copy functions from one textbox to another with
>> > auto
>> > fill? There are cases which the programmer spends enormous time over
>> > formulas
>> > just to change textbox names into the same formulas.
>>

auto uncheck the table permit

I have database called "backoff" at both server A and server B.

server A and server B is same,

but I copy the server A database to server B
the server B before paste the server A database, I set the userA in
security > logins > "userA" > checked the database access permit for some
table but after paste the database, it auto uncheck.

Information --> The userA are owner for some table in the database

help ...........

I *think* this question relates to the database engine so I've moved it to that forum. If it's really about tools, it may get moved again. Dollysheep, any clarifications you can offer would be appreciated.

Paul

auto uncheck the table ownership

I have database called "backoff" at both server A and server B.
Before the copy I set the "userA" have the table ownership.
When I copy the database from A to B, the database is fine.
But the security > logins > "userA" > database access its auto uncheck
the table ownership for me.
Its make me very very trouble, anyone can help me ...ah...ah...helpDo you mean that the 'userA' does not have access to the restored database
on server B? Is this user a SQL Server account (that is, not a Windows
account). Perhaps you need to use sp_change_users_login to link the 'userA'
from the restored database to the same login in the server B.
Hpe this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"kennywcy@.gmail.com" wrote:
> I have database called "backoff" at both server A and server B.
> Before the copy I set the "userA" have the table ownership.
> When I copy the database from A to B, the database is fine.
> But the security > logins > "userA" > database access its auto uncheck
> the table ownership for me.
> Its make me very very trouble, anyone can help me ...ah...ah...help
>|||userA is a SQL server account.
server A and server B is same,
but I copy the server A database to server B
the server B before paste the server A database, I set the userA in
security > logins > "userA" > checked the database access for some
table
but after paste it auto uncheck.
Information --> The userA are owner for some table in the database
Any idea ?|||userA is a SQL server account.
server A and server B is same,
but I copy the server A database to server B
the server B before paste the server A database, I set the userA in
security > logins > "userA" > checked the database access for some
table
but after paste it auto uncheck.
Information --> The userA are owner for some table in the database
Any idea ?

auto uncheck the table ownership

I have database called "backoff" at both server A and server B.
Before the copy I set the "userA" have the table ownership.
When I copy the database from A to B, the database is fine.
But the security > logins > "userA" > database access its auto uncheck
the table ownership for me.
Its make me very very trouble, anyone can help me ...ah...ah...helpDo you mean that the 'userA' does not have access to the restored database
on server B? Is this user a SQL Server account (that is, not a Windows
account). Perhaps you need to use sp_change_users_login to link the 'userA'
from the restored database to the same login in the server B.
Hpe this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"kennywcy@.gmail.com" wrote:

> I have database called "backoff" at both server A and server B.
> Before the copy I set the "userA" have the table ownership.
> When I copy the database from A to B, the database is fine.
> But the security > logins > "userA" > database access its auto uncheck
> the table ownership for me.
> Its make me very very trouble, anyone can help me ...ah...ah...help
>|||userA is a SQL server account.
server A and server B is same,
but I copy the server A database to server B
the server B before paste the server A database, I set the userA in
security > logins > "userA" > checked the database access for some
table
but after paste it auto uncheck.
Information --> The userA are owner for some table in the database
Any idea ?|||userA is a SQL server account.
server A and server B is same,
but I copy the server A database to server B
the server B before paste the server A database, I set the userA in
security > logins > "userA" > checked the database access for some
table
but after paste it auto uncheck.
Information --> The userA are owner for some table in the database
Any idea ?

Auto stats

Hi all,
I have an dtsx (SSIS) for "clone" manually Sql server database to another.
How I copy all stats from one database to another ? I have problem with
"auto stats".
When I try DROP statitics for auto stats I get this error:
No se puede DROP el índice 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
No es una colección de estadísticas.
Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
statitics collection.
What can I do '
-- Get Stats list
SELECT
'[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
+ '.' + st.name + '' AS [Estadistica]
FROM
sys.tables AS tbl
INNER JOIN sys.stats st ON st.object_id=tbl.object_id
ORDER BY
[Table_Name_With_Schema] ASC,[Name] ASC
Thanks in advance, any help will be appreciated, regards, greetings
--
http://www.alhambra-eidos.es/web2005/index.html
www.kiquenet.net
http://www.setbb.com/putainformatica/viewtopic.php?p=843
www.trabajobasura.com/solusoft"Alhambra Eidos Kiquenet" <AlhambraEidosKiquenet@.discussions.microsoft.com>
wrote in message news:3B43B633-9B97-4AAF-9B5E-7B597F7EA970@.microsoft.com...
> Hi all,
> I have an dtsx (SSIS) for "clone" manually Sql server database to another.
> How I copy all stats from one database to another ? I have problem with
> "auto stats".
> When I try DROP statitics for auto stats I get this error:
> No se puede DROP el índice
> 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
> No es una colección de estadísticas.
> Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
> statitics collection.
>
> What can I do '
>
> -- Get Stats list
> SELECT
> '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
> [Table_Name_With_Schema],
> '[' + st.name + ']' AS [Name],
> '' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
> + '.' + st.name + '' AS [Estadistica]
> FROM
> sys.tables AS tbl
> INNER JOIN sys.stats st ON st.object_id=tbl.object_id
> ORDER BY
> [Table_Name_With_Schema] ASC,[Name] ASC
>
> Thanks in advance, any help will be appreciated, regards, greetings
> --
> http://www.alhambra-eidos.es/web2005/index.html
> www.kiquenet.net
> http://www.setbb.com/putainformatica/viewtopic.php?p=843
> www.trabajobasura.com/solusoft
>
Hi
You are probably trying to drop indexes as statistics, to exclude indexes
try:
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
If you only want auto_stats then
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
AND st.auto_created = 1
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
John