Solved

SQL 2008 Express - How to automatically backup ALL DATABASES

Posted on 2014-12-09
9
343 Views
Last Modified: 2015-01-04
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!
0
Comment
Question by:jkeegan123
  • 5
  • 3
9 Comments
 
LVL 5

Assisted Solution

by:jkeegan123
jkeegan123 earned 0 total points
ID: 40490299
Update to this:  I tried the following script:

sqlcmd -E -S %COMPUTERNAME% -Q "use master; select name from sysdatabases;" > output.txt
for /F "tokens=*" %%A in (output.txt) do sqlcmd -E -S %COMPUTERNAME% -Q "backup database %%A to disk='%%A.bak'"

And I got what I wanted as a result, ALL DATABASES were enumerated and backed up, HOWEVER....the OUTPUT.TXT file that is dumped from the first query has MANY trailing spaces that I did not realize were there, so the filenames that are being saved are:

"config                                                                          .bak"
"master                                                                         .bak"

etc....

How can I strip the TRAILING SPACES out of this script?
0
 
LVL 1

Expert Comment

by:HZarif
ID: 40490336
Why don't you use the windows backup tools?
0
 
LVL 5

Author Comment

by:jkeegan123
ID: 40490344
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?
0
 
LVL 7

Accepted Solution

by:
Ray earned 500 total points
ID: 40490358
Create this Stored Procedure in Master DB

// 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

Open in new window


And then setup a batch file

sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

You'll probably need to change -S .\EXPRESS to you SQL instance

More details in http://support.microsoft.com/kb/2019698
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Author Comment

by:jkeegan123
ID: 40491000
@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?
0
 
LVL 7

Expert Comment

by:Ray
ID: 40491395
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

0
 
LVL 5

Author Comment

by:jkeegan123
ID: 40491445
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?
0
 
LVL 7

Expert Comment

by:Ray
ID: 40500748
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
http://www.experts-exchange.com/Programming/Languages/Scripting/Shell/Batch/Q_26350920.html
0
 
LVL 5

Author Closing Comment

by:jkeegan123
ID: 40530010
It backed up ALL databases, automatically.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now