|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Do not update Backup physical_device_name
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Do not update Backup physical_device_name
|
|
| christophe 2002-11-27, 2:23 am |
| Hi,
I am running SQLServer 2000 sp1 on Win2k sp2
I have done a first backup of a database using the following command
BACKUP DATABASE [zzzadta001] TO DISK = N'D:\backup\zzzadta001.bak'
WITH INIT , NOUNLOAD , NAME = N'zzzadta001 backup', NOSKIP ,
STATS = 10, NOFORMAT
When i do the following command
select physical_device_name
from backupmediafamily
where media_set_id = (select media_set_id
from backupset where database_name='zzzadta001' and
backup_finish_date = (select max(backup_finish_date)
from backupset where database_name='zzzadta001'))
I get "D:\backup\zzzadta001.bak", fine.
But, I have changed the target directory to d:\bck_US\ and then the
backup command is
BACKUP DATABASE [zzzadta001] TO DISK = N'D:\bck_US\zzzadta001.bak'
WITH INIT , NOUNLOAD , NAME = N'zzzadta001
When I run the select to get the physical name I still get
d:\backup\zzzadta001.bak
But what is strange also is, Backup via Maintenance plan are also done
during the day, and they update properly physical_device_name.
If somebody can help.
thanks
Christophe
| |
| Jyothi Pai [MS] 2002-11-27, 3:23 pm |
| Hi Christophe,
I tested this on SQL 2K SP1 and was unable to repro the problem.
Please try the following and see if that helps:
1. Run the backup command and getdate() after it completes executing:
BACKUP DATABASE [zzzadta001] TO DISK = N'D:\backup\zzzadta001.bak' WITH
INIT , NOUNLOAD , NAME = N'zzzadta001 backup', NOSKIP , STATS = 10,
NOFORMAT
SELECT getdate()
-- Make a note of the date/ time
2. Run the following command to see if it returns the same date/ time as
above:
SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE
database_name='zzzadta001'
-- Make a note of this
3. If it does return the same date/time, run the following command and you
should see the correct backup file:
SELECT physical_device_name FROM backupmediafamily
WHERE media_set_id =
(SELECT media_set_id FROM backupset WHERE database_name='zzzadta001' AND
backup_finish_date =
(SSELECT max(backup_finish_date) FROM backupset WHERE
database_name='zzzadta001'))
4. Then run the BACKUP command pointing to the *new* backup file:
BACKUP DATABASE [zzzadta001] TO DISK = N'D:\bck_US\zzzadta001.bak' WITH
INIT , NOUNLOAD , NAME = N'zzzadta001
SELECT getdate()
-- Make a note of the date/time
5. Run the following command to see if it returns the same date/ time as
above:
SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE
database_name='zzzadta001'
-- Make a note of this
-- If the above does not return the *same* date/time, run the following
command:
SELECT media_set_id FROM msdb..backupset WHERE database_name='zzzadta001'
-- Make a note of the media_set_id it returns
6. Then, run the following command for each media_set_id and see if any of
it returns "D:\bck_US\zzzadta001.bak" correctly:
SELECT physical_device_name FROM msdb..backupmediafamily WHERE media_set_id
IN (< media_set_ids_returned_from_st
ep3>
Regards,
Jyothi Pai
Microsoft Online Support Engineer
Get Secure! – www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: cgilabert@cbdemail.com (christophe)
| Newsgroups: microsoft.public.sqlserver.server
| Subject: Do not update Backup physical_device_name
| Date: 26 Nov 2002 23:52:31 -0800
| Organization: http://groups.google.com/
| Lines: 32
| Message-ID: <1895b70b.0211262352.46af7ef@posting.google.com>
| NNTP-Posting-Host: 62.23.65.210
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1038383551 22335 127.0.0.1 (27 Nov 2002
07:52:31 GMT)
| X-Complaints-To: groups-abuse@google.com
| NNTP-Posting-Date: 27 Nov 2002 07:52:31 GMT
| Path:
cpmsftngxa08!cppssbbsa01.microsoft.com!news-out.cwix.com!newsfeed.cwix.com!o
pentransit.net!wanadoo.fr!proxad.net!fr.clara.net!heighliner.fr.clara.net!ne
wsfeed01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!
sn-xit-02!sn-xit-06!sn-xit-01!sn-xit-08!supernews.com!postnews1.google.com!n
ot-for-mail
| Xref: cpmsftngxa08 microsoft.public.sqlserver.server:240846
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| I am running SQLServer 2000 sp1 on Win2k sp2
|
| I have done a first backup of a database using the following command
| BACKUP DATABASE [zzzadta001] TO DISK = N'D:\backup\zzzadta001.bak'
| WITH INIT , NOUNLOAD , NAME = N'zzzadta001 backup', NOSKIP ,
| STATS = 10, NOFORMAT
|
| When i do the following command
| select physical_device_name
| from backupmediafamily
| where media_set_id = (select media_set_id
| from backupset where database_name='zzzadta001' and
| backup_finish_date = (select max(backup_finish_date)
| from backupset where database_name='zzzadta001'))
|
| I get "D:\backup\zzzadta001.bak", fine.
|
| But, I have changed the target directory to d:\bck_US\ and then the
| backup command is
| BACKUP DATABASE [zzzadta001] TO DISK = N'D:\bck_US\zzzadta001.bak'
| WITH INIT , NOUNLOAD , NAME = N'zzzadta001
|
| When I run the select to get the physical name I still get
| d:\backup\zzzadta001.bak
| But what is strange also is, Backup via Maintenance plan are also done
| during the day, and they update properly physical_device_name.
|
| If somebody can help.
| thanks
| Christophe
|
| |
| christophe 2002-11-28, 5:23 am |
| Hi Jyothi,
your method has helped me to understand the reason why.
SQLServer stores in physical_device_name the backup header
information.
The problem is coming from the NOFORMAT command.
NOFORMAT does not update the header, but with INIT you force the data
to be backed up.
Using FORMAT instead, INIT is by default, has for consequence to
update the physical_device_name each time, even after changing 10
times of directory.
thanks a lot
Christophe
jyothip@online.microsoft.com (Jyothi Pai [MS]) wrote in message news:<9cKuNSllCHA.1640@cpmsftngxa08>...
> Hi Christophe,
>
> I tested this on SQL 2K SP1 and was unable to repro the problem.
>
> Please try the following and see if that helps:
>
> 1. Run the backup command and getdate() after it completes executing:
>
> BACKUP DATABASE [zzzadta001] TO DISK = N'D:\backup\zzzadta001.bak' WITH
> INIT , NOUNLOAD , NAME = N'zzzadta001 backup', NOSKIP , STATS = 10,
> NOFORMAT
> SELECT getdate()
>
> -- Make a note of the date/ time
>
> 2. Run the following command to see if it returns the same date/ time as
> above:
>
> SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE
> database_name='zzzadta001'
>
> -- Make a note of this
>
> 3. If it does return the same date/time, run the following command and you
> should see the correct backup file:
>
> SELECT physical_device_name FROM backupmediafamily
> WHERE media_set_id =
> (SELECT media_set_id FROM backupset WHERE database_name='zzzadta001' AND
> backup_finish_date =
> (SSELECT max(backup_finish_date) FROM backupset WHERE
> database_name='zzzadta001'))
>
> 4. Then run the BACKUP command pointing to the *new* backup file:
>
> BACKUP DATABASE [zzzadta001] TO DISK = N'D:\bck_US\zzzadta001.bak' WITH
> INIT , NOUNLOAD , NAME = N'zzzadta001
> SELECT getdate()
>
> -- Make a note of the date/time
>
> 5. Run the following command to see if it returns the same date/ time as
> above:
>
> SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE
> database_name='zzzadta001'
>
> -- Make a note of this
>
> -- If the above does not return the *same* date/time, run the following
> command:
>
> SELECT media_set_id FROM msdb..backupset WHERE database_name='zzzadta001'
>
> -- Make a note of the media_set_id it returns
>
> 6. Then, run the following command for each media_set_id and see if any of
> it returns "D:\bck_US\zzzadta001.bak" correctly:
>
> SELECT physical_device_name FROM msdb..backupmediafamily WHERE media_set_id
> IN (< media_set_ids_returned_from_st
ep3>
>
> Regards,
> Jyothi Pai
> Microsoft Online Support Engineer
>
>
> Get Secure! – www.microsoft.com/security
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --------------------
> | From: cgilabert@cbdemail.com (christophe)
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: Do not update Backup physical_device_name
> | Date: 26 Nov 2002 23:52:31 -0800
> | Organization: http://groups.google.com/
> | Lines: 32
> | Message-ID: <1895b70b.0211262352.46af7ef@posting.google.com>
> | NNTP-Posting-Host: 62.23.65.210
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1038383551 22335 127.0.0.1 (27 Nov 2002
> 07:52:31 GMT)
> | X-Complaints-To: groups-abuse@google.com
> | NNTP-Posting-Date: 27 Nov 2002 07:52:31 GMT
> | Path:
> cpmsftngxa08!cppssbbsa01.microsoft.com!news-out.cwix.com!newsfeed.cwix.com!o
> pentransit.net!wanadoo.fr!proxad.net!fr.clara.net!heighliner.fr.clara.net!ne
> wsfeed01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!
> sn-xit-02!sn-xit-06!sn-xit-01!sn-xit-08!supernews.com!postnews1.google.com!n
> ot-for-mail
> | Xref: cpmsftngxa08 microsoft.public.sqlserver.server:240846
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Hi,
> |
> | I am running SQLServer 2000 sp1 on Win2k sp2
> |
> | I have done a first backup of a database using the following command
> | BACKUP DATABASE [zzzadta001] TO DISK = N'D:\backup\zzzadta001.bak'
> | WITH INIT , NOUNLOAD , NAME = N'zzzadta001 backup', NOSKIP ,
> | STATS = 10, NOFORMAT
> |
> | When i do the following command
> | select physical_device_name
> | from backupmediafamily
> | where media_set_id = (select media_set_id
> | from backupset where database_name='zzzadta001' and
> | backup_finish_date = (select max(backup_finish_date)
> | from backupset where database_name='zzzadta001'))
> |
> | I get "D:\backup\zzzadta001.bak", fine.
> |
> | But, I have changed the target directory to d:\bck_US\ and then the
> | backup command is
> | BACKUP DATABASE [zzzadta001] TO DISK = N'D:\bck_US\zzzadta001.bak'
> | WITH INIT , NOUNLOAD , NAME = N'zzzadta001
> |
> | When I run the select to get the physical name I still get
> | d:\backup\zzzadta001.bak
> | But what is strange also is, Backup via Maintenance plan are also done
> | during the day, and they update properly physical_device_name.
> |
> | If somebody can help.
> | thanks
> | Christophe
> |
|
|
|
|
|