For When You Can't Have The Real Thing
[ start | index | login ]
start > Windows > MS SQL > Dump All Databases

Dump All Databases

Created by dave. Last edited by dave, 9 years and 44 days ago. Viewed 2,435 times. #1
[edit] [rdf]

This is a lightly modified script I found somewhere. I'm recording it here because the location where I found it was exporting it with "smart quotes" which made it unrunable.

Because I wanted to run this locally on a bunch of computers with SQL Server on it, I modified the so that it did the dump of databases on the local running computer. I also modified it so that the date was created in the directory structure, not the file names.


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 %ComputerName% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

mkdir d:\Backup mkdir d:\Backup\%NowDate% 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 %ComputerName% -Q "BACKUP DATABASE [%%I] TO Disk='D:\Backup\%NowDate%\%%I.bak'" ECHO. )

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


no comments | post comment
This is a collection of techical information, much of it learned the hard way. Consider it a lab book or a /info directory. I doubt much of it will be of use to anyone else.

Useful: | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt