{"id":99,"date":"2012-09-23T22:58:21","date_gmt":"2012-09-24T03:58:21","guid":{"rendered":"https:\/\/danvanfleet.com\/index.php\/automated-softpro-sql-database-backup\/"},"modified":"2013-03-11T16:25:51","modified_gmt":"2013-03-11T21:25:51","slug":"automated-softpro-sql-database-backup","status":"publish","type":"post","link":"https:\/\/danvanfleet.com\/index.php\/automated-softpro-sql-database-backup\/","title":{"rendered":"Automated SoftPro SQL Database Backup"},"content":{"rendered":"

Backing up the SoftPro SQL database, isn”t that done automatically was the question of the week.\u00a0 <\/span>Well yes and no is the answer, if you have a SQL database many times it is not included in your backup.\u00a0 <\/span>Especially if you”ve switched to a Cloud based backup.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

In my humble opinion The entire SoftPro Database should be backed up in this fashion on a regular basis even if your main backup catches it.\u00a0 <\/span>There is too much valuable data in these files not to be specially protected.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

So what do we do?<\/span><\/p>\n

<\/p>\n

Well there are a few possible answers to that question, here we backup a SQL Express database, version is 2008. There are better tools if you have SQL standard edition.\u00a0 <\/span>In my travels SoftPro runs fine on Express edition, although I like a few of the tools in Standard edition, so that”s what my shop runs. <\/span><\/p>\n

\u00a0<\/span><\/p>\n

Open Microsoft SQL Server Management Studio, opening the instance running SoftPro.<\/span><\/p>\n

Click on New Query<\/span><\/p>\n

Paste the following text in the New Query window. Or Better yet Download the Zip of SqlBackup<\/a> for the 2 files necessary to put this all in place. The Web copy and paste is not being my friend right now.
\n<\/span><\/p>\n

\n
\n
--\/\/ Copyright \u00a9 Microsoft Corporation. All Rights Reserved.<\/span><\/pre>\n
<\/p>\n
--\/\/ This code released under the terms of the --<\/span><\/pre>\n
<\/p>\n
--\/\/ Microsoft Public License (MS-PL, http:\/\/opensource.org\/licenses\/ms-pl.html.)<\/span><\/pre>\n
<\/p>\n
USE<\/span> [master]<\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
\/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******\/ <\/span><\/pre>\n
<\/p>\n
SET<\/span> ANSI_NULLS ON<\/span><\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
SET<\/span> QUOTED_IDENTIFIER ON<\/span><\/pre>\n
<\/p>\n
GO<\/span><\/pre>\n
<\/p>\n
-- ============================================= <\/span><\/pre>\n
<\/p>\n
-- Author: Microsoft <\/span><\/pre>\n
<\/p>\n
-- Create date: 2010-02-06<\/span><\/pre>\n
<\/p>\n
-- Description: Backup Databases for SQLExpress <\/span><\/pre>\n
<\/p>\n
-- Parameter1: databaseName <\/span><\/pre>\n
<\/p>\n
-- Parameter2: backupType F=full, D=differential, L=log<\/span><\/pre>\n
<\/p>\n
-- Parameter3: backup file location<\/span><\/pre>\n
<\/p>\n
-- ============================================= <\/span><\/pre>\n
<\/p>\n
CREATE<\/span> PROCEDURE<\/span> [dbo].[sp_BackupDatabases]<\/pre>\n
<\/p>\n
@databaseName sysname = null<\/span>,<\/pre>\n
<\/p>\n
@backupType CHAR<\/span>(1),<\/pre>\n
<\/p>\n
@backupLocation nvarchar(200)<\/pre>\n
<\/p>\n
AS<\/span><\/pre>\n
<\/p>\n
SET<\/span> NOCOUNT ON<\/span>;<\/pre>\n
<\/p>\n
DECLARE<\/span> @DBs TABLE<\/span><\/pre>\n
<\/p>\n
(<\/pre>\n
<\/p>\n
ID int<\/span> IDENTITY<\/span> PRIMARY<\/span> KEY<\/span>,<\/pre>\n
<\/p>\n
DBNAME nvarchar(500)<\/pre>\n
<\/p>\n
)<\/pre>\n
<\/p>\n
-- Pick out only databases which are online in case ALL databases are chosen to be backed up<\/span><\/pre>\n
<\/p>\n
-- If specific database is chosen to be backed up only pick that out from @DBs<\/span><\/pre>\n
<\/p>\n
INSERT INTO<\/span> @DBs (DBNAME)<\/pre>\n
<\/p>\n
SELECT<\/span> Name FROM<\/span> master.sys.databases<\/pre>\n
<\/p>\n
where<\/span> state<\/span>=0<\/pre>\n
<\/p>\n
AND<\/span> name=@DatabaseName<\/pre>\n
<\/p>\n
OR<\/span> @DatabaseName IS<\/span> NULL<\/span><\/pre>\n
<\/p>\n
ORDER<\/span> BY<\/span> Name<\/pre>\n
<\/p>\n
-- Filter out databases which do not need to backed up<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType=''F''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
DELETE<\/span> @DBs where<\/span> DBNAME IN<\/span> (''tempdb''<\/span>,''Northwind''<\/span>,''pubs''<\/span>,''AdventureWorks''<\/span>)<\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
ELSE<\/span> IF<\/span> @backupType=''D''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
DELETE<\/span> @DBs where<\/span> DBNAME IN<\/span> (''tempdb''<\/span>,''Northwind''<\/span>,''pubs''<\/span>,''master''<\/span>,''AdventureWorks''<\/span>)<\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
ELSE<\/span> IF<\/span> @backupType=''L''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
DELETE<\/span> @DBs where<\/span> DBNAME IN<\/span> (''tempdb''<\/span>,''Northwind''<\/span>,''pubs''<\/span>,''master''<\/span>,''AdventureWorks''<\/span>)<\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
ELSE<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
RETURN<\/span><\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
-- Declare variables<\/span><\/pre>\n
<\/p>\n
DECLARE<\/span> @BackupName varchar<\/span>(100)<\/pre>\n
<\/p>\n
DECLARE<\/span> @BackupFile varchar<\/span>(100)<\/pre>\n
<\/p>\n
DECLARE<\/span> @DBNAME varchar<\/span>(300)<\/pre>\n
<\/p>\n
DECLARE<\/span> @sqlCommand NVARCHAR(1000)<\/pre>\n
<\/p>\n
DECLARE<\/span> @dateTime NVARCHAR(20)<\/pre>\n
<\/p>\n
DECLARE<\/span> @Loop int<\/span><\/pre>\n
<\/p>\n
-- Loop through the databases one by one<\/span><\/pre>\n
<\/p>\n
SELECT<\/span> @Loop = min<\/span>(ID) FROM<\/span> @DBs<\/pre>\n
<\/p>\n
WHILE<\/span> @Loop IS<\/span> NOT<\/span> NULL<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
-- Database Names have to be in [dbname] format since some have - or _ in their name<\/span><\/pre>\n
<\/p>\n
SET<\/span> @DBNAME = ''[''<\/span>+(SELECT<\/span> DBNAME FROM<\/span> @DBs WHERE<\/span> ID = @Loop)+'']''<\/span><\/pre>\n
<\/p>\n
-- Set the current date and time n yyyyhhmmss format<\/span><\/pre>\n
<\/p>\n
SET<\/span> @dateTime = REPLACE(CONVERT<\/span>(VARCHAR<\/span>, GETDATE(),101),''\/''<\/span>,''''<\/span>) + ''_''<\/span> + REPLACE(CONVERT<\/span>(VARCHAR<\/span>, GETDATE(),108),'':''<\/span>,''''<\/span>)<\/pre>\n
<\/p>\n
-- Create backup filename in pathfilename.extension format for full,diff and log backups<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''F''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>)+ ''_FULL_''<\/span>+ @dateTime+ ''.BAK''<\/span><\/pre>\n
<\/p>\n
ELSE<\/span> IF<\/span> @backupType = ''D''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>)+ ''_DIFF_''<\/span>+ @dateTime+ ''.BAK''<\/span><\/pre>\n
<\/p>\n
ELSE<\/span> IF<\/span> @backupType = ''L''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>)+ ''_LOG_''<\/span>+ @dateTime+ ''.TRN''<\/span><\/pre>\n
<\/p>\n
-- Provide the backup a name for storing in the media<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''F''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupName = REPLACE(REPLACE(@DBNAME,''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>) +'' full backup for ''<\/span>+ @dateTime<\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''D''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupName = REPLACE(REPLACE(@DBNAME,''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>) +'' differential backup for ''<\/span>+ @dateTime<\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''L''<\/span><\/pre>\n
<\/p>\n
SET<\/span> @BackupName = REPLACE(REPLACE(@DBNAME,''[''<\/span>,''''<\/span>),'']''<\/span>,''''<\/span>) +'' log backup for ''<\/span>+ @dateTime<\/pre>\n
<\/p>\n
-- Generate the dynamic SQL command to be executed<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''F''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
SET<\/span> @sqlCommand = ''BACKUP DATABASE ''<\/span> +@DBNAME+ '' TO DISK = ''<\/span>''''<\/span>+@BackupFile+ ''''<\/span>'' WITH INIT, NAME= ''<\/span>''''<\/span> +@BackupName+''''<\/span>'', NOSKIP, NOFORMAT''<\/span><\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''D''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
SET<\/span> @sqlCommand = ''BACKUP DATABASE ''<\/span> +@DBNAME+ '' TO DISK = ''<\/span>''''<\/span>+@BackupFile+ ''''<\/span>'' WITH DIFFERENTIAL, INIT, NAME= ''<\/span>''''<\/span> +@BackupName+''''<\/span>'', NOSKIP, NOFORMAT''<\/span><\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
IF<\/span> @backupType = ''L''<\/span><\/pre>\n
<\/p>\n
BEGIN<\/span><\/pre>\n
<\/p>\n
SET<\/span> @sqlCommand = ''BACKUP LOG ''<\/span> +@DBNAME+ '' TO DISK = ''<\/span>''''<\/span>+@BackupFile+ ''''<\/span>'' WITH INIT, NAME= ''<\/span>''''<\/span> +@BackupName+''''<\/span>'', NOSKIP, NOFORMAT''<\/span><\/pre>\n
<\/p>\n
END<\/span><\/pre>\n
<\/p>\n
-- Execute the generated SQL command<\/span><\/pre>\n
<\/p>\n
EXEC<\/span>(@sqlCommand)<\/pre>\n
<\/p>\n
-- Goto the next database<\/span><\/pre>\n
<\/p>\n
SELECT<\/span> @Loop = min<\/span>(ID) FROM<\/span> @DBs where<\/span> ID>@Loop<\/pre>\n
<\/p>\n
END<\/pre>\n
<\/p>\n<\/div>\n<\/div>\n
\n