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

Batch Variable in sqlcmd Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 4:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:27 AM
Points: 45, Visits: 532
Hi ,
I am writing a batch script which will take Full backup of the specified databases on our server. For this i am first creating a folder with the current date using OS command (mkdir %date:~-4%%date:~4,2%%date:~7,2%) with the intension that all the backups should go to this folder.

If i want to mention the current date folder (%date:~-4%%date:~4,2%%date:~7,2%) in the following sqlcmd how should i do?

sqlcmd -E -S MB3DS1022 -Q"BACKUP DATABASE [SFB10D02] TO DISK = N'D:\backups\\SFB10D02.bak' WITH NOFORMAT, NOINIT,NAME = N'SFB10D02-Full Database Backup',SKIP,NOREWIND, NOUNLOAD,STATS = 10"

Could anyone help me how can i achieve this?

Thanks,
Nagarjun.
Post #792515
Posted Wednesday, September 23, 2009 6:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:51 AM
Points: 1,207, Visits: 1,267
bommaka.nagarjun (9/23/2009)
Hi ,
I am writing a batch script which will take Full backup of the specified databases on our server. For this i am first creating a folder with the current date using OS command (mkdir %date:~-4%%date:~4,2%%date:~7,2%) with the intension that all the backups should go to this folder.

If i want to mention the current date folder (%date:~-4%%date:~4,2%%date:~7,2%) in the following sqlcmd how should i do?

sqlcmd -E -S MB3DS1022 -Q"BACKUP DATABASE [SFB10D02] TO DISK = N'D:\backups\\SFB10D02.bak' WITH NOFORMAT, NOINIT,NAME = N'SFB10D02-Full Database Backup',SKIP,NOREWIND, NOUNLOAD,STATS = 10"

Could anyone help me how can i achieve this?

Thanks,
Nagarjun.




If you are doing all of this in the batch file then you can set a variable like this.

set folder=%date:~-4%%date:~4,2%%date:~7,2%


You can then reference this variable in sqlcmd line like this

sqlcmd -E -S MB3DS1022 -Q"BACKUP DATABASE [SFB10D02] TO DISK = N'D:\backups\%folder%\SFB10D02.bak' WITH NOFORMAT, NOINIT,NAME = N'SFB10D02-Full Database Backup',SKIP,NOREWIND, NOUNLOAD,STATS = 10"


I think that should work but you might want to echo the line above just to make sure
Post #792562
Posted Wednesday, April 28, 2010 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 28, 2010 7:17 AM
Points: 1, Visits: 5
I was looking for something like this, but instead of the folder, I want to create a backup of the database each time with different name, could be DDMMYYYYHHMM.bak Can I use the same solution?
Post #911920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse