Login

Hi,
I am under the impression that the master database contains all log-in
info for each database, is that correct?
How about the server log-in? If i need to do the server migration, i need
to transfer all the server log-in manually (or using DTS Transfer log-in task)
is that true?
Thanks a lot
Ed
See if this helps:
http://support.microsoft.com/default...b;en-us;246133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
Hi,
I am under the impression that the master database contains all log-in
info for each database, is that correct?
How about the server log-in? If i need to do the server migration, i need
to transfer all the server log-in manually (or using DTS Transfer log-in
task)
is that true?
Thanks a lot
Ed
|||No that is not correct.
Server logins are kept in master.dbo.sysxlogins table, or better yet,
syslogins view.
Each database keeps their own user accounts in sysusers table.
When you move to a new server, you may encounter orphaned users when
restoring databases because the new server SID does not match the source
SID. You can use sp_change_users_login to resync the SID, or use
sp_help_revlogin to script out all logins with their original SIDs and run
it to create logins on the new server.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
> Hi,
> I am under the impression that the master database contains all log-in
> info for each database, is that correct?
> How about the server log-in? If i need to do the server migration, i
need
> to transfer all the server log-in manually (or using DTS Transfer log-in
task)
> is that true?
> Thanks a lot
> Ed
|||Richard,
So if i do a server migration, I need to take care of the Server Log-in
first or User databases Log-in first or doesn't matter...
Thanks again
Ed
"Richard Ding" wrote:

> No that is not correct.
> Server logins are kept in master.dbo.sysxlogins table, or better yet,
> syslogins view.
> Each database keeps their own user accounts in sysusers table.
> When you move to a new server, you may encounter orphaned users when
> restoring databases because the new server SID does not match the source
> SID. You can use sp_change_users_login to resync the SID, or use
> sp_help_revlogin to script out all logins with their original SIDs and run
> it to create logins on the new server.
>
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
> need
> task)
>
>
|||The way I do when migrate server is to install the SQL server first. then
use sp_help_revlogin to create all logins on the new server. Next start
restoring user databases as well as msdb.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:C385D215-78F3-41BF-92BB-6F5431A1BC44@.microsoft.com...[vbcol=seagreen]
> Richard,
> So if i do a server migration, I need to take care of the Server Log-in
> first or User databases Log-in first or doesn't matter...
> Thanks again
> Ed
> "Richard Ding" wrote:
run[vbcol=seagreen]
log-in[vbcol=seagreen]
i[vbcol=seagreen]
log-in[vbcol=seagreen]
|||Richard,
Do you also have any idea about how to restore the Master Database?
What I am planning to do is
1. Backup (Old Server) all the databases (User and System) to a share folder
2. Create each individual user database in New Server
3. Restore each user database and retrive the backup files from the share
folder
4. Restore MSDB, MODEL database
5. Use DTS to transfer all the server log-in to New Server
is there anything wrong on doing this? How about dealing with the Master
Database, it needs to be transfered, too......
Thank you very much
Ed
"Richard Ding" wrote:

> The way I do when migrate server is to install the SQL server first. then
> use sp_help_revlogin to create all logins on the new server. Next start
> restoring user databases as well as msdb.
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:C385D215-78F3-41BF-92BB-6F5431A1BC44@.microsoft.com...
> run
> log-in
> i
> log-in
>
>
|||If you plan to restore master, you need to put the server in single user
mode and run restore master from tape or disk. Refer to BOL on "how to
restore master db".
You don't need to do 2. Try restore master first then user dbs.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:CB2F7FD9-22F4-4721-B60E-8988C2589062@.microsoft.com...
> Richard,
> Do you also have any idea about how to restore the Master Database?
> What I am planning to do is
> 1. Backup (Old Server) all the databases (User and System) to a share
folder[vbcol=seagreen]
> 2. Create each individual user database in New Server
> 3. Restore each user database and retrive the backup files from the share
> folder
> 4. Restore MSDB, MODEL database
> 5. Use DTS to transfer all the server log-in to New Server
> is there anything wrong on doing this? How about dealing with the Master
> Database, it needs to be transfered, too......
> Thank you very much
> Ed
>
>
> "Richard Ding" wrote:
then[vbcol=seagreen]
Log-in[vbcol=seagreen]
yet,[vbcol=seagreen]
source[vbcol=seagreen]
and[vbcol=seagreen]
migration,[vbcol=seagreen]

Login

