Resotring a replicated database

I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box

NOW

I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.

How about do I do that with replication being involved?

ThanxI don't know that there is any way to restore a database that is involved in replication. It smells like a VERY bad thing to me because it would do horribly confusing things to the data and especially the log information that could send replication into hysterics!

If you really want to do this, I'd delete the server from the replication schema (probably all of the servers from the schema for Merge Replication), then do the restore. After that restore was complete, then I'd rebuild whatever replication I needed.

-PatP|||You can backup the replicated database but follow what Pat referred and go with this MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replbackup_8enn.asp link for more details.

Resetting / deleting all the users in aspnet_* tables

Hi,

How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?

Thanks

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

|||

Assuming you don't have any other tables in the database that have foreign key constraints to the aspnet_* tables, you could simply rerun the T-SQL scripts that create those tables (since those scripts first drop the tables, if they exist). You can find the T-SQL files in %WINDOWS%\Microsoft.NET\Framework\v2.0.50727 or you can execute them using the aspnet_regsql.exe command line tool.

UPDATE: To provide more clarity... the T-SQL scripts in that folder are named like Install*.sql. InstallMembership.sql, for example, contains the scripts for the membership-related tables; InstallRoles.sql for the roles. InstallCommon.sql has them all.

|||

Sweeperq:

It has been a while since I did it, so I can't remember if deleting a user cascades and deletes all related records in other tables. In any event, you can do individual truncates on each of the tables:

TRUNCATE TABLE aspnet_Members;

The only difficulty with this approach is since the aspnet_* tables have a number of foreign key constraints among themselves, if there is data in the tables you have to truncate (drop) the tables in the correct order, otherwise you will get foreign key constraint errors and the truncate will fail.

|||

So TRUNCATE does not trigger cascading deletes?

|||

The stored procedure aspnet_users_deleteuser takes care of the relationships and constraints - it's what the Membership.DeleteUser method itself calls. You can call it manually. Here's anarticle on deleting users from membership using the stored procedure.

But this deletes one user at a time. You want to delete all users? You can use SQL to cursor through the aspnet_users table, calling the stored procedure each time.

Otherwise, you need to truncate in the correct order which would be this: aspnet_usersinroles, aspnet_profile, aspnet_personalizationperuser, aspnet_membership, aspnet_users.

|||

Sweeperq:

So TRUNCATE does not trigger cascading deletes?

It does, if I'm not mistaken. Problem is, the foreign key constraints on the aspnet_* tables do not cascade deletes. So you have to manually delete the "child" tables first before you can delete the data from the parent table.

Reset SA password

