CODEWITHSUNDEEP
sqlsql-server
claeys
claeys

Reputation: 53

How do i backup one specific database from my database server

Hi i want to backup one database of my database server using a bat and a sql file.

sqlcmd -S HEIST-BERG-SL\SQLEXPRESS -E -Q "C:\SALTO\Automatic Backups\scripts\backupDB.sql"

PAUSE

currently i'm missing something in this code to get my specific database called "Salto_Test" when i run the code above i backup all of my databases on the server and thats not what i want.

SQL

declare @datstr as varchar(100)=''
declare @currdate as datetime=getdate()
set @datstr=cast(DATEPART(YYYY,@currDate) as varchar(5))+right('00'+cast(DATEPART(MM,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(DD,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(HH,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(MINUTE,@currDate) as varchar(5)),2)
declare @path as varchar(500)='C:\SALTO\Automatic Backups\Salto_Test_db_' + @datstr +'.BAK'
backup database Salto_Test to disk= @path

Upvotes: 0

Views: 172

Answers (1)

Amit Yadav
Amit Yadav

Reputation: 481

If Database name is known then why go for the while loop and query string you can directly write query, Replace your SQL File code by below.

declare @datstr as varchar(100)=''
declare @currdate as datetime=getdate()
set @datstr=cast(DATEPART(YYYY,@currDate) as varchar(5))+right('00'+cast(DATEPART(MM,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(DD,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(HH,@currDate)as varchar(5)),2)+right('00'+cast(DATEPART(MINUTE,@currDate) as varchar(5)),2)
declare @path as varchar(500)='C:\SALTO\Automatic Backups\Salto_Test_db_' + @datstr +'.BAK'
backup database Salto_Test to disk= @path

Upvotes: 2

Related Questions