Dump All Databases
(2015-04-09)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.
@ECHO OFF
SETLOCALREM 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-%%CREM 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%"ENDLOCAL
no comments |
post comment
Virtual Dave Megaplex:
Home Page- This wiki's start page
- Send Feedback To Dave
Logged in Users: (0)
Recently Changed
Check Internet Service Database Match- Device-Local Certificate Expired
- CLI Policy Lookup
- DH Selection For IPsec VPNs
- Scan For Newly Added Disk
- nmap
- Seconds Since Epoch to Local Time
- Link Monitor
- DH group to OAKLEY_GROUP table
- Fire Eater
- start
- FortiClient Error Codes
- dhparams Generation
- SSL Certificate Bundles
- ufw
- Grow A LVM Partition
- Creating Users
- whoami
- list databases
- 2023
- 2022
- 2019
- 2018
- 2017
- 2011
- 2010
- 2008
- 2001
- 2000
- 1999
Editing: snipsnap-help, Image Macro(Et auditum est, et idcirco ego nunc simulare)
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.