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...
This question is related to
backup
sql-server-express
scheduled-tasks
Using Windows Scheduled Tasks:
In the batch file
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -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
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"
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.
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>@Loop
END
And batch file can look like this:
sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='F'" >> c:\Dropbox\backup\DB\full.log 2>&1
and
sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='D'" >> c:\Dropbox\backup\DB\diff.log 2>&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.
Using Windows Scheduled Tasks:
In the batch file
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -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
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>@Loop
END
And batch file can look like this:
sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='F'" >> c:\Dropbox\backup\DB\full.log 2>&1
and
sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='D'" >> c:\Dropbox\backup\DB\diff.log 2>&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.
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
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"
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
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
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"
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
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.
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
Source: Stackoverflow.com