How can I schedule a daily backup with SQL Server Express

Question

I m running a small web application with SQL server express  2005  as backend  I can create a backup with a SQL script  however  I d like to schedule this on a  daily basis  As extra option  should-have  I d like to keep only the last X backups  for space-saving reasons obviously  Any pointers    edit  SQL server agent is unavailable in SQL server express

User · Answer

You cannot use the SQL Server agent in SQL Server Express. The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

User · Answer

Just use this script to dynamically backup all databases on the server  Then create batch file according to the article  It is usefull to create two batch files  one for full backup a and one for diff backup  Then Create two tasks in Task Scheduler  one for full and one for diff   --    Copyright    Microsoft Corporation   All Rights Reserved  --    This code released under the terms of the --    Microsoft Public License  MS-PL  http   opensource org licenses ms-pl html   USE  master   GO          Object   StoredProcedure  dbo   sp BackupDatabases           SET ANSI NULLS ON  GO  SET QUOTED IDENTIFIER ON  GO   --                                                -- Author  Microsoft  -- Create date  2010-02-06 -- Description  Backup Databases for SQLExpress -- Parameter1  databaseName  -- Parameter2  backupType F full  D differential  L log -- Parameter3  backup file location --                                                CREATE PROCEDURE  dbo   sp BackupDatabases                 databaseName sysname   null               backupType CHAR 1                backupLocation nvarchar 200   AS          SET NOCOUNT ON                DECLARE  DBs TABLE                                 ID int IDENTITY PRIMARY KEY                    DBNAME nvarchar 500                              -- Pick out only databases which are online in case ALL databases are chosen to be backed up              -- If specific database is chosen to be backed up only pick that out from  DBs             INSERT INTO  DBs  DBNAME              SELECT Name FROM master sys databases             where state 0             AND name  DatabaseName             OR  DatabaseName IS NULL             ORDER BY Name              -- Filter out databases which do not need to backed up             IF  backupType  F                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   AdventureWorks                     END             ELSE IF  backupType  D                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   master   AdventureWorks                     END             ELSE IF  backupType  L                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   master   AdventureWorks                     END             ELSE                   BEGIN                   RETURN                   END              -- Declare variables             DECLARE  BackupName varchar 100              DECLARE  BackupFile varchar 100              DECLARE  DBNAME varchar 300              DECLARE  sqlCommand NVARCHAR 1000           DECLARE  dateTime NVARCHAR 20              DECLARE  Loop int                                -- Loop through the databases one by one             SELECT  Loop   min ID  FROM  DBs        WHILE  Loop IS NOT NULL       BEGIN  -- Database Names have to be in  dbname  format since some have - or   in their name       SET  DBNAME        SELECT DBNAME FROM  DBs WHERE ID    Loop       -- Set the current date and time n yyyyhhmmss format       SET  dateTime   REPLACE CONVERT VARCHAR  GETDATE   101                   REPLACE CONVERT VARCHAR  GETDATE   108             -- Create backup filename in path filename extension format for full diff and log backups       IF  backupType    F              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     FULL     dateTime    BAK        ELSE IF  backupType    D              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     DIFF     dateTime    BAK        ELSE IF  backupType    L              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     LOG     dateTime    TRN   -- Provide the backup a name for storing in the media       IF  backupType    F              SET  BackupName   REPLACE REPLACE  DBNAME                    full backup for     dateTime       IF  backupType    D              SET  BackupName   REPLACE REPLACE  DBNAME                    differential backup for     dateTime       IF  backupType    L              SET  BackupName   REPLACE REPLACE  DBNAME                    log backup for     dateTime  -- Generate the dynamic SQL command to be executed         IF  backupType    F                     BEGIN                SET  sqlCommand    BACKUP DATABASE     DBNAME     TO DISK        BackupFile      WITH INIT  NAME        BackupName      NOSKIP  NOFORMAT                    END        IF  backupType    D                    BEGIN                SET  sqlCommand    BACKUP DATABASE     DBNAME     TO DISK        BackupFile      WITH DIFFERENTIAL  INIT  NAME        BackupName      NOSKIP  NOFORMAT                            END        IF  backupType    L                     BEGIN                SET  sqlCommand    BACKUP LOG     DBNAME     TO DISK        BackupFile      WITH INIT  NAME        BackupName      NOSKIP  NOFORMAT                            END  -- Execute the generated SQL command        EXEC  sqlCommand   -- Goto the next database SELECT  Loop   min ID  FROM  DBs where ID gt  Loop  END   And batch file can look like this   sqlcmd -S localhost myDB -Q  EXEC sp BackupDatabases  backupLocation  c  Dropbox backup DB     backupType  F     gt  gt  c  Dropbox backup DB full log 2 gt  amp 1   and  sqlcmd -S localhost myDB -Q  EXEC sp BackupDatabases  backupLocation  c  Dropbox backup DB     backupType  D     gt  gt  c  Dropbox backup DB diff log 2 gt  amp 1   The advantage of this method is that you don t need to change anything if you add new database or delete a database  you don t even need to list the databases in the script  Answer from JohnB is better simpler for server with one database  this approach is more suitable for multi database servers

