database (Restoring...) forever?

yesterday I restored a backed up database and it worked ok...the only
problem is that the status of the database in the Server Management Studio
is: database (Restoring...) since yesterday and I tried rebooting the pc but
still the same...anu ideas'
Thanks,Perhaps the database was restored with the NORECOVERY option. Try:
RESTORE DATABASE MyDatabase
WITH RECOVERY
This will rollback in-progress transactions that were not committed at the
time of the backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex D." <alexware69@.hotmail.com> wrote in message
news:OK1SxQ3bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> yesterday I restored a backed up database and it worked ok...the only
> problem is that the status of the database in the Server Management Studio
> is: database (Restoring...) since yesterday and I tried rebooting the pc
> but still the same...anu ideas'
> Thanks,
>|||but if I dont do anything eventually it will exit the (Restoring...)
status'
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23MrzRW3bGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Perhaps the database was restored with the NORECOVERY option. Try:
> RESTORE DATABASE MyDatabase
> WITH RECOVERY
> This will rollback in-progress transactions that were not committed at the
> time of the backup.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:OK1SxQ3bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>|||And yes you are right I did a nonrecovery option cause I was getting a
message that said something like: the tail of the log wasnt backed up or
something like that.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23MrzRW3bGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Perhaps the database was restored with the NORECOVERY option. Try:
> RESTORE DATABASE MyDatabase
> WITH RECOVERY
> This will rollback in-progress transactions that were not committed at the
> time of the backup.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:OK1SxQ3bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>|||Alex D. wrote:
> but if I dont do anything eventually it will exit the (Restoring...)
> status'
>
No. If you have restored it with the NORECOVERY option, it will expect
that you are going to apply a log backup or run the RECOVERY as Dan
suggested.
Regards
Steen|||Hi,Dan
Well, the OP says that it worked well after restoring otherwise it was
thrown the database is loading error ,isn't it?, I have seen something
similar and restart command was fixed that.
BTW, Dan , I'm still waiting for your answer (discussed a few days ago)
about how to specify an option to disable to script out COLLATION clause
when we use SQL DMO objects library:-)))))))))
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23MrzRW3bGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Perhaps the database was restored with the NORECOVERY option. Try:
> RESTORE DATABASE MyDatabase
> WITH RECOVERY
> This will rollback in-progress transactions that were not committed at the
> time of the backup.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:OK1SxQ3bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>|||If you don't plan on restoring log backups, specify REPLACE and RECOVERY.
See RESTORE in the Books Online for a description of the options and
recovery scenarios.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex D." <alexware69@.hotmail.com> wrote in message
news:OoPxCZ3bGHA.1856@.TK2MSFTNGP03.phx.gbl...
> And yes you are right I did a nonrecovery option cause I was getting a
> message that said something like: the tail of the log wasnt backed up or
> something like that.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23MrzRW3bGHA.1320@.TK2MSFTNGP04.phx.gbl...
>|||> Well, the OP says that it worked well after restoring otherwise it was
> thrown the database is loading error ,isn't it?, I have seen something
> similar and restart command was fixed that.
The OP only said that "it worked ok", which I interpreted as no error during
the restore. He didn't say that the database was accessible. I assume he
would get the loading error if he tried to access the database.

> BTW, Dan , I'm still waiting for your answer (discussed a few days ago)
> about how to specify an option to disable to script out COLLATION clause
> when we use SQL DMO objects library:-)))))))))
Tibor jumped in with the following response and I didn't really have
anything to add:
<Excerpt>
Seems you need to set the SQLDMOScript2_NoCollation property.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oyqerb3bGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,Dan
> Well, the OP says that it worked well after restoring otherwise it was
> thrown the database is loading error ,isn't it?, I have seen something
> similar and restart command was fixed that.
> BTW, Dan , I'm still waiting for your answer (discussed a few days ago)
> about how to specify an option to disable to script out COLLATION clause
> when we use SQL DMO objects library:-)))))))))
>
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23MrzRW3bGHA.1320@.TK2MSFTNGP04.phx.gbl...
>|||Dan
Hmm,strange, I did not see any replies from Tibor
Where do I set this property ?
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next
End Sub
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23eSUm3bGHA.4892@.TK2MSFTNGP02.phx.gbl...
> The OP only said that "it worked ok", which I interpreted as no error
> during the restore. He didn't say that the database was accessible. I
> assume he would get the loading error if he tried to access the database.
>
> Tibor jumped in with the following response and I didn't really have
> anything to add:
> <Excerpt>
> Seems you need to set the SQLDMOScript2_NoCollation property.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oyqerb3bGHA.1260@.TK2MSFTNGP05.phx.gbl...
>|||You set it in for the fourth parameter of the script method:
Sub ScriptDB(ByVal strLogin As String, ByVal strPwd As String, _
ByVal strDataBase As String, ByVal StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
sql = CreateObject("SQLDMO.SQLServer")
db = CreateObject("SQLDMO.Database")
objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings
' Connect to local server
sql.Connect("(local)", strLogin, strPwd)
db = sql.Databases(strDataBase, "dbo")
' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script(intOptions, StrFilePath, , SQLDMOScript2_NoCollation)
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script(intOptions, StrFilePath)
End If
Next
End If
Next
MsgBox("Done")
End Sub
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eJMFtu3bGHA.4040@.TK2MSFTNGP02.phx.gbl.
.
> Dan
> Hmm,strange, I did not see any replies from Tibor
> Where do I set this property ?
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
>
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Tables and Triggers, ignoring system
> ' tables and system generated triggers
> For Each genObj In db.Tables
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> For Each objTrigger In genObj.Triggers
> If objTrigger.SystemObject = False Then
> objTrigger.Script intOptions, StrFilePath
> End If
> Next
> End If
> Next
>
> End Sub
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e%23eSUm3bGHA.4892@.TK2MSFTNGP02.phx.gbl...
>