Skip to main content

Backup Databases in a loop...


Note: This applies to Microsoft SQL Server 2005/2008...

    Ever get sick of having a different SQL Job for each database backup? I created this method for backing up all databases (filtered) in a cursor. Just create a job with this as the Step and give the Job a schedule...


  01:  DECLARE @dbName varchar(50)

  02:  DECLARE @BKPDate varchar(10)

  03:  DECLARE @sql nvarchar(1000)

  04:  SET @BKPDate = replace(convert(varchar(10), getdate(), 102), '.', '')

  05:  DECLARE bkpCursor CURSOR FOR

  06:      

  07:      /* only backup non-system databases */

  08:      select [Name] from sys.databases where [owner_sid] <> 0x01

  09:          

  10:  OPEN bkpCursor

  11:  FETCH NEXT FROM bkpCursor INTO  @dbName

  12:  IF @@FETCH_STATUS = 0

  13:  BEGIN

  14:      WHILE (@@FETCH_STATUS = 0)

  15:      BEGIN

  16:      set @sql = 

  17:          ' BACKUP DATABASE '+ @dbName +

  18:          ' TO DISK = N''C:\SQLBackup\'+ @dbName +'_'+ @BKPDate +'.bak''' +

  19:          ' WITH NOFORMAT, NOINIT,'+

  20:          ' NAME = N'''+ @dbName +'-Full Database Backup'',' +

  21:          ' SKIP, STATS = 10;'

  22:      exec sp_executesql @statement = @sql

  23:      FETCH NEXT FROM bkpCursor INTO @dbName

  24:      END

  25:  END

  26:  ELSE

  27:      PRINT 'No Databases to Backup.'

  28:  CLOSE bkpCursor

  29:  DEALLOCATE bkpCursor



MS SQL Server Express Edition

But what if you're running MS SQL Server Express? That doesn't come with SQL Server Agent Service, which is required for creating Jobs. Use the osql command-line utility.

Here is a batch file I wrote which uses osql to call a sql script file (after saving the above syntax into a sql file called "backup_Databases.sql").  Then I zip up the backup files and delete them.


  01:  @ECHO OFF

  02:  osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL

  03:   

  04:  C:\util\zip -9 -j -q C:\backup\archive_db_%1.zip C:\SQLBackup\*%1.bak

  05:  IF EXIST C:\backup\archive_db_%1.zip (

  06:     del C:\SQLBackup\*%1.bak

  07:  )

This batch file assumes you are passing in a Date string.

So to call it I would type:
call batch file
[ Note: I'm using Info-Zip to compress the backup files. ]

Stand-alone Batch File
I know what you're thinking...  Why not make the batch file smart enough to get the date instead of having to pass it in?

Here is a revised batch file which does just that:

  01:  @ECHO OFF

  02:  osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL

  03:   

  04:  for /f "tokens=2-4 delims=/ " %%a in ('date/t') do (set datevar=%%c%%a%%b)

  05:   

  06:  C:\util\zip -9 -j -q C:\backup\archive_db_%datevar%.zip C:\SQLBackup\*%datevar%.bak

  07:  IF EXIST C:\backup\archive_db_%datevar%.zip (

  08:     del C:\SQLBackup\*%datevar%.bak

  09:  )




What's left?

All that's left is to create a scheduled task using Windows Task Scheduler, which will call the batch file (daily, weekly, etc.).


Labels:

Comments

Post a Comment

Popular posts from this blog

Tyler Eating

Here is a picture of Tyler eating Peas. They are all over his face and he loves it! If you look closely, you can see his first 2 teeth (bottom).
Post a Comment
Read more

SQL Generation

Recently I wrote a console application that generates SQL code to populate a database with sample data. I wanted it to be as generic as possible, but it still relies on a Source database to get sample data from. The application is configurable in that it lets you specify which Database is the source database, as well which tables to get the data out of. I did not want this application to depend on any stored procedures; it needs to be self contained, which is why I'm hard-coding the table definition sql query. So I call the console app via a batch file. The batch file calls the exe file, outputting the text to a file: 01: @ECHO OFF 02: bin\debug\SqlGenerator.exe > "C:\run.sql" 03: ECHO done. Here is the main method in the console app: 01: class Program { 02: static void Main( string [] args) { 03: Console.WriteLine(Database.GenerateSQL()); 04: } 05: } Here is the relevant excerpt from the App.config file: 01: < a
Post a Comment
Read more

General practices (tomAYto tomAHto)...

I hate repeating code and sometimes generics can make things a bit easier. One of the things that needs to be done a lot with Drop-down lists is adding a "Please Select" as the first option, but I don't particularly like doing that in the data layer (stored procedure, etc.). I like to add that to the list, but why repeat the same code for every Drop-down list if there are several on the page? Here is one way I like to take care of that: Here are the supporting Classes: 01: #region Supporting Classes 02: public interface IReportObject { 03: int ID { get; set; } 04: string Name { get; set; } 05: } 06: public class CSP : IReportObject { 07: public int ID { get; set; } 08: public string Name { get; set; } 09: } 10: public class Base : IReportObject { 11: public int ID { get; set; } 12: public string Name { get; set; } 13: } 14: public class Property : IReportObject { 15: public int
Post a Comment
Read more