I have a need to reset the SA password on our SQL 2000 SP4 Server. Only one
production database is on this server and I've confirmed with the vendor
that the database is not using the SA account for anything.
Under Security, login, I see that I can r/c on SA and change the password
there.
Is that OK to do?
What's the impact of changing the SA password?
Anything else that I'm not thinking of'
Thank you!
RandyHi
Take a look atv sp_password stored procedure in the BOL
"RandyH" <RHollaw@.HOTmail.com> wrote in message
news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>one production database is on this server and I've confirmed with the
>vendor that the database is not using the SA account for anything.
> Under Security, login, I see that I can r/c on SA and change the password
> there.
> Is that OK to do?
> What's the impact of changing the SA password?
> Anything else that I'm not thinking of'
> Thank you!
> Randy|||Hi Randy
If you are 100% sure that, none of your application or users are using sa
password then you can change it.
You can use sol profiler to trace wheather some one is using it or not
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"RandyH" <RHollaw@.HOTmail.com> wrote in message
news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>one production database is on this server and I've confirmed with the
>vendor that the database is not using the SA account for anything.
> Under Security, login, I see that I can r/c on SA and change the password
> there.
> Is that OK to do?
> What's the impact of changing the SA password?
> Anything else that I'm not thinking of'
> Thank you!
> Randy|||Not sure what that means...sql n00b here...
but thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2O%23aEFqHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hi
> Take a look atv sp_password stored procedure in the BOL
>
>
> "RandyH" <RHollaw@.HOTmail.com> wrote in message
> news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>>one production database is on this server and I've confirmed with the
>>vendor that the database is not using the SA account for anything.
>> Under Security, login, I see that I can r/c on SA and change the password
>> there.
>> Is that OK to do?
>> What's the impact of changing the SA password?
>> Anything else that I'm not thinking of'
>> Thank you!
>> Randy
>|||Thanks, I will use profiler to check that out.
If no one or nothing is using SA, changing the password during production
hours won't affect anything?
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:e%23JhkEFqHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi Randy
> If you are 100% sure that, none of your application or users are using sa
> password then you can change it.
> You can use sol profiler to trace wheather some one is using it or not
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "RandyH" <RHollaw@.HOTmail.com> wrote in message
> news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>>one production database is on this server and I've confirmed with the
>>vendor that the database is not using the SA account for anything.
>> Under Security, login, I see that I can r/c on SA and change the password
>> there.
>> Is that OK to do?
>> What's the impact of changing the SA password?
>> Anything else that I'm not thinking of'
>> Thank you!
>> Randy
>|||I guess that I'm looking for SA in the LoginName or NTUserName Column?
I've been running it for five minutes, and never saw SA scroll by . . .
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:e%23JhkEFqHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi Randy
> If you are 100% sure that, none of your application or users are using sa
> password then you can change it.
> You can use sol profiler to trace wheather some one is using it or not
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "RandyH" <RHollaw@.HOTmail.com> wrote in message
> news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>>one production database is on this server and I've confirmed with the
>>vendor that the database is not using the SA account for anything.
>> Under Security, login, I see that I can r/c on SA and change the password
>> there.
>> Is that OK to do?
>> What's the impact of changing the SA password?
>> Anything else that I'm not thinking of'
>> Thank you!
>> Randy
>|||Hi Randy
It should be ok to change the SA password as you've suggested (I believed
from the Enterprise Manager?), as long as you are doing it using another
user with the sys admin permission on the SQL Server. As far as the impact,
I believe that as long as the sa account is not being used for any jobs or
application (which it should not as part of best practice), then I can't see
it have any major impact.
Thank you
Lucas
"RandyH" <RHollaw@.HOTmail.com> wrote in message
news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>one production database is on this server and I've confirmed with the
>vendor that the database is not using the SA account for anything.
> Under Security, login, I see that I can r/c on SA and change the password
> there.
> Is that OK to do?
> What's the impact of changing the SA password?
> Anything else that I'm not thinking of'
> Thank you!
> Randy|||Thank you for the input!!
I plan to change the password today.
Thanks again!
"Lucas Kartawidjaja" <luke_k@.hotmail.com> wrote in message
news:uSlN2AeqHHA.4100@.TK2MSFTNGP06.phx.gbl...
> Hi Randy
> It should be ok to change the SA password as you've suggested (I believed
> from the Enterprise Manager?), as long as you are doing it using another
> user with the sys admin permission on the SQL Server. As far as the
> impact, I believe that as long as the sa account is not being used for any
> jobs or application (which it should not as part of best practice), then I
> can't see it have any major impact.
> Thank you
> Lucas
> "RandyH" <RHollaw@.HOTmail.com> wrote in message
> news:B69508E4-AFC5-445C-9DD0-CD50054496B9@.microsoft.com...
>>I have a need to reset the SA password on our SQL 2000 SP4 Server. Only
>>one production database is on this server and I've confirmed with the
>>vendor that the database is not using the SA account for anything.
>> Under Security, login, I see that I can r/c on SA and change the password
>> there.
>> Is that OK to do?
>> What's the impact of changing the SA password?
>> Anything else that I'm not thinking of'
>> Thank you!
>> Randy
>

Re-seeding Indexes

