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
Adrian
Use 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...
> 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...
> 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...
> 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...
> 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
>