Prev: Move SQL Database
Next: The affinity mask specified does not match the CPU mask on this system
From: Tibor Karaszi on 15 Sep 2007 03:40
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(a)nospam.com> wrote in message news:OgO3CSy9HHA.5160(a)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(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.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

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