|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
backup problemsHi 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. -- Show quoteKeith "Adrian Sandor" <adi***@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.bac***@comcast.net> wrote in message Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling news:OePMgJ2SFHA.2096@TK2MSFTNGP14.phx.gbl... > Use T-SQL > > BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT 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. -- Show quoteKeith "Adrian Sandor" <adi***@yahoo.com> wrote in message news:eQhVGM2SFHA.2432@TK2MSFTNGP12.phx.gbl... > > "Keith Kratochvil" <sqlguy.bac***@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.bac***@comcast.net> wrote in message Thanks, I will look into that.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. 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: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Adrian Sandor" <adi***@yahoo.com> wrote in message news:%23ZohBo2SFHA.264@tk2msftngp13.phx.gbl... > "Keith Kratochvil" <sqlguy.bac***@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)" <m***@epprecht.net> wrote in message The path I found there is neither the old one nor the new one, but it's the news:uUBaG24SFHA.2424@TK2MSFTNGP09.phx.gbl... > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer > Key: BackupDirectory "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.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Adrian Sandor" <adi***@yahoo.com> wrote in message news:u3AbCW8SFHA.3636@TK2MSFTNGP14.phx.gbl... > > "Mike Epprecht (SQL MVP)" <m***@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_kara***@hotmail.nomail.com> wrote in Starting from your suggestion and digging further, I found the old paths in message news:e6DLyZ8SFHA.560@TK2MSFTNGP10.phx.gbl... > Perhaps EM reads backup history tables? You could check out > sp_delete_backuphistory. 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: Show quote > "Tibor Karaszi" <tibor_please.no.email_kara***@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" <S**@REMOVEdatea.dk> wrote in message Yes, most likely.news:uRt6zPITFHA.2128@TK2MSFTNGP15.phx.gbl... > Could it be that you are backing up to a Backup Device that you have > created in EM? 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 Is it really so unusual/uncivilized to make backups in EM?> either, > but when you look at the Backup Device properties in EM, it has a I think I didn't use a "Backup Device", but just a "File Name" (I get the > "physical location" and it might be this that are causing the issue. choice when I add a new backup destination) > that you'll have to drop this backup device and then re-create it to get Hm, creating a new file might help, I will try that.> the > right location back. > You might also be able to change the path in the MSDB database, but I Oh.. then maybe I should keep it as a last resort.> don't know if it's as simple as that. 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 Well, that helped. Apparently, when creating a backup in a new path, the >> the right location back. > > Hm, creating a new file might help, I will try that. file must not already exist. Adrian  |
|||||||||||||||||||||||
Other interesting topics