Post reply

Batch Variable in sqlcmd

  • 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.

  • September 23, 2009 at 6:29 am

    #1057438

    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

  • April 28, 2010 at 7:19 am

    #1158890

    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?

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy