The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks.
Transact-SQL statements:
sqlcmd -S <ComputerName>\<InstanceName>
or
sqlcmd -S .\<InstanceName>
Connecting to a named instance by using Windows Authentication and specifying input and output files:
sqlcmd -S <ComputerName>\<InstanceName> -i <MySqlScript.sql> -o <MyOutput.txt>
For example:
sqlcmd -S "ComputerName or InstanceName" -i "C:\AutoBackup\AutoBackup_sql.sql" -o C:\AutoBackup\AutoBackup_sqllog.txt
Syntax:
- -S [protocol:]server[instance_name][,port]
- -i input_file
- -o output_file
AutoBackup_sql.sql backup by date:
declare @currentDate datetime
set @currentDate = GetDate()
declare @fileName_dbname varchar(255)
-----DBNAME-----
set @fileName_dbname = 'D:\MSSQL_Backup\DBNAME_'
+ cast(Year(@currentDate) as varchar(4))
+ Replicate('0', 2 - Len(cast(Month(@currentDate) as varchar(2))))
+ cast(Month(@currentDate) as varchar(2))
+ Replicate('0', 2 - Len(cast(Day(@currentDate) as varchar(2))))
+ cast(Day(@currentDate) as varchar(2))
+ '_' +
+ Replicate('0', 2 - Len(cast(DatePart(hour, @currentDate) as varchar(2))))
+ cast(DatePart(hour, @currentDate) as varchar(2))
+ Replicate('0', 2 - Len(cast(DatePart(minute, @currentDate) as varchar(2))))
+ cast(DatePart(minute, @currentDate) as varchar(2)) + '.bak'
backup database DBNAME to disk = @fileName_dbname with NOFORMAT, INIT,
name = N'DBNAME-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
And save to type .bat file and test run.
