asked on

SQL 2008 Express - How to automatically backup ALL DATABASES

Hello,

I have a SQL 2008 Express server that has about 10 databases attached to it. Since it's SQL 2008 Express, I am unable to create a maintenance plan without scripting one out and scheduling the scripts using task scheduler.

While I was creating the scripts for the maintenance plan, I found that I could enumerate all of the databases on the server and output the list to a file with the following command:

sqlcmd -E -S SERVERNAME -Q "use master; select name from sysdatabases;" > c:\temp\output.txt

The result listed all of the attached databases, as well as a separator header of dashes "------------------" , and the successful change to the context 'master' like so:

<---SNIP--->
Changed database context to 'master'.
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
config
staging
warehouse
ods_cms_ds1
masterrmaster
SharePoint_Config
SharePoint_AdminContent_e6ae53e0-45af-4d1c-82ad-f4f1dbe132b0
WSS_Content
PerformancePoint Service Application_fc449ab6d129409c98511c872332405c
WordAutomationServices_b7e50fa22f3b4768be8cd013834d021c
Search_Service_Application_DB_9451ad98291c41db93bddbe93b29709d
Search_Service_Application_PropertyStoreDB_320b7718732548c086e358368131bda5
Search_Service_Application_CrawlStoreDB_c1e4f3c7299942d3a9aa7b2539a43cec
Secure_Store_Service_DB_5cea423c68cf45398922eb4ef8061c8a
StateService_9dd67c4561a24af08b3fc074a7cf8b20
Managed Metadata Service_46436773146e41278bcee32432075244
WebAnalyticsServiceApplication_StagingDB_90820248-1b80-4f24-907e-9bf5d7511fc4
WebAnalyticsServiceApplication_ReportingDB_42d38c4b-82f4-49f2-823c-c0280a4ccf6d
WSS_Logging
Application_Registry_Service_DB_349ee92cf9214efb8e621b3a5137d30e
User Profile Service Application_ProfileDB_b00429bee3814bba97bd4dd257074561
User Profile Service Application_SyncDB_ca2a65aaa56d41ec965d3e6ba253abcc
User Profile Service Application_SocialDB_4aab827de54343a29a25d5ea7f24999d
Bdc_Service_DB_5a4444ab9aa6491688e69afce216c33f
PBXLogger
WSS_Content_157f1fddd74c4197a8d8839c28723dbc
test1
Special

(34 rows affected)
<---SNIP--->

I want to create a script that will do this connected database enumeration as above, and backup EVERY DATABASE listed on the server in this output command. I was thinking of using a "FOR" loop with /f to read in the file, and to run a backup for each database using the following SQLCMD script:

sqlcmd -E -S SERVERNAME -Q "backup database %%F to disk='c:\temp\%%F.bak'"

I am not sure how to get around skipping the lines:

name
----------------------------------
(34 rows affected)

Does anyone have a solution? I would like to have this happen so that when I encounter a SQL EXPRESS server at a client, I can use this script to list ALL databases, back them up to file, and know that we at least have that one single backup.

Also, this can be used with WINDOWS TASK SCHEDULER to be a CURE-ALL for SQL EXPRESS and the absence of a MAINTENANCE PLAN.

- List all databases
- Backup all listed databases
- Keep calm and query on!

SOLUTION

jkeegan123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

George Zarif

Why don't you use the windows backup tools?

jkeegan123

ASKER

Because I need to create a script that will backup ALL SQL databases on a server. I don't want to look at all of the databases and create a backup job, hopefully not missing any...I want a script that will, without fail, backup ALL SQL databases on a server to .BAK files, and this script does that PERFECTLY....it just writes the filenames with LOTS of spaces in them.

Can anyone help me fix this?

ASKER CERTIFIED SOLUTION

Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jkeegan123

ASKER

@Ray: Thank you, this script is interesting.....and will certainly help in a SQL EXPRESS only environment. However, it seems that this SP is specifically excluding system databases like MASTER, TEMPDB, etc....

I am specifically on this quest because I had a customer who corrupted their MASTER database and SQL service would not start until we did a rebuild of MASTER using the installation media.

I want a backup that will, without prejudice, backup EVERYTHING. I want to give this script to techs that know NOTHING about SQL and say, "LISTEN, JUST RUN THIS AND EVERYTHING WILL BE GOOD".

What do you think, can we change this script to do that?

Ray

You cannot back up the tempdb system database.
http://msdn.microsoft.com/en-CA/library/ms190190.aspx

With the script, all databases (Including Master, MSDB, MODEL) are backup except tempdb and the sample databases. I've modified the code to exclude the tempdb.

    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')
                      END
                ELSE IF @backupType='D'
                      BEGIN
                      DELETE @DBs where DBNAME IN ('tempdb')
                      END
                ELSE IF @backupType='L'
                      BEGIN
                      DELETE @DBs where DBNAME IN ('tempdb')
                      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

Open in new window

jkeegan123

ASKER

This is an awesome script... Thank you.

Still, I really want to get the results of the posted script to truncate leading spaces.

Maybe there's a better way to ask the question :

Is there any way to give a query by way of script, not stored procedure, that will return the names of all databases on the sql server by name, without the trailing spaces? The query that I posted gives what I need, but the trailing spaces makes the file names... Weird. I can live with it if I have to but... I'd rather fix. Any ideas?

Ray

When you run the sqlcmd and output the results to text file, the output file is in fix length (Same width as the dashes '----' ). So when your batch script try to read output.txt file, it include the additional space as the database name. Not an expert on batch scripting and cannot assist you further,

Here're some samples in EE
https://www.experts-exchange.com/questions/26350920/Batch-File-Remove-spaces-in-variable.html

jkeegan123

ASKER

It backed up ALL databases, automatically.