• Article

How to Start the SQL Server 2008 R2 Script Wizard

 

Updated: May 13, 2016

Applies To: System Center 2012 SP1 - Service Manager, System Center 2012 R2 Service Manager, System Center 2012 - Service Manager

You can use the following procedure as part of your disaster recovery preparation steps for System Center 2012 – Service Manager to generate a script to capture SQL Server logon permissions and object-level permissions. You perform this procedure on the computer that hosts SQL Server Reporting Services (SSRS) and on the computers that host the following Service Manager and data warehouse databases:

  • DWDataMart

  • DWRepository

  • DWStagingAndConfig

  • ServiceManager

  • ReportServer

To start the SQL Server Script Wizard

  1. Using an account with Administrator privileges, log on to the computer that hosts 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, do the following:

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

    2. In the Server Name list, select the server and the instance for your Service 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, do the following:

    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 then 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 File name, 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.