How to Start the SQL Server 2008 R2 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 R2, 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 Generate and Publish Scripts wizard, perform the following steps:

    1. On the Introduction page, click Next.

    2. On the Choose Objects page, select Select specific database objects, and then click Select All.

    3. In the database objects list, expand Tables.

    4. Clear the check box for the following tables:

      • dbo.STG_Collation

      • dbo.STG_Locale

      • dbo.STG_MTD_ConverisonLog

    5. Scroll up to the top of the list and collapse Tables.

    6. Expand Stored Procedures.

    7. Clear the check box for the following stored procedures:

      • dbo.STG_DTS_ConvertToUnicode

      • dbo.STG_DTS_CreateClonedTable

      • dbo.STG_DTS_InsertSQL

      • dbo.STG_DTS_ValidateConversion

    8. Click Next.

    9. On the Set Scripting Options page, select Save scripts, select Save to file, select Single file, specify a file location in the File name box, and then click Next.

    10. On the Summary page, click Next.

    11. When the script is complete, on the Save or Publish Scripts page, click Finish.

  7. If you need to restore a database, use this script to set permissions.

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