Hi,
You can try to export the login information out of master..syslogins on SQL
Server 7 to a file the imported into SQL 2000 server.
Restore your user database onto SQL 2000 server then resolve the logins for
the database just restored. The steps should be something like the
following:
"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" master..syslogins
out "c:\data\syslogins.dat" /T /N /S <SQL7 servername>
Copy the syslogins.dat file to the SQL2000 server drive, say
C:\SQL2000\syslogins.dat
Then run the following commands to create logins for the database on SQL2000
server.
use master
go
exec sp_resolve_logins
@dest_db = '<databasename>',
@dest_path = 'C:\SQL2000\',
@filename = 'syslogins.dat'
go
use <databasename>
go
-- Get the list of login names to be resolved
exec sp_change_users_login report
-- Use the following sample statement to resolve the login
--EXEC sp_change_users_login 'Update_One', 'user1', 'user1'
I am not sure this will work, but I think it will be worth the try
especially if you have lots of users to be migrated.
Sang
-----Original Message-----
From: Tom Whitfield
[mailto:mssqldba-ezmlmshield-x51271327.[Email address protected]
Sent: Thursday, August 26, 2004 10:07 AM
To: LazyDBA Discussion
Subject: RE: Hi there...I need to backup and restore database from sql 7
t o sql 2000
This is not entirely true - the logins are stored in the Master database and
should be moved as well. However, a SQL 7 Master database cannot be restored
to a SQL 2000 database.
Microsoft has created a stored procedure to move these logins due to this
problem. The SP copies the logins with all passwords and SIDs intact, even
if the password is encrypted. The document number is Q246133 and can be
found at http://support.microsoft.com/default.aspx?scid=kb;en-us;246133.
Since I found this, I never do Master restores anymore unless the Master
itself has become corrupt.
Once you move the logins, you simply need to backup your user databases on
your SQL 7 box and restore them to your SQL 2000 box. You may also want to
move the MODEL database as well, if you're using it as a template for new
databases.
Thanks,
Tom Whitfield
Senior Support Engineer
Magic Service Desk
Remedy, a BMC Software company
-----Original Message-----
From: Raman
[mailto:mssqldba-ezmlmshield-x83994614.[Email address protected]
Sent: Thursday, August 26, 2004 8:27 AM
To: LazyDBA Discussion
Subject: Re: Hi there...I need to backup and restore database from sql 7
to sql 2000
Dear Prakash,
You no need to take backup of all database (i.e maste, temp etc.,,) you
need to take backup the customised only.
bcs SQL - 2000 having backward compactibilty.
Hope clear.
Regards
Raman
----- Original Message -----
From: "Prakash Koshy "
<mssqldba-ezmlmshield-x17336653.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, August 26, 2004 15:00
Subject: Hi there...I need to backup and restore database from sql 7 to sql
2000
> I am fairly new to sql database.I need to backup and restore database from
> sql 7 to sql 2000. We bought a new compaq server and on it I need to
> install a SQL server 2000 and restore the database.
>
> Presently....we are having SQL 7. I need to backup the database and
restore
> it to a new machine running SQL server 2000.
> How do i go about this....Do i need to backup all the databases...(
master,
> model........etc) or only the customised database "Brains" that we use?
>
> Can i restore it back to a sql 2000 server ?.....will it have any
> compactibility problems.?
>
> Please someone help me out with this.
>
> thank you.
>
> -----Original Message-----
> From: jason_whiffin
> [mailto:mssqldba-ezmlmshield-x38299815.[Email address protected]
> Sent: Thursday, August 26, 2004 11:57 AM
> To: LazyDBA Discussion
> Subject: RE: Query Question
>
>
>
> Also i have used in a query which dynamically builds another actual query
> to run; in my example i was using it to build the join clause of which the
> number of join fields would be variable. Rather than case the first join
> with an "ON" and subsequent joins with an "AND", i used "ON 1=1" for the
> first part of my join.
>
> But if you think that is mad, some of the stuff i've been looking at has
> had coded in t-sql "WHERE 1=0"...........
>
> go figure ! ;)
> rgds
>
>
>
>
>
> "Carter Phillip "
> <mssqldba-ezmlmshield-x37010695.[Email address protected]
> To: "LazyDBA Discussion"
> om>
> <[Email address protected]
>
> cc:
>
> Subject: RE: Query Question
> Thursday August 26, 2004 08:06
>
>
>
>
>
>
>
>
> I've seen this done in a couple of web applications where they pass
> through the field name and criteria to a stored procedure. If they want
> everything back they just pass '1' as the field name and 1 as the
> criteria.
>
> By calling the same stored procedure with different parameters they get
> back different results.
>
> EG: Exec FindCustomers 'lastname', 'smith'
> Results in a query like,
> Select * from customers
> Where lastname = 'smith'
>
>
>
> Thanks
> Phillip Carter
> Ph: +61 3 9235 1691
>
>
> -----Original Message-----
> From: prakash
> [mailto:mssqldba-ezmlmshield-x55741692.[Email address protected]
>
> Sent: Thursday, 26 August 2004 4:40 PM
> To: LazyDBA Discussion
> Subject: Query Question
>
> Hi All,
>
> Why some people always use 1 =1 in there query. Any reasons behind
> this....
>
> Example
>
> Select * from [Table name]
> Where 1 =1
>
>
> Thanks
> Prakash Zalkikar
> SQL DBA
> WebDirekt India Pvt LTD
> C 1/19 Kumar City, Kalyani Nagar
> Pune - 411014
> Phone(O):- 91-20-27031240
> Phone(R):- 91-20-27034557
> Mobile:- 9422314041
> mailto:- p.[Email address protected]
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
> IMPORTANT DISCLAIMER - THIS MAY AFFECT YOUR LEGAL RIGHTS:
>
> Because this document has been prepared without consideration of any
> specific clients investment objectives, financial situation or needs,
> a Bell Potter Securities Limited investment adviser should be
> consulted before any investment decision is made.
>
> While this document is based on the information from sources which
> are considered reliable, Bell Potter Securities Limited, its directors,
> employees and consultants do not represent, warrant or guarantee,
> expressly or impliedly, that the information contained in this document
> is complete or accurate.
>
> Nor does Bell Potter Securities Limited accept any responsibility to
> inform you of any matter that subsequently comes to its notice, which
> may affect any of the information contained in this document.
>
> This document is a private communication to clients and is not intended
> for public circulation or for the use of any third party, without the
> prior approval of Bell Potter Securities.
>
>
> Disclosure of Interest: Bell Potter Securities Limited receives
commission
> from dealing in securities and its authorised representatives, or
> introducers
> of business, may directly share in this commission. Bell Potter Securities
> and its associates may hold shares in the companies recommended.
>
> Bell Potter Securities Limited ABN 25 006 390 772 AFS Licence No. 243480
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page