The tip is useful for 2005+ - is there an equivalent for SS2K, please?

 There's a similar screen in SQL 2000

In Enterprise Manager, right-click on the server name and choose Properties and then the Database Settings tab

Yep - known...

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K...  Poster is NOT a DBA, hence the seemingly "basic" nature of the question...

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?

Do you have a BackupDirectory key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer ? I don't have access to SQL Server 2000 at the moment and I'll have to install it to check the installed reg keys

[quote user="SAinCA"]

How does one CHANGE the default backup destination folder?  There doesn't appear to be an equivalent registry area for 2K.

[/quote]

Create ONE Backup device using Enterprise Manager, which will then use your chosen backup folder as the default (one of many useful behaviors that went away in 2005).

[quote user="SAinCA"]

AND, do I have to make the change to both nodes of the cluster, if indeed it is a registry change that is required...?

[/quote]

I've done this on a cluster, but can't verify fully that this is the case without actually failing one over to test. Not knowing where the default is stored: if in the registry, you may need to do it twice; but if it's stored in Master or Msdb, then seems like you're covered.

Is there a way to point to a remote server's shared folder(s)? This will make backup to remote servers and keep a copy standing by for better recoverability. Currently, I am backing up in the local server (SQL) and then copying to remote server.

Thanks

Thanks, Mike, it had to be "something different" for 2K.  Will give it a whirl on our test box...

To do what you want, I'd recommend using Red-Gate's SQL Backup as it runs exceptionally quickly, including compression (HUGE disk saving), and has a simple "copy to network drive" option you can employ.  The guys here tried local and network-copy using native commands but it became a pain - they were glad of SQL Backup's feature...  Hope you can justify the minimal expense for what the tool delivers.

For SQL Server 2000 it is basically the same thing. 

Here is an example of the key path for a SQL 2000 installation 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2000\MSSQLServer

The parameter is called "BackupDirectory"

This worked great for new databases that are created.  However, the default location for databases that existed before the registry change still defaults to the old location.  Is there a fix for that?

Sam are you saying that for newer databases the backups go to the new backup location, but for older databases the backups go to the older backup folder? Just want to make sure I understand the need.

Correct.  I'm able to change the location and remove the old location, but every time I right click-->all tasks-->back up, the default location would revert back to what it was before I made the change.

Hi Sam, I just tried this on SQL Server 2008 and the GUI showed the new location for me after I made the change.  I also had done a backup for this database prior to changing the location and it still showed the new location using the GUI.

I am not sure why it is not working consistently for you.

You could also try this to see if the backup for one of these databases goes to the new location and put in your database name and the name of the file for the backup file without specifying a backup location.

BACKUP DATABASE dbName TO DISK = 'fileName'

BACKUP DATABASE master TO DISK = 'master_test.bak'