Restore DB**

Hi
I've backed up my DB called DB1 in SQLServer 2000,
which include some users that owns some objects in
the database such as tables,views,...and these users
related to some logins.
now I want to restore DB1 in another Server,
but my problem is how can I define my logins and make a
relation between them and DB1'users?
(note: if I want to restore my DB1 in first server
there's no problem cause the defination of related logins exists)
or is there anyway to restore these information
without any problem?
any help would be greatly apprreciated.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Hi,
After restoration you can use the system stored procedure to fix the login
issue,
sp_change_users_login
Please refere BOL for more infomation. The only issue is you may need to
create the logins manually based on the first server. After that you can run
the above procedure with required parameters.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||This link should help you
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:
> This link should help you
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
> HTH
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>> Hi
>> I've backed up my DB called DB1 in SQLServer 2000,
>> which include some users that owns some objects in
>> the database such as tables,views,...and these users
>> related to some logins.
>> now I want to restore DB1 in another Server,
>> but my problem is how can I define my logins and make a
>> relation between them and DB1'users?
>> (note: if I want to restore my DB1 in first server
>> there's no problem cause the defination of related logins exists)
>> or is there anyway to restore these information
>> without any problem?
>> any help would be greatly apprreciated.
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

restore database from another server

after restore database from another server to a new server, i cannot delete
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
--
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
--
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> seems not worked
>> let me explain in more details
>> i find the said login (to be removed) owns 3 tables in the old database.
>> when it is restored at a new server, i cannot delete the said login
>> once i delete these 3 tables, the said login can be deleted ( but it is
>> not a solution to me)
>> how can i change the owner of these 3 tables back to 'sa'?
>> Thanks a lot.
>> tony
>>
>> "Keith Kratochvil" <sqlguy.back2u@.comcast.net>
>> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>>
>

restore database from another server

after restore database from another server to a new server, i cannot delete
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.4864@.TK2MSFTNGP05.
phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...agreen">
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.
4864@.TK2MSFTNGP05.phx.gbl...
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql

Restore a sql 2k database to 7

Hi,
I need to restore a db from 2K to 7. I know I can't do a restore from backup, objects transfer is also out of the question because of the compatibility issue. Beside upgrading my server to 2k is there another way for me to accomplish this task? TIASure...bcp out all the data, script sql 2k with 7.0 only options, build the structure in 7.0, bcp all the data in...

good luck

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF