Use SQLCMD to Set single user mode and Detach DB


  • Hello,

    I am trying to detach the ReportServer Database using sqlcmd, but not able to. I tried to Alter database to set it into Single User mode before detaching, but it says "Login Failed" in the SQL logs and specifies the user under which the Reporting Service is running. I also explicitly added this user in the logins, but even then its not working. It says:

    Database state cannot be changed while other users are using the database 'ReportServer'

    Please help. Thanks a lot
    Manoj Deshpande

    Monday, May 11, 2009 7:12 AM


  • Hi All,

    I got it -

    sRptDatabase = "ReportServer"

    sAltDBCmd = "sqlcmd -S " & sServer & " -d master -E -Q""ALTER DATABASE " & sRptDatabase & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE"""

    sCmdDet1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_detach_db '" & sRptDatabase & "','TRUE' "" "

    ObjAltDBExcode1 = WshShell.Run(sAltDBCmd,5,True)

    ObjDetachExcode1 = WshShell.Run(sCmdDet1,5,True)
    Monday, May 11, 2009 7:27 AM