Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup script Expand / Collapse
Author
Message
Posted Thursday, December 05, 2013 6:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
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
Post #1520368
Posted Thursday, December 05, 2013 8:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1520384
Posted Friday, December 06, 2013 12:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:40 PM
Points: 1,077, Visits: 1,496
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\"
Post #1520436
Posted Friday, December 06, 2013 3:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 1,261, Visits: 2,292
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.


- Divine Flame
Post #1520472
Posted Friday, December 06, 2013 10:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
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\
Post #1520677
Posted Friday, December 06, 2013 10:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063

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'
Post #1520681
Posted Friday, December 06, 2013 11:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 504, Visits: 1,465
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.
Post #1520682
Posted Friday, December 06, 2013 7:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:40 PM
Points: 1,077, Visits: 1,496
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.
Post #1520783
Posted Tuesday, December 10, 2013 9:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063

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.
Post #1521587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse