Prev: Move SQL Database
Next: The affinity mask specified does not match the CPU mask on this system
From: Don Miller on 13 Sep 2007 11:19
I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
it to my development machine (only 225mb). I then tried to restore the
database (to a newly named database) from a device (disk), located the
backup file, checked contents in the Specify Backup window (it all seemed to
be there), and then added the file. When I get back to the Restore Database
window, there is NOTHING in the "Select the backup sets to restore:"
window!! So, I can't go any further, cannot go to the Options tab ("You must
select a restore source") and cannot restore the database.

Does anyone know what is going on?

Thanks for any help.


From: TheSQLGuru on 13 Sep 2007 12:03
1) Manually craft a RESTORE statement and try that from SSMS directly. See
if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see
what's up.

2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
makes a difference.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Don Miller" <nospam(a)nospam.com> wrote in message
news:Oh14Hmh9HHA.1208(a)TK2MSFTNGP05.phx.gbl...
>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
>it to my development machine (only 225mb). I then tried to restore the
>database (to a newly named database) from a device (disk), located the
>backup file, checked contents in the Specify Backup window (it all seemed
>to be there), and then added the file. When I get back to the Restore
>Database window, there is NOTHING in the "Select the backup sets to
>restore:" window!! So, I can't go any further, cannot go to the Options tab
>("You must select a restore source") and cannot restore the database.
>
> Does anyone know what is going on?
>
> Thanks for any help.
>


From: Don Miller on 13 Sep 2007 13:27
Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs)
and now I know that it cannot do COPY_ONLY restores (UNdocumented).

I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI
also worked for a non-COPY_ONLY backup).

Thanks for pointing me in the right direction (away from the GUI ;)


"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
news:ufl2b$h9HHA.5980(a)TK2MSFTNGP04.phx.gbl...
> 1) Manually craft a RESTORE statement and try that from SSMS directly.
> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY
> to see what's up.
>
> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
> makes a difference.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Don Miller" <nospam(a)nospam.com> wrote in message
> news:Oh14Hmh9HHA.1208(a)TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>moved it to my development machine (only 225mb). I then tried to restore
>>the database (to a newly named database) from a device (disk), located the
>>backup file, checked contents in the Specify Backup window (it all seemed
>>to be there), and then added the file. When I get back to the Restore
>>Database window, there is NOTHING in the "Select the backup sets to
>>restore:" window!! So, I can't go any further, cannot go to the Options
>>tab ("You must select a restore source") and cannot restore the database.
>>
>> Does anyone know what is going on?
>>
>> Thanks for any help.
>>
>
>


From: Tibor Karaszi on 13 Sep 2007 14:27
> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).

The RESTORE command doesn't differentiate between COPY_ONLY backups and regular backups. In fact,
there's nothing in the backup which indicates it is taken using COPY_ONLY. COPY_ONLY only affects
the database of which you do the backup (not resetting the BCM page if db backup or not emptying the
log if log backup). So my guess is that you didn't specify the correct options in the GUI (like MOVE
and/or REPLACE).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Don Miller" <nospam(a)nospam.com> wrote in message news:euirpti9HHA.5316(a)TK2MSFTNGP04.phx.gbl...
> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).
>
> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI also worked for a
> non-COPY_ONLY backup).
>
> Thanks for pointing me in the right direction (away from the GUI ;)
>
>
> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
> news:ufl2b$h9HHA.5980(a)TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly. See if it works. Also try
>> RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see what's up.
>>
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it makes a difference.
>>
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>> "Don Miller" <nospam(a)nospam.com> wrote in message news:Oh14Hmh9HHA.1208(a)TK2MSFTNGP05.phx.gbl...
>>>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved it to my development
>>>machine (only 225mb). I then tried to restore the database (to a newly named database) from a
>>>device (disk), located the backup file, checked contents in the Specify Backup window (it all
>>>seemed to be there), and then added the file. When I get back to the Restore Database window,
>>>there is NOTHING in the "Select the backup sets to restore:" window!! So, I can't go any further,
>>>cannot go to the Options tab ("You must select a restore source") and cannot restore the
>>>database.
>>>
>>> Does anyone know what is going on?
>>>
>>> Thanks for any help.
>>>
>>
>>
>
>

From: Don Miller on 14 Sep 2007 19:10
> So my guess is that you didn't specify the correct options in the GUI
> (like MOVE and/or REPLACE).

I didn't have a chance to specify options because every time I went to the
Option tab I got "Select the backup sets to restore:" because there was
nothing to select once I added the device(file).

Here is my backup SQL. Would the INIT have anything to do with this problem?

BACKUP DATABASE MyDatabase
TO DISK = N'E:\Backup\COPY_ONLY_MyDatabase_DAILY.BAK'
WITH INIT, COPY_ONLY


"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F(a)microsoft.com...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
>
> The RESTORE command doesn't differentiate between COPY_ONLY backups and
> regular backups. In fact, there's nothing in the backup which indicates it
> is taken using COPY_ONLY. COPY_ONLY only affects the database of which you
> do the backup (not resetting the BCM page if db backup or not emptying the
> log if log backup). So my guess is that you didn't specify the correct
> options in the GUI (like MOVE and/or REPLACE).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Don Miller" <nospam(a)nospam.com> wrote in message
> news:euirpti9HHA.5316(a)TK2MSFTNGP04.phx.gbl...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
>>
>> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the
>> GUI also worked for a non-COPY_ONLY backup).
>>
>> Thanks for pointing me in the right direction (away from the GUI ;)
>>
>>
>> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980(a)TK2MSFTNGP04.phx.gbl...
>>> 1) Manually craft a RESTORE statement and try that from SSMS directly.
>>> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or
>>> FILELISTONLY to see what's up.
>>>
>>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
>>> makes a difference.
>>>
>>> --
>>> TheSQLGuru
>>> President
>>> Indicium Resources, Inc.
>>>
>>> "Don Miller" <nospam(a)nospam.com> wrote in message
>>> news:Oh14Hmh9HHA.1208(a)TK2MSFTNGP05.phx.gbl...
>>>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>>>moved it to my development machine (only 225mb). I then tried to restore
>>>>the database (to a newly named database) from a device (disk), located
>>>>the backup file, checked contents in the Specify Backup window (it all
>>>>seemed to be there), and then added the file. When I get back to the
>>>>Restore Database window, there is NOTHING in the "Select the backup sets
>>>>to restore:" window!! So, I can't go any further, cannot go to the
>>>>Options tab ("You must select a restore source") and cannot restore the
>>>>database.
>>>>
>>>> Does anyone know what is going on?
>>>>
>>>> Thanks for any help.
>>>>
>>>
>>>
>>
>>
>


 |  Next  |  Last
Pages: 1 2
Prev: Move SQL Database
Next: The affinity mask specified does not match the CPU mask on this system