I've been working with a SQL 2000 test database extensively and am about
ready to move the database from development to production. I'm going to
delete all of the test data that has built up. What I'd like to do is to be
able to reset the various indexes within the tables so that they start again
from 1 or whatever the first seed number would be for the index. Is there a
way to do this other than remove the seed from each table, save the table,
and then set up the seed again?
ThanksYou could try this... I haven't tried it or even looked at the code so I
take no responsibility if it doesn't work or breaks something :)
http://www.sqlservercentral.com/scripts/contributions/507.asp
"D Long" <msnewsaspen.5.dlong@.spamgourmet.com> wrote in message
news:#TN3wdvnDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I've been working with a SQL 2000 test database extensively and am about
> ready to move the database from development to production. I'm going to
> delete all of the test data that has built up. What I'd like to do is to
be
> able to reset the various indexes within the tables so that they start
again
> from 1 or whatever the first seed number would be for the index. Is there
a
> way to do this other than remove the seed from each table, save the table,
> and then set up the seed again?
> Thanks
>|||Indexes don't have "seeds". They are made up from the data in the column(s)
that the index expression is on. If you remove all the data then there is
nothing in the index. If your talking about an column in the table that has
an IDENTITY property you can issue DBCC CHECKIDENT to reseed the next value.
If your removing ALL the rows you can also use TRUNCATE TABLE which not only
deletes all the data in a snap but resets the IDENTITY value as well.
--
Andrew J. Kelly
SQL Server MVP
"D Long" <msnewsaspen.5.dlong@.spamgourmet.com> wrote in message
news:%23TN3wdvnDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I've been working with a SQL 2000 test database extensively and am about
> ready to move the database from development to production. I'm going to
> delete all of the test data that has built up. What I'd like to do is to
be
> able to reset the various indexes within the tables so that they start
again
> from 1 or whatever the first seed number would be for the index. Is there
a
> way to do this other than remove the seed from each table, save the table,
> and then set up the seed again?
> Thanks
>

Repost: Exporting Information

Hello NG
We have a production database that we have moved the tables to SQLServer and
I have been creating stored procedures to replicate some of the processes
done on the access side - the server does processing extremely faster than
access - Here is my problem I am currently pulling info from SQL SERVER to
Access once a w for wly stats I want to create a stored procedure to
process the wly stats and push them to an access table upon request - we
have multiple warehouses and I would like to have the process coded once in
sql and dump to the access table based off a warehouse variable passed to
the stored procedure - Can SQLServer from a stored procedure export a
recordset into a varible defined Access Table?
TIAFAH
RandyHi Randy,
Yes this is possible...You can do it by using a linked server. You can
find info about linked servers in SQL Server Books Online. I've given
an outline of the general steps you need to do below.
Hope this helps.
=================
1) Open Query Analyzer and log in using an account with SysAdmin
privileges (e.g. sa). Make sure you are using the "master" database.
2) Type in the following:
exec sp_addlinkedserver '<Linked Server Name>', '<MS Access Version>',
'Microsoft.Jet.OLEDB.4.0', '<Full Path To Access DB>'
<Linked Server Name> can be whatever you want it to be, you will use
this to reference the Access DB.
<MS Access Version> is something like 'Access 97' or 'Access 2003' etc
<Full Path To Access DB> is, well, yeah...
3) Execute what you entered in (2) and then clear the Query Analyzer
window of the command
4) Type in the folliwng into Query Analyzer:
sp_addlinkedsrvlogin '<Linked Server Name>', false, '<SQL Server Login
Name>', 'Admin', NULL
<SQL Server Login Name> is the SQL Server login that will access the
Access DB. Make it something like "sa" if you will be executing the
stored proc under your account.
5) Execute the command entered in (4)
OK, after doing that you can now access you MS Access DB from SQL
Server. To reference a table in Access you have to write:
<Linked Server Name>...<Table Name>
You can do Inserts and other things to this table. In your case you
would probably be wanting to Insert values into the table.
Hope that helps.