Post reply

Multiple Databases Mirroring Script Fails

  • October 6, 2011 at 2:44 pm

    #249264

    I have database mirroring configured for our Sharepoint databases. We have about 10 databases configured. The databases have long names with spaces, dashes, and characters. ex. PowerPoint Service Application_f9cfdcaf9d4a4b82bcc4b8568b731c34

    I have this sql script to set the databases safety mode, but I am getting this error that I cannot figure out. I can't see what it is that causing the script to fail.

    Here is the sql script:

    --This script alters all mirrored databases to set safety off asynchronous

    --NOTE: Run this script in the PRINCIPAL server instance

    SET NOCOUNT OFF

    DECLARE @strSQL NVARCHAR(400) --variable for dynamic SQL statement - variable size should change depending on the

    DECLARE @strDatabasename NVARCHAR(200) --variable for destination directory

    DECLARE MyCursor CURSOR FOR --used for cursor allocation

    SELECT name FROM master.sys.databases a

    INNER JOIN master.sys.database_mirroring b

    ON a.database_id=b.database_id

    WHERE NOT mirroring_guid IS NULL

    AND mirroring_role_desc='PRINCIPAL'

    OPEN MyCursor

    FETCH Next FROM MyCursor INTO @strDatabasename

    WHILE @@Fetch_Status = 0

    BEGIN

    ---Run the ALTER DATABASE databaseName SET SAFETY MODE TO ASYNCHRONOUS

    SET @strSQL = 'ALTER DATABASE ' + @strDatabaseName + ' SET SAFETY OFF'

    EXEC sp_executesql @strSQL

    PRINT 'SETTING ' + @strDatabaseName + ' to ASYNCHRONOUS'

    PRINT '========================================'

    FETCH Next FROM MyCursor INTO @strDatabasename

    END

    CLOSE MyCursor

    DEALLOCATE MyCursor

    Here is the output and errors I receive:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    Msg 195, Level 15, State 5, Line 1

    'SAFETY' is not a recognized SET option.

    SETTING SharePoint_AdminContent_9d1e8a38-4c96-4eb1-a754-872f7a24ba0d to ASYNCHRONOUS

    ========================================

    SETTING SharePoint_Config to ASYNCHRONOUS

    ========================================

    SETTING Metadata_DB to ASYNCHRONOUS

    ========================================

    SETTING Search_Service_Application_DB_706da7c7f5784850a8f200a7fff2102b to ASYNCHRONOUS

    ========================================

    SETTING Search_Service_Application_PropertyStoreDB_5fd2be98daeb4670a51b22abadd95de6 to ASYNCHRONOUS

    ========================================

    SETTING Search_Service_Application_CrawlStoreDB_0635193f9ff3412db1b6594be1f646dc to ASYNCHRONOUS

    ========================================

    SETTING WSS_UsageApplication to ASYNCHRONOUS

    ========================================

    SETTING StoragePoint to ASYNCHRONOUS

    ========================================

    SETTING WSS_Content to ASYNCHRONOUS

    ========================================

    SETTING WSS_Content_63c5fae3eed6494ca64bfed19193bc94 to ASYNCHRONOUS

    ========================================

    SETTING Profile_DB_NYT1 to ASYNCHRONOUS

    ========================================

    SETTING Sync_DB_NYT1 to ASYNCHRONOUS

    ========================================

    SETTING Social_DB_NYT1 to ASYNCHRONOUS

    ========================================

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Service'.

    Msg 195, Level 15, State 5, Line 1

    'SAFETY' is not a recognized SET option.

    SETTING PowerPoint Service Application_f9cfdcaf9d4a4b82bcc4b8568b731c34 to ASYNCHRONOUS

    ========================================

    These two databases were not updated:

    PowerPoint Service Application_f9cfdcaf9d4a4b82bcc4b8568b731c34

    SharePoint_AdminContent_9d1e8a38-4c96-4eb1-a754-872f7a24ba0d

    Thanks for a second set of eyes.

    Patti

  • October 6, 2011 at 2:50 pm

    #1391497

    Tried this?

    SET @strSQL = 'ALTER DATABASE ' + quotename(@strDatabaseName) + ' SET SAFETY OFF'

  • October 6, 2011 at 2:55 pm

    #1391502

    Awesome. No I didn't try that. I decided because I was on a time crunch to hard code the script for the databases using [dbname]. I will try what you suggested. Thanks Lynn very much. I won't know for a few days. We are going to do another test and then I can try again.

    Patti

  • October 6, 2011 at 2:55 pm

    #1391503

    Hey Lynn, while I gotcha. What does quotename do?

  • October 6, 2011 at 3:04 pm

    #1391506

    Patti Johnson (10/6/2011)

    Hey Lynn, while I gotcha. What does quotename do?

    Would you get mad if I asked you to look it up in BOL?

  • October 6, 2011 at 3:05 pm

    #1391507

    Lynn, I realized that since I am setting the safety to off I could run it. I made the change and it work.

    Thanks alot.

  • October 6, 2011 at 3:08 pm

    #1391509

    The key to your problem is the spaces in the database name. You need to surround the name with [], just like you would with column names that contain spaces (or start with numbers).

  • October 8, 2011 at 7:40 am

    #1392059

    Lynn Pettis (10/6/2011)

    The key to your problem is the spaces in the database name. You need to surround the name with [], just like you would with column names that contain spaces (or start with numbers).

    The underscores cause a real problem too

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

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