COPY_ONLY Restore Problems

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.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@.nospam.com> wrote in message
news:Oh14Hmh9HHA.1208@.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.
>|||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@.earthlink.net> wrote in message
news:ufl2b$h9HHA.5980@.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@.nospam.com> wrote in message
> news:Oh14Hmh9HHA.1208@.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.
>|||> 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@.nospam.com> wrote in message news:euirpti9HHA.5316@.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@.earthlink.net> wrote in message
> news:ufl2b$h9HHA.5980@.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@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.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.
>>
>|||> 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@.hotmail.nomail.com> wrote in
message news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.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@.nospam.com> wrote in message
> news:euirpti9HHA.5316@.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@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.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@.nospam.com> wrote in message
>> news:Oh14Hmh9HHA.1208@.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.
>>
>>
>|||NOINIT is not the problem. Let me try it and see if I get the same behaviour in the GUI as you
describe:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
Right-click Databases folder, Restore Database, Type in "pubs" for database name, select "from
device", press "..."
Backup media: File
File name: C:\pubs.bak, OK
OK
... and indeed, there is nothing listed!
OK, lets do the same except I don't specify COPY_ONLY... And now the backup is listed! So, my
apologies. I was incorrect. I'm surprised that the backup somehow indicated it was done using
COPY_ONLY. Let me try something else:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT, COPY_ONLY
RESTORE HEADERONLY FROM DISK = N'C:\pubs.bak'
Yes, RESTORE HEADERONLY does indicate whether the backup was done using COPY_ONLY. I see a
difference in the "flags" column as well as the "IsCopyOnly" column. And the restore dialog only
show the first backup. Let me now try the other way around:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT
Now the restore dialog only show the second backup in the backup file (position 2). I get the same
result if I type in some other database name to restore into (a non-existing database).
So, the restore dialog does indeed refuse to list backups done using COPY_ONLY. So here's another
reason to type the RESTORE command instead of relying on how the GUI developer believe the restore
should be done... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Miller" <nospam@.nospam.com> wrote in message news:OgO3CSy9HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> 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@.hotmail.nomail.com> wrote in message
> news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.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@.nospam.com> wrote in message news:euirpti9HHA.5316@.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@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.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@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.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.
>>
>>
>>
>