Hi,
I am under the impression that the master database contains all log-in
info for each database, is that correct?
How about the server log-in? If i need to do the server migration, i need
to transfer all the server log-in manually (or using DTS Transfer log-in tas
k)
is that true?
Thanks a lot
EdSee if this helps:
http://support.microsoft.com/defaul...kb;en-us;246133
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
Hi,
I am under the impression that the master database contains all log-in
info for each database, is that correct?
How about the server log-in? If i need to do the server migration, i need
to transfer all the server log-in manually (or using DTS Transfer log-in
task)
is that true?
Thanks a lot
Ed|||No that is not correct.
Server logins are kept in master.dbo.sysxlogins table, or better yet,
syslogins view.
Each database keeps their own user accounts in sysusers table.
When you move to a new server, you may encounter orphaned users when
restoring databases because the new server SID does not match the source
SID. You can use sp_change_users_login to resync the SID, or use
sp_help_revlogin to script out all logins with their original SIDs and run
it to create logins on the new server.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
> Hi,
> I am under the impression that the master database contains all log-in
> info for each database, is that correct?
> How about the server log-in? If i need to do the server migration, i
need
> to transfer all the server log-in manually (or using DTS Transfer log-in
task)
> is that true?
> Thanks a lot
> Ed|||Richard,
So if i do a server migration, I need to take care of the Server Log-in
first or User databases Log-in first or doesn't matter...
Thanks again
Ed
"Richard Ding" wrote:

> No that is not correct.
> Server logins are kept in master.dbo.sysxlogins table, or better yet,
> syslogins view.
> Each database keeps their own user accounts in sysusers table.
> When you move to a new server, you may encounter orphaned users when
> restoring databases because the new server SID does not match the source
> SID. You can use sp_change_users_login to resync the SID, or use
> sp_help_revlogin to script out all logins with their original SIDs and run
> it to create logins on the new server.
>
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:0FA72DC1-AF86-422E-96CA-FCB183B4FE66@.microsoft.com...
> need
> task)
>
>|||The way I do when migrate server is to install the SQL server first. then
use sp_help_revlogin to create all logins on the new server. Next start
restoring user databases as well as msdb.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:C385D215-78F3-41BF-92BB-6F5431A1BC44@.microsoft.com...[vbcol=seagreen]
> Richard,
> So if i do a server migration, I need to take care of the Server Log-in
> first or User databases Log-in first or doesn't matter...
> Thanks again
> Ed
> "Richard Ding" wrote:
>
run[vbcol=seagreen]
log-in[vbcol=seagreen]
i[vbcol=seagreen]
log-in[vbcol=seagreen]|||Richard,
Do you also have any idea about how to restore the Master Database?
What I am planning to do is
1. Backup (Old Server) all the databases (User and System) to a share folder
2. Create each individual user database in New Server
3. Restore each user database and retrive the backup files from the share
folder
4. Restore MSDB, MODEL database
5. Use DTS to transfer all the server log-in to New Server
is there anything wrong on doing this? How about dealing with the Master
Database, it needs to be transfered, too......
Thank you very much
Ed
"Richard Ding" wrote:

> The way I do when migrate server is to install the SQL server first. then
> use sp_help_revlogin to create all logins on the new server. Next start
> restoring user databases as well as msdb.
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:C385D215-78F3-41BF-92BB-6F5431A1BC44@.microsoft.com...
> run
> log-in
> i
> log-in
>
>|||If you plan to restore master, you need to put the server in single user
mode and run restore master from tape or disk. Refer to BOL on "how to
restore master db".
You don't need to do 2. Try restore master first then user dbs.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:CB2F7FD9-22F4-4721-B60E-8988C2589062@.microsoft.com...
> Richard,
> Do you also have any idea about how to restore the Master Database?
> What I am planning to do is
> 1. Backup (Old Server) all the databases (User and System) to a share
folder[vbcol=seagreen]
> 2. Create each individual user database in New Server
> 3. Restore each user database and retrive the backup files from the share
> folder
> 4. Restore MSDB, MODEL database
> 5. Use DTS to transfer all the server log-in to New Server
> is there anything wrong on doing this? How about dealing with the Master
> Database, it needs to be transfered, too......
> Thank you very much
> Ed
>
>
> "Richard Ding" wrote:
>
then[vbcol=seagreen]
Log-in[vbcol=seagreen]
yet,[vbcol=seagreen]
source[vbcol=seagreen]
and[vbcol=seagreen]
migration,[vbcol=seagreen]

logical device already exists?

I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BAK.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for other system default databases, like msdb, model, etc.
Anybody know what might be the problem?
Bing
When you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:

> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below command
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>

logical device already exists?

I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BA
K.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\
data\MSSQL\BACKUP. I had no problem creating the backup devices for other
system default databases, like msdb, model, etc.
Anybody know what might be the problem?
BingWhen you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:

> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below comma
nd
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>