Why can't I put database in READ_ONLY using SQLCMD from a remote Server?

  • Hi Everyone,

    I'm using MS SQL 2005 - sqlcmd on a windows 2003 box (yes not complaint!!)    restoring to a SQL 2012 server running Windows 2008R2

    I created a job on SQL 2005 box (T-SQL and Command Exec) 

       1. TSQL step backup to SQL 2012 box via network share
    Successful!
        2.   CMDEXC  step - run sqlcmd to on SQL 2005 box to remotely restore the database on SQL 2012 box

    sqlcmd -E -d master -S  WINSRV_SQL2012 -Q "RESTORE
    DATABASE [HR_DB] FROM DISK = N'E:\Backup\HR_DB.bak ......  

    Successful!
        3. CMDEXC  step  run sqlcmd to on SQL 2005 box to remotely restore the database on SQL 2012 box
    sqlcmd -E -d master -S WINSRV_SQL2012 -Q "ALTER DATABASE  [HR_DB] SET READ_ONLY WITH NO_WAIT"
     No  Error in SQL JOB  - job completes successfully
    I don't see HR_DB on WINSRV_SQL2012  in read_only

    When I run the sqlcmd from dos prompt on SQL 2005 box it does work successfully though... is this a SQL 2005 bug/sp issue with sql cmd in SQL Agent... 
    Unfortunately... it is a 12 year old un supported DB 🙁

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • replace " with no_wait" to "with rollback immediate" !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Which, it's worth noting, will rollback open transactions. Not the end of the world or anything, but worth noting.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • To explain...

    The NO_WAIT option on the ALTER DATABASE means that if the exclusive DB lock cannot be obtained immediately, the command should abort, not wait to try and acquire the lock. Hence, if there was *any* other connection to that DB, the command would run, be unable to get the needed lock, and exit without changing the DB's state.

    ROLLBACK IMMEDIATE says to rollback existing transactions, terminate existing connections and then set the DB to READ_ONLY.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 4 (of 4 total)

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