How to get SQL server''s default backup directory programmaticaly?

Question

How to get SQL server''s default backup directory programmaticaly?

Gil Slutsky

SSC Veteran

Points: 286
More actions
October 17, 2005 at 4:19 pm

#111100

I am writing a program (VB-6), which supposed to issue backup commands to a variety of SQL servers. I know that it is possible to get the default backup directory from the registry of the SQL server, but my program will run somewhere else on the network and would not have access to the SQL Server's registry.
Is there a way to get the default backup directory by executing SQL or any system stored procedure through the ADO?
Thank you.
P.S. Interesting fact is that if you try to backup and restore working only with the physical disk path and not logical names, everything works like a charm, i.e.:
BACKUP DATABASE pubs TO DISK = 'pubs.bak' and RESTORE DATABASE pubs FROM DISK = 'pubs.bak' . The backup file gets placed into the default backup directory, which is in my case 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs.bak'. The only problem is that SQL server doesn't know anything about this backup. You can't list it using sp_helpdevice and you can't delete the file using the EXEC sp_dropdevice 'pubs', 'delfile'.

On the other hand, if you use logical names and do the following: EXEC master.dbo.sp_addumpdevice 'disk', 'pubs_logical', 'pubs.bak';
BACKUP DATABASE pubs TO pubs_logical it places the file in my case into the system32 folder and not into the default backup directory. It looks like you have to provide the full physical backup path into the sp_addumpdevice and I just don't know how to get it.
Kindest Regards,
gils13

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47795 More actions · Answer 1

First, with my DBA hat well and truly screwed on tight. I wouldn't be running an external application to issue backup commands to my SQL Servers across a network link. If the link is inactive for any reason then your backup doesn't happen. The safe approach is to run the backup on the server itself therefore ensuring you have a successful point of recovery. Once the backup has been created it can be encrypted/compressed/moved as applicable.

Second, with my DBA hat not so tight, it has always been my preference to use physical backup files instead of backup devices. It's one less step thats required in the backup/restore process. In the event of a disaster recovery situation I can restore the database from the backup file with one command.

--------------------
Colt 45 - the original point and click interface

Glenn Wallis SSC Eights! Points: 811 More actions · Answer 2

I agree with Phil regarding the idea of using the server to handle the backup process.

However, for adhoc backups via an application (ie backup the DB during the day before a data load or schema change) you can use the following command to find the default backup directory:

master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

Glenn

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 3

Phil, I'd prefer using "backup device" instead of physical file, because you can always change your backup device to point to another file once the disk is full for the old physical file. This way, you never modify your original code.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Hmmm ... I've never had to change my code yet

My routine creates a unique filename that includes database name, backup type and datetime. Database is then backed up to the local disk using this filename. Then the backup file is moved to a central storage location for easy offline backup to tape for offsite storage.

We're also using this same process to run log shipping for two database on different servers. The backup routine just passes the filename created above to a stored procedure on the stand-by server. All totally hands off with no code changes required even though a totally new filename is created with every backup.

--------------------
Colt 45 - the original point and click interface

Gil Slutsky SSC Veteran Points: 286 More actions · Answer 5

Thank you all for your input. The idea of using xp_instance_regread helped me a lot. It solved all my problems.

I see that majority of your suggestions to handle the backup process on the server itself is coming from the DBA point of view, which is agreeable, but in our case we are trying to deal with a number of small sized user specific databases. We would like to give a user a programmatic way to backup the DB, then perform changes, and if the user will have screwed it up to the point of no return, to allow him/her to get out of the misery by quickly restoring the previous version. This way we can keep the records of the user initiated backups and restores in a different "administrative" database and offer users some sort of version control for their own databases.

Kindest Regards,

gils13

philcart SSC-Forever Points: 47795 More actions · Answer 6

"This way we can keep the records of the user initiated backups and restores in a different "administrative" database and offer users some sort of version control for their own databases."

You can still have this functionality while handling the backup on the same machine as SQL Server. Just get your VB app to write a record to the "administrative" database. I think this would provide less risk and more recoverability than trying to manage things across the network link.

--------------------
Colt 45 - the original point and click interface