database:mssql:backup_to_od

Backup MSSQL to OneDrive

'dat.exe' is a utility that outputs the current date in a form of Year,Month,Day,Hour,Minute,Second,DayOfWeek - all with leading zeros. Contact me if you require it. Otherwise change the script to work with system date and time tools

@ECHO OFF
SETLOCAL
 
for /F "tokens=1-7 delims==," %%a in ('dat.exe') do (set DATE=%%a-%%b-%%c& set TIME=%%d:%%e:%%f)
echo %DATE%
echo %TIME%
 
SqlCmd -E -S 127.0.0.1 -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > databases.lst
FOR /F "tokens=*" %%I IN (databases.lst) DO (
	ECHO I1: %%I
	SqlCmd -E -S 127.0.0.1 -Q "BACKUP DATABASE [%%I] TO Disk='t:\BackupScript\Backup\I1\Backup_%DATE%_%%I.bak'"
	ECHO.
)
IF EXIST "databases.lst" DEL /F /Q "databases.lst"
 
attrib +U -P t:\BackupScript\Backup /s
 
ENDLOCAL

t:\BackupScript\Backup must be soft linked to your OneDrive folder, and OneDrive should be set to save up disk space.

Note - the OneDrive part works only if user is logged on.

Enter your comment:
U S P M J
 
  • database/mssql/backup_to_od.txt
  • Last modified: 2020-12-09 13:02
  • (external edit)