How to Start the SQL Server 2008 Script Wizard

  • Article

Applies To: System Center Service Manager 2010 SP1

Use the following procedure as part of your disaster recovery preparation steps to generate a script to capture SQL Server logon and object level permissions. You need to perform this procedure on the computers hosting your Service Manager and data warehouse databases and on the computer hosting SQL Server Reporting Services (SSRS):

  • DWDataMart

  • DWRepository

  • DWStagingAndConfig

  • ServiceManager

  • ReportServer

To start the SQL Server Script wizard

  1. Using an account with Administrator privileges, log on to the computer hosting the Service Manager or data warehouse database.

  2. On the Windows desktop, click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

  3. In the Connect to Server dialog box, follow these steps:

    1. In the Server Type list, select Database Engine.

    2. In the Server Name list, select the server and the instance for your Server Manager database. For example, select computer\INSTANCE1.

    3. In the Authentication list, select Windows Authentication, and then click Connect.

  4. In the Object Explorer pane, expand Databases.

  5. Right-click the database name, point to Tasks, and then click Generate Scripts. For this example, right-click ServiceManager, point to Tasks, and then click Generate Scripts.

  6. In the Script Wizard dialog box, perform the following steps:

    1. On the Welcome to the Generate SQL Server Scripts Wizard page, click Next.

    2. On the Select Database page, select the database you are generating the script for. In this example, select ServiceManager, and then click Next.

    3. On the Choose Script Options page, set all True/False entries to False. In the General area, set Script Logins and Script Object-Level Permissions to True, and then click Next.

    4. On the Choose Object Types page, click Select All, and then click Next.

    5. On the Choose Assemblies page, click Select All, and then click Next.

    6. On the Choose Database Roles page, click Select All, and then click Next.

    7. On the Choose Schemas page, click Select All, and then click Next.

    8. On the Choose Stored Procedures page, click Select All, and then click Next.

    9. On the Choose Tables page, click Select All, and then click Next.

    10. On the Choose User-Defined Table Types page, click Select All, and then click Next.

    11. On the Choose User-Defined Functions page, click Select All, and then click Next.

    12. On the Choose Users page, click Select All, and then click Next.

    13. On the Choose Views page, click Select All, and then click Next.

    14. On the Choose XML Schema Collections page, click Select All, and then click Next.

    15. On the Choose full text catalogs page, click Select All, and then click Next.

    16. On the Output Option page, select Script to file, in the File name text box, enter a location and file name for the script (for example, type C:\Backup\ServiceManagerDatabaseScript.sql), and then click Next.

    17. On the Script Wizard Summary page, click Finish.

    18. On the Generate Script Progress page, make sure that Success is displayed, and then click Close.

  7. Save the script file you just created on a separate physical computer, usually at the same location where you are saving your Service Manager encryption keys.

  8. Run this same procedure on each of the three data warehouse databases and SSRS.

  9. In the event you need to restore a database, use these scripts to restore permissions to the new server.

Did you find this information helpful? Please send your suggestions and comments about System Center Service Manager documentation to scsmdocs@microsoft.com.