Post reply

Export SQL 2008 Logins to Disaster Recovery site ?

  • November 5, 2009 at 5:23 am

    #217148

    Wanted to get some ideas as to how folks are EXPORTing their SQL Logins (on SQL 2008) from their active site to their D/R site on a daily basis.

    We are using SQL 2008's DB Mirroring to actively populate the application DB's at the D/R site. Unfortunately, the SQL Logins are located in the master..syslogins system table which is not being Mirror'd!

    Any feedback is greatly appreciated.. thx

    BT

  • November 10, 2009 at 7:24 am

    #1077236

    I've built an SSIS 2008 package incorporating the 'Transfer Logins Task'

    BT

  • November 10, 2009 at 8:13 am

    #1077294

    restoring master and msdb will give you all user login and job linked server etc which where present on old server

    Regards

    Ramu

  • November 11, 2009 at 9:08 am

    #1077867

    This is what I use to gen out a list and then run it in the new SQL Server. However, it only works in SQL 2000. I have not seen a script for SQL 2005 yet.

  • November 11, 2009 at 2:22 pm

    #1078041

    Won't this(http://support.microsoft.com/kb/246133) link work for you?

    MJ

  • November 11, 2009 at 3:16 pm

    #1078090

    Try this one, http://support.microsoft.com/kb/918992

    Using this procedure, you can use OSQL to output all the logins to a single file. If you want individual files per login, just loop through the logins using a cursor or while loop.

    EG:

    declare @server varchar(30)

    declare @instance varchar(30)

    declare @cmdText varchar(2000)

    declare @sqlText varchar(2000)

    declare @outputFile varchar(2000)

    declare @login_name sysname

    declare @outputDir varchar(500)

    set @server = cast(serverproperty('ServerName') as varchar(30))

    set @instance = cast(serverproperty('InstanceName') as varchar(30))

    set @outputDir = 'my directory path'

    declare curLgns cursor fast_forward for

    SELECT p.name

    FROM sys.server_principals p

    WHERE p.type IN ( 'S', 'G', 'U' )

    AND p.name <> 'sa'

    AND not (p.name like '%##%')

    ORDER BY p.name

    open curLgns

    fetch next from curLgns into @login_name

    while @@FETCH_STATUS = 0

    begin

    set @sqlText = 'exec DBA..sp_help_revlogin @login_name = ''' + @login_name + ''''

    set @outputFile = @outputDir + @server + isnull('_' + @instance, '') + '_login_' + replace(@login_name, '\', '~') + '.sql'

    set @cmdText = 'osql -S. -E -Q"' + @sqlText + '" -o "' + @outputFile + '" -w255'

    exec master..xp_cmdshell @cmdText, no_output

    fetch next from curLgns into @login_name

    end

    close curLgns

    deallocate curLgns

    --------------------
    Colt 45 - the original point and click interface

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply