0

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
claeys
  • 53
  • 3
  • 11
  • So... what's in the script? – Jacob H Apr 19 '19 at 11:57
  • added code @JacobH – claeys Apr 19 '19 at 12:03
  • 1
    At the bottom of your first script you have a `WHILE` loop that dynamically loops through all of your databases in `sys.databases`. You need to remove the loop and replace it with just a query to backup a single database. Like `BACKUP DATABASE [YourDbNameHere] TO DISK = ''C:\SALTO\Automatic Backups\YourDbNameHere'' + @dateString +'.BAK'' WITH INIT` – Jacob H Apr 19 '19 at 12:14
  • I'd suggest using [Ola Hallengren's award winning backup scripts](https://ola.hallengren.com/sql-server-backup.html) instead of writing your own. Backing up a database is a simple command. Those scripts though allow you to backup specific types of databases (user,system or all) , specify databases based on a pattern, folder locations, automate cleanup, compression, checksums etc. You can even specify your own directory structure through the `DirectoryStructure` parameter and filename format through the `FileName` parameter – Panagiotis Kanavos Apr 19 '19 at 12:43
  • @JacobH your line seems to be missing a ' i just don't know where – claeys Apr 19 '19 at 12:56

1 Answers1

2

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
Amit Yadav
  • 481
  • 3
  • 11
  • i'm getting an error incorrect syntax near + can't seem to find the exact error – claeys Apr 19 '19 at 12:59
  • Can you look at the deletion of old files aswell please? How can i test that this works since when i use 0 days instead of the 32 days threshold nothing happens – claeys Apr 19 '19 at 13:08
  • https://stackoverflow.com/questions/43269779/how-to-delete-files-on-the-directory-via-ms-sql-server this link may help you – Amit Yadav Apr 19 '19 at 13:24
  • For some reason i'm getting an error incorrect syntax near \ line 1 when i try out your code – claeys Apr 19 '19 at 13:39
  • can u check by removing END in the last line – Amit Yadav Apr 19 '19 at 13:43
  • and don't forget to verify your database name is 'Salto_Test' – Amit Yadav Apr 19 '19 at 13:45
  • My bad the end was still there from the last code i'm not using that and the database name is indeed Salto_Test – claeys Apr 19 '19 at 13:46
  • SQL File is fine there may be error in your bat file code i am sharing u link for that pls go through this, that may be helpful. https://www.codeproject.com/Tips/444317/SQL-database-backups-by-batch-file-using-SQL-scrip – Amit Yadav Apr 19 '19 at 14:39