backup problems

Hi all,
I have a database on SQL Server 2000, and I back it up from time to time.
At one point, I moved the location of the backup file. Since then, every
time I want to do a backup, I get the old location;
I add the new one, remove the old one, do the backup (successfully), but
next time I still get the old one.
How can I fix this problem?
Thanks
AdrianUse T-SQL
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You will need to replace "foo" with the name of the database that you want
to back up
You will also want to replace "c:\" with a physical path on your database
server. That will be the location of your backup file.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:e%23gL201SFHA.2756@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a database on SQL Server 2000, and I back it up from time to time.
> At one point, I moved the location of the backup file. Since then, every
> time I want to do a backup, I get the old location;
> I add the new one, remove the old one, do the backup (successfully), but
> next time I still get the old one.
> How can I fix this problem?
> Thanks
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Use T-SQL
> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling
an automatic backup. Or do you still suggest using T-SQL for that?
Adrian|||You can schedule the appropriate sql statement to run within the jobs node
under the management folder within Enterprise Manager.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:eQhVGM2SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
>> Use T-SQL
>> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
> Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of
> scheduling an automatic backup. Or do you still suggest using T-SQL for
> that?
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> You can schedule the appropriate sql statement to run within the jobs node
> under the management folder within Enterprise Manager.
Thanks, I will look into that.
But even if I am able to do everything I need, I'm still wondering if there
is a solution to that unpleasant behavior described in my first message.
Where does Sql Server store the information about backup destinations, and
how can I change it?
Adrian|||Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Key: BackupDirectory
Adjust the path if you are using a named instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:%23ZohBo2SFHA.264@.tk2msftngp13.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
>> You can schedule the appropriate sql statement to run within the jobs
>> node under the management folder within Enterprise Manager.
> Thanks, I will look into that.
> But even if I am able to do everything I need, I'm still wondering if
> there is a solution to that unpleasant behavior described in my first
> message. Where does Sql Server store the information about backup
> destinations, and how can I change it?
> Adrian
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> Key: BackupDirectory
The path I found there is neither the old one nor the new one, but it's the
"Program Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used
for making backups). I'm not sure what it is for, but it's quite clear it's
not what I need.
To be clearer, I used to back up that database to the file
"D:\db_backup\business" and then I moved it to "D:\backups\sql
server\business". I keep getting the "D:\db_backup\business" path when I try
to make a backup from Ent. Manager.
"business" is a file with no extension, not a folder.
Adrian|||Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:u3AbCW8SFHA.3636@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
>> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
>> Key: BackupDirectory
> The path I found there is neither the old one nor the new one, but it's the "Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used for making backups). I'm not
> sure what it is for, but it's quite clear it's not what I need.
> To be clearer, I used to back up that database to the file "D:\db_backup\business" and then I
> moved it to "D:\backups\sql server\business". I keep getting the "D:\db_backup\business" path when
> I try to make a backup from Ent. Manager.
> "business" is a file with no extension, not a folder.
> Adrian
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Perhaps EM reads backup history tables? You could check out
> sp_delete_backuphistory.
Starting from your suggestion and digging further, I found the old paths in
the backupmediafamily table in the msdb database.
The field name is physical_device_name.
I wonder if it is safe to change the paths there. Or is there a stored
procedure for that?
Thanks
Adrian|||I wouldn't hack this. The question is why EM connects a database with this backupmodeiafamily? I
don't know as I don't use EM for things like these. I thought that EM suggests a backup device which
was the *last one* used for that database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:%23034Fy8SFHA.3464@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old paths in the backupmediafamily
> table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored procedure for that?
> Thanks
> Adrian
>|||Could it be that you are backing up to a Backup Device that you have created
in EM? As Tibor, I'm not an expert on how EM does this since I'm not using
it either, but when you look at the Backup Device properties in EM, it has a
"physical location" and it might be this that are causing the issue. I think
that you'll have to drop this backup device and then re-create it to get the
right location back. You might also be able to change the path in the MSDB
database, but I don't know if it's as simple as that.
Regards
Steen
Adrian Sandor wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out
>> sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old
> paths in the backupmediafamily table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored
> procedure for that?
> Thanks
> Adrian|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uRt6zPITFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Could it be that you are backing up to a Backup Device that you have
> created in EM?
Yes, most likely.
I don't think I've ever used any other way to create a backup.
> As Tibor, I'm not an expert on how EM does this since I'm not using it
> either,
Is it really so unusual/uncivilized to make backups in EM?
> but when you look at the Backup Device properties in EM, it has a
> "physical location" and it might be this that are causing the issue.
I think I didn't use a "Backup Device", but just a "File Name" (I get the
choice when I add a new backup destination)
> that you'll have to drop this backup device and then re-create it to get
> the
> right location back.
Hm, creating a new file might help, I will try that.
> You might also be able to change the path in the MSDB database, but I
> don't know if it's as simple as that.
Oh.. then maybe I should keep it as a last resort.
Thanks
Adrian|||Oh, I forgot to report my progress
>> that you'll have to drop this backup device and then re-create it to get
>> the right location back.
> Hm, creating a new file might help, I will try that.
Well, that helped. Apparently, when creating a backup in a new path, the
file must not already exist.
Adrian