Abbreviate PathName ''C:\Program Files\Microsoft SQL Server\MSSQL\Backup''

Question

Abbreviate PathName ''C:\Program Files\Microsoft SQL Server\MSSQL\Backup''

Express12

SSCoach

Points: 18514
More actions
March 28, 2006 at 8:29 am

#112307

I recall seeing the PATH name to SQL Server abbreviated using the '~' symbol. Is there a way to abbreviate this PATHNAME? :
BACKUP DATABASE MyDB to disk='C:\Program Files\Microsoft SQL Server\MSSQL\Backup\MyDB.bak'

BT

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Bill

C:\Progra~1\Micros~1\MSSQL\Backup\MyDB.bak should do it. BUT if you already had a folder whose name began with "Progra" or "Micros" when either of those abbreviated ones were created, then you may be find they are abbreviated to something like Micros~2 or Progra~3. Why do you not want to use the full path name?

John

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 2

Hi,

would it not be easier to simply create a directory "c:\backups", and put the backup there?

Just because "C:\Program Files\Microsoft SQL Server\MSSQL\Backups" is the default you are not required to use it... 😉

regards karl

Best regards
karl

EdVassie SSC Guru Points: 60472 More actions · Answer 3

Be aware that using the short form name such as C:\Progra~1\Micros~1\ is taking advantage of wha are known as DOS 8.3 file names. The generation of 8.3 names can be turned off by a registry setting, and 8.3 names may not be available in a future version (or possibly service pack...) of Windows.

As has already been said, you should not assume that Program Files will always have the 8.3 name of Progra~1. It may be called Progra~2 if a folder caled (say) C:\Progras existed before C:\Program Files was created.

Also be aware that the SQL2000 install program requires 8.3 file names to be generated in order to run the install.

There is also a gotcha if you install multiple instances... When SQL Install configures the services for a new instance, it will use 8.3 filenames for the service paths. We have found situations where the Install used the wrong short-form name for a given instance. e.g. the 8.3 name of instance MSSQL$INSTA was MSSQL$~1, the name for MSSQL$INSTB was MSSQL$~2, and for MSSQL$INSTC was MSSQL$~3, but the Install configured the service for MSSQL$INSTC to run from the MSSQL$~2 folder. If the different instances were ever at different hotfix levels this could have been a problem.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

I assume that you can use new environmental variable or a UDF function to define a long path string depending on what you are doing This is an example with a local variable for SQL Server 2005 that I just tested:

declare

@mypath varchar(200)

select

@mypath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mybackup.bak'

backup

database myDB to disk = @mypath

Regards,Yelena Varsha