User · Answer

Eduardo Molteni had a great answer   SQL Server Automated Backups   Using Windows Scheduled Tasks  In the batch file   quot C  Program Files Microsoft SQL Server 100 Tools Binn SQLCMD EXE quot  -S   local  SQLExpress -i D  dbbackups SQLExpressBackups sql   In SQLExpressBackups sql  BACKUP DATABASE MyDataBase1 TO  DISK   N D  DBbackups MyDataBase1 bak   WITH NOFORMAT  INIT   NAME   N MyDataBase1 Backup   SKIP  NOREWIND  NOUNLOAD   STATS   10  BACKUP DATABASE MyDataBase2 TO  DISK   N D  DBbackups MyDataBase2 bak   WITH NOFORMAT  INIT   NAME   N MyDataBase2 Backup   SKIP  NOREWIND  NOUNLOAD   STATS   10  GO

User · Answer

The folks at MSSQLTips have some very helpful articles  the one most relevant for this is  Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files   The basic approach is to set up two tasks using the Windows Task Scheduler  One task runs a TSQL script that generates separate backup files for all MSSQL databases  except TEMPDB  with the database name and a date time stamp in the file name into a specified directory  The second task runs a VBScript script that goes through that directory and deletes all files with a  BAK extension that are more than 3 days old   Both scripts require minor editing for your environment  paths  how long to keep those database dumps  but are very close to drop-in-and-run    Note that there are possible security implications if you re sloppy with these or with directory permissions  since they are plain text files that will need to run with some level of privilege  Don t be sloppy

User · Answer

You can create a backup device in server object  let us say      BDTEST   and then create a batch file contain following command  sqlcmd -S 192 168 1 25 -E -Q  BACKUP DATABASE dbtest TO BDTEST    let us say with name      backup bat   then you can call      backup bat   in task scheduler according to your convenience

User · Answer

We have used the combination of    Cobian Backup for scheduling maintenance ExpressMaint for backup   Both of these are free  The process is to script ExpressMaint to take a backup as a Cobian  before Backup  event  I usually let this overwrite the previous backup file  Cobian then takes a zip 7zip out of this and archives these to the backup folder  In Cobian you can specify the number of full copies to keep  make multiple backup cycles etc   ExpressMaint command syntax example   expressmaint -S HOST SQLEXPRESS -D ALL USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

User · Answer

We have used the combination of    Cobian Backup for scheduling maintenance ExpressMaint for backup   Both of these are free  The process is to script ExpressMaint to take a backup as a Cobian  before Backup  event  I usually let this overwrite the previous backup file  Cobian then takes a zip 7zip out of this and archives these to the backup folder  In Cobian you can specify the number of full copies to keep  make multiple backup cycles etc   ExpressMaint command syntax example   expressmaint -S HOST SQLEXPRESS -D ALL USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

User · Answer

Just use this script to dynamically backup all databases on the server  Then create batch file according to the article  It is usefull to create two batch files  one for full backup a and one for diff backup  Then Create two tasks in Task Scheduler  one for full and one for diff   --    Copyright    Microsoft Corporation   All Rights Reserved  --    This code released under the terms of the --    Microsoft Public License  MS-PL  http   opensource org licenses ms-pl html   USE  master   GO          Object   StoredProcedure  dbo   sp BackupDatabases           SET ANSI NULLS ON  GO  SET QUOTED IDENTIFIER ON  GO   --                                                -- Author  Microsoft  -- Create date  2010-02-06 -- Description  Backup Databases for SQLExpress -- Parameter1  databaseName  -- Parameter2  backupType F full  D differential  L log -- Parameter3  backup file location --                                                CREATE PROCEDURE  dbo   sp BackupDatabases                 databaseName sysname   null               backupType CHAR 1                backupLocation nvarchar 200   AS          SET NOCOUNT ON                DECLARE  DBs TABLE                                 ID int IDENTITY PRIMARY KEY                    DBNAME nvarchar 500                              -- Pick out only databases which are online in case ALL databases are chosen to be backed up              -- If specific database is chosen to be backed up only pick that out from  DBs             INSERT INTO  DBs  DBNAME              SELECT Name FROM master sys databases             where state 0             AND name  DatabaseName             OR  DatabaseName IS NULL             ORDER BY Name              -- Filter out databases which do not need to backed up             IF  backupType  F                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   AdventureWorks                     END             ELSE IF  backupType  D                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   master   AdventureWorks                     END             ELSE IF  backupType  L                    BEGIN                   DELETE  DBs where DBNAME IN   tempdb   Northwind   pubs   master   AdventureWorks                     END             ELSE                   BEGIN                   RETURN                   END              -- Declare variables             DECLARE  BackupName varchar 100              DECLARE  BackupFile varchar 100              DECLARE  DBNAME varchar 300              DECLARE  sqlCommand NVARCHAR 1000           DECLARE  dateTime NVARCHAR 20              DECLARE  Loop int                                -- Loop through the databases one by one             SELECT  Loop   min ID  FROM  DBs        WHILE  Loop IS NOT NULL       BEGIN  -- Database Names have to be in  dbname  format since some have - or   in their name       SET  DBNAME        SELECT DBNAME FROM  DBs WHERE ID    Loop       -- Set the current date and time n yyyyhhmmss format       SET  dateTime   REPLACE CONVERT VARCHAR  GETDATE   101                   REPLACE CONVERT VARCHAR  GETDATE   108             -- Create backup filename in path filename extension format for full diff and log backups       IF  backupType    F              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     FULL     dateTime    BAK        ELSE IF  backupType    D              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     DIFF     dateTime    BAK        ELSE IF  backupType    L              SET  BackupFile    backupLocation REPLACE REPLACE  DBNAME                     LOG     dateTime    TRN   -- Provide the backup a name for storing in the media       IF  backupType    F              SET  BackupName   REPLACE REPLACE  DBNAME                    full backup for     dateTime       IF  backupType    D              SET  BackupName   REPLACE REPLACE  DBNAME                    differential backup for     dateTime       IF  backupType    L              SET  BackupName   REPLACE REPLACE  DBNAME                    log backup for     dateTime  -- Generate the dynamic SQL command to be executed         IF  backupType    F                     BEGIN                SET  sqlCommand    BACKUP DATABASE     DBNAME     TO DISK        BackupFile      WITH INIT  NAME        BackupName      NOSKIP  NOFORMAT                    END        IF  backupType    D                    BEGIN                SET  sqlCommand    BACKUP DATABASE     DBNAME     TO DISK        BackupFile      WITH DIFFERENTIAL  INIT  NAME        BackupName      NOSKIP  NOFORMAT                            END        IF  backupType    L                     BEGIN                SET  sqlCommand    BACKUP LOG     DBNAME     TO DISK        BackupFile      WITH INIT  NAME        BackupName      NOSKIP  NOFORMAT                            END  -- Execute the generated SQL command        EXEC  sqlCommand   -- Goto the next database SELECT  Loop   min ID  FROM  DBs where ID gt  Loop  END   And batch file can look like this   sqlcmd -S localhost myDB -Q  EXEC sp BackupDatabases  backupLocation  c  Dropbox backup DB     backupType  F     gt  gt  c  Dropbox backup DB full log 2 gt  amp 1   and  sqlcmd -S localhost myDB -Q  EXEC sp BackupDatabases  backupLocation  c  Dropbox backup DB     backupType  D     gt  gt  c  Dropbox backup DB diff log 2 gt  amp 1   The advantage of this method is that you don t need to change anything if you add new database or delete a database  you don t even need to list the databases in the script  Answer from JohnB is better simpler for server with one database  this approach is more suitable for multi database servers

User · Answer

The folks at MSSQLTips have some very helpful articles  the one most relevant for this is  Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files   The basic approach is to set up two tasks using the Windows Task Scheduler  One task runs a TSQL script that generates separate backup files for all MSSQL databases  except TEMPDB  with the database name and a date time stamp in the file name into a specified directory  The second task runs a VBScript script that goes through that directory and deletes all files with a  BAK extension that are more than 3 days old   Both scripts require minor editing for your environment  paths  how long to keep those database dumps  but are very close to drop-in-and-run    Note that there are possible security implications if you re sloppy with these or with directory permissions  since they are plain text files that will need to run with some level of privilege  Don t be sloppy

User · Answer

Eduardo Molteni had a great answer   SQL Server Automated Backups   Using Windows Scheduled Tasks  In the batch file   quot C  Program Files Microsoft SQL Server 100 Tools Binn SQLCMD EXE quot  -S   local  SQLExpress -i D  dbbackups SQLExpressBackups sql   In SQLExpressBackups sql  BACKUP DATABASE MyDataBase1 TO  DISK   N D  DBbackups MyDataBase1 bak   WITH NOFORMAT  INIT   NAME   N MyDataBase1 Backup   SKIP  NOREWIND  NOUNLOAD   STATS   10  BACKUP DATABASE MyDataBase2 TO  DISK   N D  DBbackups MyDataBase2 bak   WITH NOFORMAT  INIT   NAME   N MyDataBase2 Backup   SKIP  NOREWIND  NOUNLOAD   STATS   10  GO

User · Answer

You can create a backup device in server object  let us say      BDTEST   and then create a batch file contain following command  sqlcmd -S 192 168 1 25 -E -Q  BACKUP DATABASE dbtest TO BDTEST    let us say with name      backup bat   then you can call      backup bat   in task scheduler according to your convenience

User · Answer

You cannot use the SQL Server agent in SQL Server Express. The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

User · Answer

We have used the combination of    Cobian Backup for scheduling maintenance ExpressMaint for backup   Both of these are free  The process is to script ExpressMaint to take a backup as a Cobian  before Backup  event  I usually let this overwrite the previous backup file  Cobian then takes a zip 7zip out of this and archives these to the backup folder  In Cobian you can specify the number of full copies to keep  make multiple backup cycles etc   ExpressMaint command syntax example   expressmaint -S HOST SQLEXPRESS -D ALL USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

User · Answer

You cannot use the SQL Server agent in SQL Server Express. The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

User · Answer

We have used the combination of    Cobian Backup for scheduling maintenance ExpressMaint for backup   Both of these are free  The process is to script ExpressMaint to take a backup as a Cobian  before Backup  event  I usually let this overwrite the previous backup file  Cobian then takes a zip 7zip out of this and archives these to the backup folder  In Cobian you can specify the number of full copies to keep  make multiple backup cycles etc   ExpressMaint command syntax example   expressmaint -S HOST SQLEXPRESS -D ALL USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

User · Answer

You cannot use the SQL Server agent in SQL Server Express. The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

[backup] How can I schedule a daily backup with SQL Server Express?

Examples related to backup

Examples related to sql-server-express

Examples related to scheduled-tasks