SQL Server 2008 Express – “Best” backup solution? – Managing your servers can streamline the performance of your team by allowing them to complete complex tasks faster. Plus, it can enable them to detect problems early on before they get out of hand and compromise your business. As a result, the risk of experiencing operational setbacks is drastically lower.
But the only way to make the most of your server management is to perform it correctly. And to help you do so, this article will share nine tips on improving your server management and fix some problem about windows, backup, sql-server-2008, sql, .
What backup solutions would you recommend when using SQL Server 2008 Express? I’m pretty new to SQL Server, but as I’m coming from an MySQL background I thought of setting up replication on another computer and just take Xcopy backups of that server.
But unfortunately replication is not available in the Express Edition.
The site is heavily accessed, so there has to be no delays and downtime. I’m also thinking of doing a backup twice a day or something.
What would you recommend? I have multiple computers I can use, but I don’t know if that helps me since I’m using the Express version.
SQL Server Express 2008 supports database backups. It’s missing SQL Agent, which allows to schedule backups, and the maintenance plan wizard for creating a backup tasks.
You can backup databases in two different ways:
- Use Microsoft SQL Server Management Studio Express which has the Backup option on the right click menu for each database under “tasks.”
- Use T-SQL to manually write your backup script. Read the MSDN documentation for the T-SQL BACKUP command.
Syntax something like:
BACKUP DATABASE MyDatabase TO DISK='C:MyDatabase.bak';
If you want to schedule your backup jobs, you have to write a T-SQL script and then use the Windows Task Schedule to call SQLCmd to run the script on what every schedule you’re interested in:
sqlcmd -s server_namesqlexpress -i C:SqlJobsbackup.sql -o C:Logsoutput.txt
I use SQLBackupAndFTP – fantastic and simple product.
I was wrote backup script for use myself, install as metioned in post by splattne:
----- Version: 2.0 - 2009-12-12 - SQL 2000 Compatible ----- Pham Kim Ngan (email@example.com) ----- Usage: -- Copy 7za.exe (http://www.7-zip.org/download.html - Command Line Version) to @CFG_BACKUP_PATH -- Modify @CFG_BACKUP_PATH = <Backup Store Path> - no long filename/directory please -- Modify @CFG_DAYS_DELETE = Days to keep backups -- Enable 'xp_cmdshell' (SQL 2005/EXPRESS or higher) ----- Configuration Variables DECLARE @CFG_BACKUP_PATH NVARCHAR(256) DECLARE @CFG_DAYS_DELETE INT SET @CFG_BACKUP_PATH = 'C:DatabaseBackup' SET @CFG_DAYS_DELETE = 30 DECLARE @Today DATETIME DECLARE @TodayName CHAR(8) SET @Today = GETDATE() SET @TodayName = CONVERT(CHAR(8), @Today, 112) DECLARE @id INT DECLARE @name VARCHAR(50) DECLARE @path VARCHAR(256) DECLARE @cmd VARCHAR(256) ----- Create Temporarity Directory DECLARE @TempDir VARCHAR(256) SET @TempDir = @CFG_BACKUP_PATH + '' + CONVERT(VARCHAR(256), NEWID()) SET @cmd = 'md ' + @TempDir EXEC xp_cmdshell @cmd, no_output ----- List of current databases, only 'ONLINE' databases to be backup DECLARE @dbList TABLE ( dbno INT IDENTITY, dbname NVARCHAR(256) ) INSERT INTO @dbList ( dbname ) SELECT name FROM master.dbo.sysdatabases WHERE ( name NOT IN ( 'tempdb' ) ) AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ------ Starting backup, one by one SELECT @id = dbno, @name = dbname FROM @dbList WHERE dbno = 1 WHILE @@ROWCOUNT = 1 BEGIN PRINT N'++ Backup: ' + @name SET @path = @TempDir + '' + @name + '.bak' BACKUP DATABASE @name TO DISK = @path SELECT @id = dbno, @name = dbname FROM @dbList WHERE dbno = @id + 1 END PRINT N'++ Compressing: ' + @TempDir ----- Delete output file if existed SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '' + @TodayName + '.ZIP' EXEC xp_cmdshell @cmd, no_output DECLARE @Count INT DECLARE @StartTime DATETIME SET @StartTime = GETDATE() ----- Compress, -mx1 = Set Compression Ratio to 1 (very low) SET @cmd = @CFG_BACKUP_PATH + '7za.exe a -bd -y -tzip -mx1 ' SET @cmd = @cmd + @CFG_BACKUP_PATH + '' + @TodayName + '.ZIP ' + @TempDir + '*.bak"' EXEC xp_cmdshell @cmd, no_output SET @Count = DATEDIFF(second, @StartTime, GETDATE()) PRINT N'++ Compression Time: ' + CONVERT(VARCHAR, @Count) + ' seconds' SET @Count = DATEDIFF(second, @Today, GETDATE()) PRINT N'++ Total Execution Time: ' + CONVERT(VARCHAR, @Count) + ' seconds' ---- Delete temporarity directory SET @cmd = 'rd /s /q ' + @TempDir EXEC xp_cmdshell @cmd, no_output ---- Delete previous backup versions DECLARE @OlderDateName CHAR(8) SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112) ----- List all .ZIP files CREATE TABLE #delList ( subdirectory VARCHAR(256), depth INT, [file] BIT ) INSERT INTO #delList EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1 DELETE #delList WHERE RIGHT(subdirectory, 4) <> '.ZIP' SELECT @Count = COUNT(1) FROM #delList PRINT N'++ Number of Backups: ' + CONVERT(NVARCHAR, @Count) SELECT TOP 1 @name = subdirectory FROM #delList WHERE LEN(subdirectory) = 12 AND RIGHT(subdirectory, 4) = '.ZIP' AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName WHILE ( @@ROWCOUNT = 1 ) BEGIN PRINT N'++ Delete Older Backup: ' + @name SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '' + @name EXEC xp_cmdshell @cmd, no_output DELETE #delList WHERE subdirectory = @name SELECT TOP 1 @name = subdirectory FROM #delList WHERE LEN(subdirectory) = 12 AND RIGHT(subdirectory, 4) = '.ZIP' AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName END DROP TABLE #delList PRINT N'++ Done.' PRINT '' PRINT '' PRINT ''
I use ExpressMaint, and it works great as a scheduled task. Just pass the appropriate parameters for the type of job you are doing.
The source code is also out there. We changed it slightly to add an entry in the application event log on failure.