hi,
I took backup of the database and restored in some other location say mirror
database.
In the mirror database, I add some constraints by eliminating duplicates and
foreign key constraints.
In the meantime some data would have been updated or inserted inside the
original database.
How to identify the updated or inserted records without using triggeres. I
am also not allowed to add timestamp column to the structure.
pls help me to solve this.
thanks in advance
regds
vanithaVanitha
Take Transaction log backup with no truncate option from the main server
and apply it in the second server.
However, If you have made any changes to DML in the new database( as you
said constraint etc) then it may not possible.In those circumstances you are
left with no other option but to compare values table by table.
--
Regards
R.D
--Knowledge gets doubled when shared
"vanitha" wrote:
> hi,
> I took backup of the database and restored in some other location say mirr
or
> database.
> In the mirror database, I add some constraints by eliminating duplicates a
nd
> foreign key constraints.
> In the meantime some data would have been updated or inserted inside the
> original database.
> How to identify the updated or inserted records without using triggeres. I
> am also not allowed to add timestamp column to the structure.
> pls help me to solve this.
> thanks in advance
> regds
> vanitha
>|||If you're preparing a new database model be sure to test it well before you
deploy it into production.
For a final migration of data from the old database into the new one it
would seem in your case that the old system should be made inaccessible to
users for the duration of the migration process.
What exactly are you trying to acomplish?
ML|||no the production db will not go offline.
after applying constraints I will make the mirror db as the production db.
In the meantime I shd find the updated record and update that records also.
downtime for the production db will be only 1 hr.
thanks
vanitha
"ML" wrote:
> If you're preparing a new database model be sure to test it well before yo
u
> deploy it into production.
> For a final migration of data from the old database into the new one it
> would seem in your case that the old system should be made inaccessible to
> users for the duration of the migration process.
> What exactly are you trying to acomplish?
>
> ML|||there are millions of records so i can't chk record by record.
"R.D" wrote:
> Vanitha
> Take Transaction log backup with no truncate option from the main server
> and apply it in the second server.
> However, If you have made any changes to DML in the new database( as you
> said constraint etc) then it may not possible.In those circumstances you a
re
> left with no other option but to compare values table by table.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "vanitha" wrote:
>sql
Showing posts with label restored. Show all posts
Showing posts with label restored. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Database (Standby / Read Only)
Hi all,
This morning when I tried to test my restores, they all went well except for
one Database which when restored went into a "Standby / Read Only" mode, its
greyed out and I can't for the life of me get it to go back to Normal.
I have googled, searched through the two MS SQL 2005 books I have but I cant
seem to get any answer to why it happend or more importantly how to fix the
problem.
The only options I am given when I right click the Dbs is to restore the
transaction log but when I navigate to the my backup (which I restored from)
it wont show any logs. I get a " You must select a restore Source" error
message. But I am pointing to a *.trn file.
Im not a dba by any means more of a babysitter so this could be something
simple.
Could anyone shed some light please.Hello Adrian!
There are three Recovery State when you perform a restore operation.
You can choose one of them while restoring from GUI or T-SQL.
If you want to use GUI (as you said you do not know much about SQL Server,
I'll tell you this way) then go from the following path:
Open SQL Server Management Studio, connect to your instance and right click
on the Databases and click Restore Database.
Type some name to "To database:" or choose one. Click "From device" and go
find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
from the backup sets listbox.
Go to Options and from the Recovery State (which is at the bottom of the
pane) be sure you select the first one (RESTORE WITH RECOVERY).
When you set this option as RESTORE WITH NORECOVERY, then your database will
be inaccessible.
When you set it as RESTORE WITH STANDBY, then your users can use this
database for reporting purposes. But they can not use UPDATE and DELETE,
they can only use SELECT against your database in STANDBY mode.
You can learn more about "Backing Up and Restoring Databases in SQL Server"
from BOL:
http://msdn2.microsoft.com/en-us/library/ms187048.aspx
Ekrem Ã?nsoy
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> Hi all,
> This morning when I tried to test my restores, they all went well except
> for
> one Database which when restored went into a "Standby / Read Only" mode,
> its
> greyed out and I can't for the life of me get it to go back to Normal.
> I have googled, searched through the two MS SQL 2005 books I have but I
> cant
> seem to get any answer to why it happend or more importantly how to fix
> the
> problem.
> The only options I am given when I right click the Dbs is to restore the
> transaction log but when I navigate to the my backup (which I restored
> from)
> it wont show any logs. I get a " You must select a restore Source" error
> message. But I am pointing to a *.trn file.
> Im not a dba by any means more of a babysitter so this could be something
> simple.
> Could anyone shed some light please.
>|||Hi Ekrem Ã?nsoy,
You were spot on! Thank you very much, I was getting worried for a while :)
Have a nice day!
"Ekrem Ã?nsoy" wrote:
> Hello Adrian!
>
> There are three Recovery State when you perform a restore operation.
> You can choose one of them while restoring from GUI or T-SQL.
> If you want to use GUI (as you said you do not know much about SQL Server,
> I'll tell you this way) then go from the following path:
> Open SQL Server Management Studio, connect to your instance and right click
> on the Databases and click Restore Database.
> Type some name to "To database:" or choose one. Click "From device" and go
> find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
> from the backup sets listbox.
> Go to Options and from the Recovery State (which is at the bottom of the
> pane) be sure you select the first one (RESTORE WITH RECOVERY).
> When you set this option as RESTORE WITH NORECOVERY, then your database will
> be inaccessible.
> When you set it as RESTORE WITH STANDBY, then your users can use this
> database for reporting purposes. But they can not use UPDATE and DELETE,
> they can only use SELECT against your database in STANDBY mode.
> You can learn more about "Backing Up and Restoring Databases in SQL Server"
> from BOL:
> http://msdn2.microsoft.com/en-us/library/ms187048.aspx
>
> --
> Ekrem Ã?nsoy
>
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> > Hi all,
> >
> > This morning when I tried to test my restores, they all went well except
> > for
> > one Database which when restored went into a "Standby / Read Only" mode,
> > its
> > greyed out and I can't for the life of me get it to go back to Normal.
> >
> > I have googled, searched through the two MS SQL 2005 books I have but I
> > cant
> > seem to get any answer to why it happend or more importantly how to fix
> > the
> > problem.
> >
> > The only options I am given when I right click the Dbs is to restore the
> > transaction log but when I navigate to the my backup (which I restored
> > from)
> > it wont show any logs. I get a " You must select a restore Source" error
> > message. But I am pointing to a *.trn file.
> >
> > Im not a dba by any means more of a babysitter so this could be something
> > simple.
> >
> > Could anyone shed some light please.
> >
>
This morning when I tried to test my restores, they all went well except for
one Database which when restored went into a "Standby / Read Only" mode, its
greyed out and I can't for the life of me get it to go back to Normal.
I have googled, searched through the two MS SQL 2005 books I have but I cant
seem to get any answer to why it happend or more importantly how to fix the
problem.
The only options I am given when I right click the Dbs is to restore the
transaction log but when I navigate to the my backup (which I restored from)
it wont show any logs. I get a " You must select a restore Source" error
message. But I am pointing to a *.trn file.
Im not a dba by any means more of a babysitter so this could be something
simple.
Could anyone shed some light please.Hello Adrian!
There are three Recovery State when you perform a restore operation.
You can choose one of them while restoring from GUI or T-SQL.
If you want to use GUI (as you said you do not know much about SQL Server,
I'll tell you this way) then go from the following path:
Open SQL Server Management Studio, connect to your instance and right click
on the Databases and click Restore Database.
Type some name to "To database:" or choose one. Click "From device" and go
find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
from the backup sets listbox.
Go to Options and from the Recovery State (which is at the bottom of the
pane) be sure you select the first one (RESTORE WITH RECOVERY).
When you set this option as RESTORE WITH NORECOVERY, then your database will
be inaccessible.
When you set it as RESTORE WITH STANDBY, then your users can use this
database for reporting purposes. But they can not use UPDATE and DELETE,
they can only use SELECT against your database in STANDBY mode.
You can learn more about "Backing Up and Restoring Databases in SQL Server"
from BOL:
http://msdn2.microsoft.com/en-us/library/ms187048.aspx
Ekrem Ã?nsoy
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> Hi all,
> This morning when I tried to test my restores, they all went well except
> for
> one Database which when restored went into a "Standby / Read Only" mode,
> its
> greyed out and I can't for the life of me get it to go back to Normal.
> I have googled, searched through the two MS SQL 2005 books I have but I
> cant
> seem to get any answer to why it happend or more importantly how to fix
> the
> problem.
> The only options I am given when I right click the Dbs is to restore the
> transaction log but when I navigate to the my backup (which I restored
> from)
> it wont show any logs. I get a " You must select a restore Source" error
> message. But I am pointing to a *.trn file.
> Im not a dba by any means more of a babysitter so this could be something
> simple.
> Could anyone shed some light please.
>|||Hi Ekrem Ã?nsoy,
You were spot on! Thank you very much, I was getting worried for a while :)
Have a nice day!
"Ekrem Ã?nsoy" wrote:
> Hello Adrian!
>
> There are three Recovery State when you perform a restore operation.
> You can choose one of them while restoring from GUI or T-SQL.
> If you want to use GUI (as you said you do not know much about SQL Server,
> I'll tell you this way) then go from the following path:
> Open SQL Server Management Studio, connect to your instance and right click
> on the Databases and click Restore Database.
> Type some name to "To database:" or choose one. Click "From device" and go
> find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
> from the backup sets listbox.
> Go to Options and from the Recovery State (which is at the bottom of the
> pane) be sure you select the first one (RESTORE WITH RECOVERY).
> When you set this option as RESTORE WITH NORECOVERY, then your database will
> be inaccessible.
> When you set it as RESTORE WITH STANDBY, then your users can use this
> database for reporting purposes. But they can not use UPDATE and DELETE,
> they can only use SELECT against your database in STANDBY mode.
> You can learn more about "Backing Up and Restoring Databases in SQL Server"
> from BOL:
> http://msdn2.microsoft.com/en-us/library/ms187048.aspx
>
> --
> Ekrem Ã?nsoy
>
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> > Hi all,
> >
> > This morning when I tried to test my restores, they all went well except
> > for
> > one Database which when restored went into a "Standby / Read Only" mode,
> > its
> > greyed out and I can't for the life of me get it to go back to Normal.
> >
> > I have googled, searched through the two MS SQL 2005 books I have but I
> > cant
> > seem to get any answer to why it happend or more importantly how to fix
> > the
> > problem.
> >
> > The only options I am given when I right click the Dbs is to restore the
> > transaction log but when I navigate to the my backup (which I restored
> > from)
> > it wont show any logs. I get a " You must select a restore Source" error
> > message. But I am pointing to a *.trn file.
> >
> > Im not a dba by any means more of a babysitter so this could be something
> > simple.
> >
> > Could anyone shed some light please.
> >
>
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...
>
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...
>
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...
>> 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,
>|||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...
>> 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,
>|||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...
>> 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,
>|||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...
>> 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,
>>
>|||> 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...
>> 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,
>>
>|||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...
>> 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...
>> 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,
>>
>>
>|||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...
>> 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...
>> 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,
>>
>>
>>
>|||I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
some info lines saying database being upgrade from one number to another
number...like 10 lines like that and then a line saying database
successfullly restored. but it is still in the Recovering... status. Now I
try the same and it says error database already restored.
"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...
>> 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,
>|||You should be able to use the database after the RESTORE...WITH RECOVERY.
You'll need to refresh SSMS to see the new database state.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
>some info lines saying database being upgrade from one number to another
>number...like 10 lines like that and then a line saying database
>successfullly restored. but it is still in the Recovering... status. Now I
>try the same and it says error database already restored.
>
> "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...
>> 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,
>>
>|||Great, thanks Tibor and Dan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GAQLK4bGHA.3840@.TK2MSFTNGP04.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...
>> 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...
>> 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,
>>
>>
>>
>>|||you say I should... but I am not able to use the database
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHcEqM9bGHA.3956@.TK2MSFTNGP05.phx.gbl...
> You should be able to use the database after the RESTORE...WITH RECOVERY.
> You'll need to refresh SSMS to see the new database state.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
> news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
>>some info lines saying database being upgrade from one number to another
>>number...like 10 lines like that and then a line saying database
>>successfullly restored. but it is still in the Recovering... status. Now I
>>try the same and it says error database already restored.
>>
>> "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...
>> 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,
>>
>>
>|||What does sp_helpdb report for the database status?
EXEC sp_helpdb 'MyDatabase'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
news:uqH9df3cGHA.380@.TK2MSFTNGP04.phx.gbl...
> you say I should... but I am not able to use the database
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OHcEqM9bGHA.3956@.TK2MSFTNGP05.phx.gbl...
>> You should be able to use the database after the RESTORE...WITH RECOVERY.
>> You'll need to refresh SSMS to see the new database state.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
>> news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work.
>>got some info lines saying database being upgrade from one number to
>>another number...like 10 lines like that and then a line saying database
>>successfullly restored. but it is still in the Recovering... status. Now
>>I try the same and it says error database already restored.
>>
>> "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...
>> 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,
>>
>>
>>
>
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...
>> 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,
>|||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...
>> 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,
>|||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...
>> 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,
>|||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...
>> 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,
>>
>|||> 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...
>> 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,
>>
>|||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...
>> 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...
>> 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,
>>
>>
>|||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...
>> 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...
>> 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,
>>
>>
>>
>|||I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
some info lines saying database being upgrade from one number to another
number...like 10 lines like that and then a line saying database
successfullly restored. but it is still in the Recovering... status. Now I
try the same and it says error database already restored.
"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...
>> 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,
>|||You should be able to use the database after the RESTORE...WITH RECOVERY.
You'll need to refresh SSMS to see the new database state.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
>some info lines saying database being upgrade from one number to another
>number...like 10 lines like that and then a line saying database
>successfullly restored. but it is still in the Recovering... status. Now I
>try the same and it says error database already restored.
>
> "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...
>> 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,
>>
>|||Great, thanks Tibor and Dan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GAQLK4bGHA.3840@.TK2MSFTNGP04.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...
>> 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...
>> 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,
>>
>>
>>
>>|||you say I should... but I am not able to use the database
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHcEqM9bGHA.3956@.TK2MSFTNGP05.phx.gbl...
> You should be able to use the database after the RESTORE...WITH RECOVERY.
> You'll need to refresh SSMS to see the new database state.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
> news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work. got
>>some info lines saying database being upgrade from one number to another
>>number...like 10 lines like that and then a line saying database
>>successfullly restored. but it is still in the Recovering... status. Now I
>>try the same and it says error database already restored.
>>
>> "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...
>> 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,
>>
>>
>|||What does sp_helpdb report for the database status?
EXEC sp_helpdb 'MyDatabase'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
news:uqH9df3cGHA.380@.TK2MSFTNGP04.phx.gbl...
> you say I should... but I am not able to use the database
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OHcEqM9bGHA.3956@.TK2MSFTNGP05.phx.gbl...
>> You should be able to use the database after the RESTORE...WITH RECOVERY.
>> You'll need to refresh SSMS to see the new database state.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandro Penate-Diaz" <alexware69@.hotmail.com> wrote in message
>> news:%23BX9eE6bGHA.3872@.TK2MSFTNGP04.phx.gbl...
>>I tried the RESTORE DATABASE MyDatabase WITH RECOVERY...it didnt work.
>>got some info lines saying database being upgrade from one number to
>>another number...like 10 lines like that and then a line saying database
>>successfullly restored. but it is still in the Recovering... status. Now
>>I try the same and it says error database already restored.
>>
>> "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...
>> 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,
>>
>>
>>
>
Subscribe to:
Posts (Atom)