Database corrupted: management

Showing posts with label management. Show all posts

Wednesday, March 21, 2012

Database backup and maintenance plan

In EM, what are the diffeneces between these two under the Management folder
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.Different way of scheduling, quite simply. To understand why you cannot see backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, it addresses files
directly (see the BACKUP command and DISK = 'filename' option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > In EM, what are the diffeneces between these two under the Management
> folder
> > ?
> >
> > I can schedule the database backup in Backup and also can schedule the
> > database schedule in Database Maintenance Plans ?
> >
> > I set up the full backup and schedule in 'BackUp', it can create the
full
> > backup file in the directory but cannot view contents by right click the
> > item on the right pane of 'Backup' folder.
> >
> >
>|||Look in the SQL Server agent folder. Each time you press "Schedule" in the backup dialog, a SQL
Server Agent job is created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created.
> I had schedule the full backup at every Sunday and differential backup every
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Another difference between the two is that the backup dialog box simply
> > allows you do create/schedule a backup..
> >
> > The database maintenance plan wizard is intended to allow you to set up
> > several jobs which should cover most of your maintenance needs... not only
> > backups, but index maintenance, and dbcc checkdb...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > In EM, what are the diffeneces between these two under the Management
> > folder
> > > ?
> > >
> > > I can schedule the database backup in Backup and also can schedule the
> > > database schedule in Database Maintenance Plans ?
> > >
> > > I set up the full backup and schedule in 'BackUp', it can create the
> full
> > > backup file in the directory but cannot view contents by right click the
> > > item on the right pane of 'Backup' folder.
> > >
> > >
> >
> >
>|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> > Thanks.
> > However, I have setup the backup device for the Northwind database for
> > example in the Management|Backup subfolder, and schedule the full and
> > differential backup for that, it can backup according to the schedule,
> > that's fine, BUT I cannot review the schedule of the backup that I
created.
> >
> > I had schedule the full backup at every Sunday and differential backup
every
> > weekday at 5 pm, how do I review the schedule of these backup ?
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > > Another difference between the two is that the backup dialog box
simply
> > > allows you do create/schedule a backup..
> > >
> > > The database maintenance plan wizard is intended to allow you to set
up
> > > several jobs which should cover most of your maintenance needs... not
only
> > > backups, but index maintenance, and dbcc checkdb...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > > In EM, what are the diffeneces between these two under the
Management
> > > folder
> > > > ?
> > > >
> > > > I can schedule the database backup in Backup and also can schedule
the
> > > > database schedule in Database Maintenance Plans ?
> > > >
> > > > I set up the full backup and schedule in 'BackUp', it can create the
> > full
> > > > backup file in the directory but cannot view contents by right click
the
> > > > item on the right pane of 'Backup' folder.
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

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,
>>
>>
>>
>

Database & Flash Wear Management

Hello everyone,

I'm currently developping a windows .net compact framework application which is basically a local datalogger.

Since my application will log data to the database (located on compact flash card) a few times a second over long period, I wonder if SQL Server Compact Edition offers some mechanism to reduce disk access.
By example, can SQL Server compact edition wait let's say 5-10 "insert into" commands before actually write to the database located on the flash card ?.

Any ideas which could help me to reduce flash wear would be greatly appreciated !

Thanks

The storage engine introduced in SQL Mobile (v3.0) and currently in SQL CE is storage-card aware.

If you feel you need to queue up inserts to further reduce writes to the card, that would be something that

you would need to code into your logger application.

My suggestion, instead of queuing your DML commands would be to periodically perform a Verify

and if needed a Repair on the database itself. See the SqlCeEngine documentation for samples

of how to perform these checks. Repair will reorganize the database (reorder indexes, reclaim

unused page space, etc) and int he process the physical file will be rewritten on the storage card.

Regards,

Darren Shaffer

|||

Thank you very much ! It's very appreciated

Regards,

Emmanuel

database

Hi,
I have installed MS sql server management Studio Express.
Downloaded the AdventureWorks_Data.mdf
Now would like to have this as a database to use.
Right clicked on databases, Attach, in database to Attach added the path to the .mdf, then clicked OK.
The error message is:
Unable to open the AdventureWorks_Date.mdf
Any thoughts please?
Thanks

solved.

It was to do with security

Tuesday, February 14, 2012

Data Transformation Services

How can I make a DTS in MSSQL Server Management Studio Express?

I know how to do that in SQL Server Enterprise Manager.

Is this restricted in the express?

Thanks.

SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"

Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?

Data Transformation Services

How can I make a DTS in MSSQL Server Management Studio Express?

I know how to do that in SQL Server Enterprise Manager.

Is this restricted in the express?

Thanks.

SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Wednesday, March 21, 2012

Database backup and maintenance plan

Database backup and maintenance plan

Database backup and maintenance plan

Wednesday, March 7, 2012

database (Restoring...) forever?

database (Restoring...) forever?

Database & Flash Wear Management

database

Tuesday, February 14, 2012

Data Transformation Services

Data Transformation Services

Database corrupted

Blog Archive

About Me