Batch Variable in sqlcmd

Question

Batch Variable in sqlcmd

ArjunaReddy

Default port

Points: 1467
More actions
September 23, 2009 at 4:45 am

#226474

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.

Viewing 3 posts - 1 through 2 (of 2 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

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

yazalpizar SSC Journeyman Points: 95 More actions · Answer 2

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?