SQL Backup Scripts

 

There are a number of ways of running these scripts (in order of easiest to hardest):

 

1. Maintenance Tasks (uses SQL Agent) - easy GUI for backing up databases, cleaning up backups etc. ** NOT AVAILABLE IN SQL EXPRESS **

2. SQL Agent Jobs (uses SQL Agent) (if you don't have Maintenance Tasks) allow you to run a script on a shedule

3. Windows Task Scheduler - save the sql script in a txt file then run a command like one or both of these:

sqlcmd -SHORIZON -E -i "c:\logs and scripts\BackupAllSqlDatabases.txt"

sqlcmd -SHORIZON -E -i "c:\logs and scripts\CleanUpOldSqlBackups.txt"

(where BackupAllSqlDatabases.txt contains your SQL script)

 

 

BackupAllSqlDatabases.txt

--Backup All Databases 

DECLARE @basePath varchar(300);

SET @basePath = N'C:\websites\LocalUser\BackupSQL\'; 

DECLARE @db sysname;

DECLARE user_db_cursor CURSOR FOR 

--SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')

OPEN user_db_cursor

FETCH NEXT FROM user_db_cursor 

INTO @db 

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @backupPath varchar(1000);

--SET @backupPath = @basePath + @db + '\';

SET @backupPath = @basePath; -- no need for extra folders 

DECLARE @backupName varchar(1000);

DECLARE @time datetime2;

SET @time = SYSDATETIME();

SET @backupName = @db + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_')+ '_' +  CAST(DATEPART(NANOSECOND, @time)/100 as varchar) 

      --Create sub-directory

--EXECUTE master.dbo.xp_create_subdir @backupPath 

DECLARE @backupFileName varchar(1000);

SET @backupFileName = @backupPath + @backupName + '.bak'; 

--Backup database

BACKUP DATABASE @db TO DISK=@backupFileName

WITH 

--RETAINDAYS=14, 

NOFORMAT, NOINIT, NAME=@backupName, 

SKIP, NOREWIND, NOUNLOAD, STATS=10 

FETCH NEXT FROM user_db_cursor 

INTO @db 

END 

CLOSE user_db_cursor

DEALLOCATE user_db_cursor

 

CleanUpOldSqlBackups.txt

-- Clean up Old Backups 

DECLARE @basePath varchar(300);

SET @basePath = N'C:\websites\LocalUser\BackupSQL\'; 

DECLARE @time datetime;

SET @time = DATEADD(DAY,-14, GETDATE() ); 

EXECUTE master.dbo.xp_delete_file 0,@basePath,N'bak',@time,0