1

I am using this batch file to backup all the databases in my sql server except those shipped with sql server,

@ECHO OFF
SETLOCAL

REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=%SystemDrive%SQLDBList.txt
SqlCmd -E -S MY-PC\SQLEXPRESS -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%”

REM Backup each database, prepending the date to the filename
FOR /F “tokens=*” %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S MY-PC\SQLEXPRESS -Q “BACKUP DATABASE [%%I] TO Disk=’D:\SQLdata\Backup\%NowDate%_%%I.bak'”
ECHO.
)

REM Clean up the temp file
IF EXIST “%DBList%” DEL /F /Q “%DBList%”

ENDLOCAL

I have got the server name by using the query - SELECT @@SERVERNAME just in case and the path is exactly the same.

I have run it manually and through windows task scheduler with Administrator permission, but I am not getting any back ups in the specified folder. I don't know what is wrong I am doing here. I am quite new to this stuff, please if anybody know then guide me, thanks.

Learner
  • 776
  • 6
  • 14
  • 40
  • SQL Server Management Studio has **maintenance plans** which do this for you in a much simpler, much more easy-to-use fashion ..... – marc_s Jan 24 '15 at 07:54

2 Answers2

2

I'd use a text-editor like Editplus or, if you must, Notepad - not a WP to generate batch files.

Batch does not understand “..” - it needs " - same goes for the single-quotes.

Also you don't specify whay you mean by the specified folder. Your pathnames are relative so for instance you are selecting MY-PC\SQLEXPRESS relative to the current directory. You can set the current directory with a cd "c:\desired\directory\name" statement or you could use an absolute path (from the drive-root). But use the propoer sort of quotes.

Magoo
  • 77,302
  • 8
  • 62
  • 84
  • There are no problems with the paths, assuming `D:\SQLdata\Backup` exists. The only issue I see is the funny quotes too. Make sure you use straight quotes, not ones mucked around with by MS products. Seeing the script is straight from http://www.howtogeek.com/50299/batch-script-to-backup-all-your-sql-server-databases/ I can't see it having any issue, it's been tested and works. Try copying and pasting from the original page again straight into notepad. – Scott C Jan 24 '15 at 09:56
2

You could get the backup scripts from https://ola.hallengren.com/ to backup the databases on your SQL Express db. mark_s mentioned using SQL Management Studio, and I agree that this makes managing SQL Express easier for me.

If you choose to use the scripts from Ola Hallengren, you basically run the setup script that you download from the main page. Then view the 'DatabaseBackup' page (linked from the home page). About half way down the page is a list of examples that should clear up how to use the queries.

Bill
  • 314
  • 1
  • 10