SQL 2005 Express Edition Backups

If you are using or have an application that is using the free version of SQL 2005 (Express), then you should install the free utility to manage it, Microsoft SQL Server Management Studio Express.  It provides a simple interface to manage instances of SSDE.  You can download this from Microsoft here.

It is very light weight, not taking up much space.  You can still use SQLcmd to script and schedule it through Scheduled Tasks if you prefer.  It can be found in C:\Program Files\Microsoft SQL Server\90\Tools\binn  or  C:\Program Files\Windows Resource Kits\Tools  .

First create a TSQL file.  You can create this using SQL Server 2005 Express Studio to get the proper syntax.  First create a backup, then while in the backup, go to the top bar and select Script - Script Action to File, and it will create the .sql file for you :

You will notice that Express defaults to a Simple Recovery.  If you need to perform a Full Recovery you will need to right click on your database, select New Query, paste the code below changing [Linda] for your database name, then select Execute.  Then you will notice you will now have the Transaction Log as an option under Backup Method.

USE [master]
GO
/****** Object:  Database [Linda]    Script Date: 09/04/2008 12:57:52 ******/
ALTER DATABASE [Linda] SET RECOVERY FULL
GO

Example of Backup of a Database:

BACKUP DATABASE [Linda] TO  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Linda.bak' WITH NOFORMAT, NOINIT,  NAME = N'Linda-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Example of Backup of a Database with FULL, Append to Existing and Verify:

BACKUP DATABASE [Linda] TO  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Linda.bak' WITH NOFORMAT, NOINIT,  NAME = N'Linda-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Linda' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Linda' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Linda'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Linda.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Example of Shrink of a Transaction Log:

USE [Linda]
GO
DBCC SHRINKFILE (N'Linda_log' , 1)
GO

You should always backup the Transaction Log before Shrinking it.  Example:

BACKUP LOG [Linda] TO  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Linda.bak' WITH NOFORMAT, NOINIT,  NAME = N'Linda-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Linda' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Linda' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Linda'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Linda.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Then create your command for the Scheduled Task:

Example:  SQLcmd -S ComputerName\Instance -E -i "LocationOfINPUTfile\Backup.sql"

4/6/2009 11:13 PM