database:mssql:backup_to_od [Eureka Moment]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== 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

<code dos>
@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
</code>

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.