Windows批处理文件备份SQL数据库 [英] Windows batch file to backup sql database

问题描述

我正在使用该批处理文件备份sql服务器中的所有数据库，但sql服务器附带的数据库除外，

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

我已经通过使用查询获得服务器名称-SELECT @@SERVERNAME，以防万一且路径完全相同.

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

我已经通过管理员授权通过Windows任务计划程序手动运行它，但是在指定的文件夹中没有任何备份.我不知道我在这里做什么.我对这些东西还很陌生，如果有人知道，请指导我，谢谢.

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.

推荐答案

我会使用 Editplus ，或者(如果需要的话)记事本-不是WP来生成批处理文件.

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.

此外，您也没有指定the specified folder表示的含义.您的路径名是relative，因此例如您要相对于当前目录选择MY-PC\SQLEXPRESS.您可以使用cd "c:\desired\directory\name"语句设置当前目录，也可以使用绝对路径(来自驱动器根目录).但是请使用适当的引号.

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.

这篇关于Windows批处理文件备份SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！