locked
how to easily script out logins, sql jobs , alerts, operators, master objects , etc RRS feed

  • Question

  • Hello,

    I've been looking for an easy way to script out all sql objects (except user databases)  for backup purposes.

    I'd like to automatically script our master objects, sql jobs, alerts, operators and logins.

    is there any way to do it? is there any application to accomplish this goal?

    Thanks

    Javier

    Thursday, January 27, 2011 3:55 PM

Answers

  • You can do that using SQL Management Objects (SMO). Here is a link to get you started.

    http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

    Thursday, January 27, 2011 4:09 PM
  • Why aren't you backing up your system databases?  You should be backing up the master and msdb databases just as you'd backup your user databases on the server.  You can certainly use SMO to script all this stuff out, but I would recommend against that because then you have to start checking those scripts into versioning control to have multiple recovery points and it is a bigger mess than standard backups of the system databases.

    All that said, you don't really need SMO to accomplish this in a one off fashion.  You can script logins with SIDs and password hashes usign the stored procedure provided by Microsoft for doing this.

    Transfer Logins to Another Server

    For all the other stuff in Management Studio, you can hit F7 and have it open the Object Explorer Details window.  If you click on a top level folder, it will display the child objects in the Details panel.  Select all the objects you want to script, it is multi-selectable using Shift+Click or Ctrl+Click, then right click and you can script them to a query window, files, etc.  This works for SQL Agent Jobs as well.

    I'd still recommend that you rely on backups of the databases over doing this though.


    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    Saturday, January 29, 2011 4:59 PM

All replies

  • You can do that using SQL Management Objects (SMO). Here is a link to get you started.

    http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

    Thursday, January 27, 2011 4:09 PM
  • Why aren't you backing up your system databases?  You should be backing up the master and msdb databases just as you'd backup your user databases on the server.  You can certainly use SMO to script all this stuff out, but I would recommend against that because then you have to start checking those scripts into versioning control to have multiple recovery points and it is a bigger mess than standard backups of the system databases.

    All that said, you don't really need SMO to accomplish this in a one off fashion.  You can script logins with SIDs and password hashes usign the stored procedure provided by Microsoft for doing this.

    Transfer Logins to Another Server

    For all the other stuff in Management Studio, you can hit F7 and have it open the Object Explorer Details window.  If you click on a top level folder, it will display the child objects in the Details panel.  Select all the objects you want to script, it is multi-selectable using Shift+Click or Ctrl+Click, then right click and you can script them to a query window, files, etc.  This works for SQL Agent Jobs as well.

    I'd still recommend that you rely on backups of the databases over doing this though.


    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    Saturday, January 29, 2011 4:59 PM
  • I agree with Jon on both the points .But make sure that the patch level on the target SQL Server instance is same as that of the instance you are backing up system databases from .

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, January 30, 2011 6:17 AM
  • I agree with Jon on both the points .But make sure that the patch level on the target SQL Server instance is same as that of the instance you are backing up system databases from .


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

    Yes!  That is a great point that I missed in my initial response.  You have to document the patch level that the system databases are at so that you apply the same patches to the environment that the databases will be restored to in the event of a disaster requiring recovery from backups.
    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    Sunday, January 30, 2011 6:24 AM
  • Please always state what vesrion you are using

    --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

    --Problem: when executing CREATE LOGINs generated by the query above,
    --it is not possible to assign default database when that database is
    mirrored or unavailable for some other reason.
    --(filled a bug for that, this needs to be solved)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 30, 2011 8:11 AM
    Answerer
  • Please always state what vesrion you are using

    --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

    --Problem: when executing CREATE LOGINs generated by the query above,
    --it is not possible to assign default database when that database is
    mirrored or unavailable for some other reason.
    --(filled a bug for that, this needs to be solved)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 30, 2011 8:12 AM
    Answerer
  • Uri,

    No offense, but why do you always create a duplicate of your post?

    Sunday, January 30, 2011 4:14 PM