December 5, 2013 at 6:12 pm
If I would like to create a script to back up a database to the default backup directory, how can I spesify the default location of backup direction:
Backup database @databasename
TO DISK = @BackupDirectory
I don't want to hard coded the @backupdirectory, for it also have the subdirectory of each database.
I see someone coded like this
@BackupDirectory = '.\\'+@DatabaseNm+'\\'+@DatabaseNm+'_backup_'+
....
I don't really understand .\\ mean? does it mean the default backup directory?
Why is that?
Thanks
December 5, 2013 at 8:56 pm
Default backup lockation is available in registry of the server "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer" on "BackupDirectory".
i dont think this is accessible using the partila code provided by you.
can you post the complete code.
Note you have to specify the backup file name only during every backup, this create backup in that location.
Regards
Durai Nagarajan
December 6, 2013 at 12:51 am
You can set the default backup location in the registry using this command (replace the backup folder)
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'N:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup'
GO
The "." in ".\\" means "this" folder, the double backslash in the escape character \ followed by the actual \ - in total it means, "Default Backup path\"
December 6, 2013 at 3:21 am
If you only write the backup file name & DO NOT provide the backup directory path, then SQL Server automatically takes the backup in default backup location.
BACKUP DATABASE AdventureWorks TO DISK = 'AdventureWorks.BAK'
Above code will automatically take the backup at default backup directory. As simple as that.
December 6, 2013 at 10:52 am
Divine Flame (12/6/2013)
If you only write the backup file name & DO NOT provide the backup directory path, then SQL Server automatically takes the backup in default backup location.
BACKUP DATABASE AdventureWorks TO DISK = 'AdventureWorks.BAK'
Above code will automatically take the backup at default backup directory. As simple as that.
Thanks, I tried this, it is good to know it goes to default backup directory, but I found it didn't go to the subfolder of the databasename directory, which is defaultbackupdirectory\databasename\
December 6, 2013 at 10:57 am
The "." in ".\\" means "this" folder, the double backslash in the escape character \ followed by the actual \ - in total it means, "Default Backup path\"
Thanks, tried to understand this, but still not quite get it.
so . means this folder, what is this folder?
the whole script is put in the SQL agent job, type is Transact_SQL script,
but I still don't get it: why .\\ means default backup directory, can you please explain more? Thanks
it is defined as this in the script:
@BackupDirectory = '.\\'+@DatabaseNm+'\\'+
@DatabaseNm+'_backup_'+
CAST(DATEPART(YYYY,@BackupDate) AS VARCHAR(4))+'_'+
RIGHT('0'+CAST(DATEPART(MM,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(DD,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(HH,@BackupDate) AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(MI,@BackupDate) AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(SS,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(MCS,@BackupDate) AS VARCHAR(6)),6)+'.bak'
December 6, 2013 at 11:00 am
I would add that if a default backup directory was not deliberately set when SQL Server was installed, then the default backup location is on the C: drive. Since C: drives usually are not huge, this can get you in trouble if your database is very large, backup compression is not enabled and a backup job fills up the C: drive.
December 6, 2013 at 7:06 pm
sqlfriends (12/6/2013)
The "." in ".\\" means "this" folder, the double backslash in the escape character \ followed by the actual \ - in total it means, "Default Backup path\"
Thanks, tried to understand this, but still not quite get it.
so . means this folder, what is this folder?
the whole script is put in the SQL agent job, type is Transact_SQL script,
but I still don't get it: why .\\ means default backup directory, can you please explain more? Thanks
it is defined as this in the script:
@BackupDirectory = '.\\'+@DatabaseNm+'\\'+
@DatabaseNm+'_backup_'+
CAST(DATEPART(YYYY,@BackupDate) AS VARCHAR(4))+'_'+
RIGHT('0'+CAST(DATEPART(MM,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(DD,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(HH,@BackupDate) AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(MI,@BackupDate) AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(SS,@BackupDate) AS VARCHAR(2)),2)+'_'+
RIGHT('0'+CAST(DATEPART(MCS,@BackupDate) AS VARCHAR(6)),6)+'.bak'
The "." is a special relative path in most OS command line interpreters. The same as ".." for example if you open a CMD window from your desktop, try typing DIR and you will see the first 2 directories are always "." and "..". They represent "." the current folder and ".." the parent folder. You can do relative path statements like DIR ..\..\windows\ or DIR .\..\.\ or CD ..\..\windows
The double backslash \\ represents an escape character \ followed by the actual character that you want, \ in this case. If you were using a LIKE statment the escape character can be set to any character you want (see here http://msdn.microsoft.com/en-us/library/ms179859.aspx, but when you set a path variable, you need to just know that double backslash represents a single backslash. (actually that's for when you're programming, I think in this case the double slash doesn't matter and is just ignored, eg. if you open a CMD windows, try "CD C:\\windows", the same as "CD C:\windows")
In your backup example, the "." means "the current folder as specified by the preceding statement (BACKUP command)", ie the default backup folder, followed by a single backslash. If you omit the .\\, it looks like you are referring to a filename rather than a folder path.
December 10, 2013 at 9:57 am
In your backup example, the "." means "the current folder as specified by the preceding statement (BACKUP command)", ie the default backup folder, followed by a single backslash. If you omit the .\\, it looks like you are referring to a filename rather than a folder path.
Thank you very much, Andrew for the detailed explanation. I understood the . and \\ part in Windows that means the root directory and escape operator.
But the above statement really helped me, so Backup command, if used with .\\ it really means the default backup directory. That was I was confusing about. And I cannot find it in any of the document of the website of Microsoft. But it apparently is.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply