Database corrupted: backed

Showing posts with label backed. Show all posts

Thursday, March 29, 2012

Database Compatability

Hi everybody,
I am currently developing a website backed by an SQL 2000 datatabse, my
question is can I attach the database to an instance of SQL server 2005
and then re attach the same database to the SQL server 2000 that is on the
Production server without it beinfg upgraded to the 2005 format?
ThanksJason
Yes , you can attach SS2000 db to SS2005 instance , but cannot re-attach it
to SS2000 again
"Jason Chandler" <Jason Chandler@.discussions.microsoft.com> wrote in message
news:C9A0F01E-1AE0-4C88-8DAE-FB09BF220C84@.microsoft.com...
> Hi everybody,
> I am currently developing a website backed by an SQL 2000 datatabse, my
> question is can I attach the database to an instance of SQL server 2005
> and then re attach the same database to the SQL server 2000 that is on the
> Production server without it beinfg upgraded to the 2005 format?
> Thanks

Database Compatability

Hi everybody,
I am currently developing a website backed by an SQL 2000 datatabse, my
question is can I attach the database to an instance of SQL server 2005
and then re attach the same database to the SQL server 2000 that is on the
Production server without it beinfg upgraded to the 2005 format?
Thanks
Jason
Yes , you can attach SS2000 db to SS2005 instance , but cannot re-attach it
to SS2000 again
"Jason Chandler" <Jason Chandler@.discussions.microsoft.com> wrote in message
news:C9A0F01E-1AE0-4C88-8DAE-FB09BF220C84@.microsoft.com...
> Hi everybody,
> I am currently developing a website backed by an SQL 2000 datatabse, my
> question is can I attach the database to an instance of SQL server 2005
> and then re attach the same database to the SQL server 2000 that is on the
> Production server without it beinfg upgraded to the 2005 format?
> Thanks

Database Compatability

Sunday, March 25, 2012

Database Backups & Compression

Hi All

I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.

My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.

Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.

Any suggestions? Thanks in advance guys n gals :-)Log shipping IS a full copy, in the truest sense of the word. It is not a monolithic file, but that is a feature instead of a problem in my opinion.

-PatP|||okay for a moment let;
machine A = live, machine B = warm standby server on a remote site

I believed that when restoring log shipments to machine B, there are inherent problems, due to the nighly backups that take place on machine A.

When the log shipping from machine A continues after it has performed a nightly backup, the transcation log entries that were processed and removed during the backup will not be included in the next log shipment to machine B, thus losing a porion of transactions during that given period. Therefore, to successfully restore on machine B, the monolithic database backup file from machine A would be required initially to restore the database and then applying any log shipments that have been shipped after that night's backup.

Is that incorrect...anybody?

The problem is getting that monolithic file to copy accross the network in a given timeframe - it's too big to achieve but 'they' are insisting that it is done.|||Is that incorrect...anybody?

Bzzzzzzzz ... thank you for playing ... you will get your consolation prize as you head backstage :)

When databases are in full recovery mode, the backup does not mark the transaction log for re-use. That only occurs after the transaction log backup.

Prove it to yourself and the PTB (powers that be) by setting up log shipping with a copy of Northwind on the target box ... that way you aren't fighting the database size issue. GO thru the full backup, and tran backup cycles, then make database mods and see if any are lost (hint: they won't be).

I have, in the past, restored a backup from three months prior and then brought it current by applying log backups from that point forward, even across weekly full backups (storage team issues ... grrrrrrrrrrr!).

And to make the uneducated happy, you could even slowly copy the full backup across the wire to the failover machine weekly.|||The database backup will not truncate the log, so the subsequent log backups will contain all the log entries.

I have not done this with MS SQL backups yet but
To speedup the transfer of your 72 GB database backup;
Consider using rsync (http://www.google.com/search?num=100&hl=en&q=rsync&meta=)
Also consider using an rsyncable gzip (http://www.google.com/search?num=100&hl=en&q=rsyncable+gzip&meta=) to compress the file
At minimum compression it should reduce it to 14 GB at acceptable speed (assuming you don't have images inside your database)
And will allow rsync to only copy the portion inside the gzip file that changed.
You would probably see that only 1.4 GB of the 14 GB is actually transferred across the line (10 times faster).

PS. I can understand why they want full backups. You only need a problem with one log backup (missing or damaged file) and you won't be able to recover past that point. A full nightly backup and 15 min log backups make sense to me.

database backups

We have a Veritas application that backs up our databases at night. There are two databases that are backed up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines below. We the program gets to these two databases the process that is running changes to spid' while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid' while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > We have a Veritas application that backs up our databases at night. There are two databases that are backed
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid' while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
> >
> > Thanks,
> >
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > spid51 Starting up database 'master4IDR'.
> > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > spid51 Starting up database 'model4IDR'.
> > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > --
> > Dan D.
>
>|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
> > Perhaps the autoclose database option is set for these databases. However, considering the names of the
> > database, my guess is that veritas does something strange to these databases, like taking a copy of some
> > database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find
out,
> > I'd be happy to hear... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > > We have a Veritas application that backs up our databases at night. There are two databases that are
backed
> > up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample
lines
> > below. We the program gets to these two databases the process that is running changes to spid' while the
> > other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why
does
> > the process start the database up first? Why would the database not be online already? And what does the
"IN
> > LOAD" phrase mean?
> > >
> > > Thanks,
> > >
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > --
> > > Dan D.
> >
> >
> >sql

Thursday, March 22, 2012

Database Backup question

Greetings...
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
Dan
It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||In order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>
|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.

Database Backup question

Greetings...
I have a tape drive that my data base it backed up to. I'm running SQL
Server 7 and I backup the entire MSSQL7 folder.
I needed to restore some data in the database. The file was going to
restore was my_database_name.mdf.
Is that the actual database file, and if not, what is?
The reason I ask is because when looking at the file in Windows Explorer,
the date modified was from a week ago, and I know the database has been
modified since then...it changes daily of course.
So basically...if I wanted to back up the database not using the backup in
SQL server, but the one I use for my tape drive, which file or files would I
back up? Any why doesn't the date modified show the current date, since the
database has been modified today?
Thanks
DanIn order to successfully backup your .MDF and .NDF files (data and log),
using the scenario that you posed, you must FIRST stop the MSSQLServer
service. While SQL Server is running, those files will be open and you will
not be able to back them up properly.
Another option is to perform an sp_detachdb and once detached, backup the
data file.
You should really use the Backup features in SQL Server however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||It's normal that the database file has an old time stamp even it's being
changed actively. The timestamp seems to reflect the time that the file gets
created.
The .mdf file is the data file, not the backup file. you can't restore from
it. You can use sp_attach_db to attach it. If you want to restore, probably
look for a backup file (with or without extension).
HTH.
"Dan B" <none@.none.com> wrote in message
news:eLzrIfI3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Greetings...
> I have a tape drive that my data base it backed up to. I'm running SQL
> Server 7 and I backup the entire MSSQL7 folder.
> I needed to restore some data in the database. The file was going to
> restore was my_database_name.mdf.
> Is that the actual database file, and if not, what is?
> The reason I ask is because when looking at the file in Windows Explorer,
> the date modified was from a week ago, and I know the database has been
> modified since then...it changes daily of course.
> So basically...if I wanted to back up the database not using the backup
> in SQL server, but the one I use for my tape drive, which file or files
> would I back up? Any why doesn't the date modified show the current date,
> since the database has been modified today?
> Thanks
> Dan
>|||On Tue, 7 Dec 2004 11:08:39 -0700, "Dan B" <none@.none.com> wrote:
>So basically...if I wanted to back up the database not using the backup in
>SQL server, but the one I use for my tape drive, which file or files would I
>back up?
By default, everything in your PRIMARY filegroup is in the one .mdf
file.
You can set up additional filegroups which are separately restorable.
As usual, see BOL.
J.

Wednesday, March 21, 2012

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, StevenWhich version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>|||... or perhaps the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
> Which version are you talking about? If it is 2000 then have a look at the
> sysdbmaintplan_databases table in msdb.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Steven" <Steven@.nospaml.com> wrote in message news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up in any Maintenance plan
>>or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>> Which version are you talking about? If it is 2000 then have a look at
>> the sysdbmaintplan_databases table in msdb.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Steven" <Steven@.nospaml.com> wrote in message
>> news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up
>>in any Maintenance plan or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out
>> using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>

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

Thursday, March 29, 2012

Database Compatability

Database Compatability

Database Compatability

Sunday, March 25, 2012

Database Backups & Compression

database backups

database backups

database backups

Thursday, March 22, 2012

Database Backup question

Database Backup question

Database Backup question

Wednesday, March 21, 2012

Database Backup in a Maintenance Plan

Database Backup in a Maintenance Plan

Wednesday, March 7, 2012

database (Restoring...) forever?

database (Restoring...) forever?

Database corrupted

Blog Archive

About Me