Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Backups » Batch Variable in sqlcmd
Add to briefcase

Batch Variable in sqlcmd Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ArjunaReddy
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, May 07, 2013 10:08 PM
Points: 39, Visits: 388
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
matt6288
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: Monday, January 28, 2013 2:05 PM
Points: 1,130, Visits: 1,181
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
yazalpizar
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.