Abbreviate PathName ''C:\Program Files\Microsoft SQL Server\MSSQL\Backup''
-
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 -
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
-
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 -
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
-
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply