Retrieve all Login - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Retrieve all Login

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-11-27 : 02:13:45

Hi everyone,

Can I retrieve all LOGIN if I restore the master database backup in a newly created SQL server?

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-11-27 : 02:55:26

-dC:\Program Files\Microsoft SQL Server\MSSQL10.TRIACSSVR\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL10.TRIACSSVR\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL Server\MSSQL10.TRIACSSVR\MSSQL\DATA\mastlog.ldf

if these are my Startup Parameters,will i change the "-d", "-e" and "-l" to "-m"?

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-12-01 : 03:21:33

any help here?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-12-01 : 03:47:59

Yes, the logins are retrievable in that way . I would recommend , if you are thinking of transfering login that you script out the logins from one server , and execute the script on the target server.
Your approach will be vary according to the platforms you are using i.e are you transfering between 2 sql server 2005 servers?

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-12-01 : 03:51:44

i'm trying to transfer the whole sql server 2000 to another server machine..

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-12-02 : 01:39:43

In that case , one approach is:
1)Build the other sql server with exactly the SAME directory\path structure as the sql server 1
2)Backup all dbs on sql server 2 (except tempdb
3)Detach all dbs from sql server 1 , and reattach on sql server 2.
4)The alternative to step 3 - is to not transfer the syetem dbs , but script out the logons, DTS, jobs etc , and migrate via scripts

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-02 : 02:05:44

If your paths are not the same between the two systems, then here's what I use instead of restoring master:
http://www.sqlmag.com/article/articleid/16090/16090.html

If you've got a ton of logins, then I'd recommend this instead: http://weblogs.sqlteam.com/tarad/archive/2008/06/24/How-to-transfer-SQL-logins-between-SQL-Server-2005-instances.aspx

For jobs, I just script them out. Idera has a tool to help with this if you've got a lot of jobs.

I never ever restore master unless forced to do so due to corruption or something similar. I instead use the approach jackv mentions when the paths are the same.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-12-04 : 03:31:27

thanks guys..
I'll update you when we transfer our SQL Server to other Server machine..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources