Backup database to disk in SQL Server unknown location of writable folder

Question

Hi,

I use command "Backup Database " to disk to copy databases programmatically. However this command requires to specify exactly where to put the backup. I don't know in advance on which machine the script will be run so I don't know where specific MSSQL server has permission to write. Perfect place would, of course, be MSSQL data folder (for example on my machine: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA).

It would be perfect if I could somehow query SQL Server DATA folder path from the script. Is there any way?

Answer 1

hm well this question is stupid, answer is simple. If you specify name backup "TO" then its automatically back uped into backup directory...

Answer 2

One option would be to get the path from sys.database_files of master...