How can I create a full DB backup in MS SQL Server?

0
=
0
+
0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
1

I've tried to run this console command:

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

But it did not work!

Please help )

2 answers

2
=
0
=
$0
Internet users could send Bitcoin Tips to you if they like your answer!

Basically there are two steps:

Create a stored procedure in your master db. See msft link or if it's broken try here:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 5/25/2013 2:38:32 PM ******/
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 owner_sid != 1
            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
  1. Run this code from the console and make backups:

sqlcmd -S YOURSERVERNAME\SQLEXPRESS -E -Q "EXEC spBackupDatabases @backupLocation='C:\SQLBackup\', @backupType='F'" 1>c:\SQL_Backup\backup.log

I hope it helped you

SEND BITCOIN TIPS
1

To create a full database backup

  • Execute the BACKUP DATABASE statement to create the full database backup, specifying:

       o    The name of the database to back up.
    
    
       o    The backup device where the full database backup is written.
    
    
    The basic Transact-SQL syntax for a full database backup is:
     BACKUP DATABASE database
     TO backup_device [ ,...n ]
     [ WITH with_options [ ,...o ] ] ;
    

    enter image description here

         2. Optionally, specify one or more WITH options. A few basic WITH options are described here.    For information about all the WITH options, see BACKUP (Transact-SQL).
    
    
    
         o  Basic backup set WITH options:
    
    
        { COMPRESSION | NO_COMPRESSION }
    
    
        In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed                  on this backup, overriding the server-level default.
    
    
      Note:The server-level default is set by using the backup compression default option.
    
    
        DESCRIPTION = { 'text' | @text_variable }
    
    
        Specifies the free-form text that describes the backup set. The string can have a maximum of 255                 characters.
    
    
       NAME = { backup_set_name | @backup_set_name_var }
    
    
       Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
    
    
      o Basic backup set WITH options:
    
    
       By default, BACKUP appends the backup to an existing media set, preserving existing backup sets.   To explicitly specify this, use the NOINIT option. For more information, see Appending to Existing Backup Sets.
    
    
        Alternatively, to format the backup media, use the FORMAT option:
    
    
        FORMAT [ , MEDIANAME= { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text |@text_variable } ]
    
    
      Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
    

Important : Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.

            Example
          A. Backing up to a disk device

          The following example backs up the complete AdventureWorks2008R2 database to disk, by using   FORMAT to create a new media set.

          USE AdventureWorks2008R2;

          GO

         BACKUP DATABASE AdventureWorks2008R2

          TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
           WITH FORMAT,

           MEDIANAME = 'Z_SQLServerBackups',

           NAME = 'Full Backup of AdventureWorks2008R2'

            GO

           B. Backing up to a tape device

           The following example backs up the complete AdventureWorks2008R2 database to tape, appending the backup to the previous backups.

           USE AdventureWorks2008R2;    

            GO

           BACKUP DATABASE AdventureWorks2008R2

           TO TAPE = '\\.\Tape0'

          WITH NOINIT,  

         NAME = 'Full Backup of AdventureWorks2008R2';

         GO

         C. Backing up to a logical tape device

         The following example creates a logical backup device for a tape drive. The example then backs up the complete AdventureWorks2008R2 database to that device.

       -- Create a logical backup device, 

       -- AdventureWorks2008R2_Bak_Tape, for tape device \\.\tape0.
          USE master;

             C. Backing up to a logical tape device

            The following example creates a logical backup device for a tape drive. The example then backs up              the complete AdventureWorks2008R2 database to that device.

          -- Create a logical backup device, 

          -- AdventureWorks2008R2_Bak_Tape, for tape device \\.\tape0.
              USE master;

Reference

*BACKUP (Transact-SQL)

sp_addumpdevice (Transact-SQL)

Concepts

Appending to Existing Backup Sets

Full Database Backups

Considerations for Backing Up the master Database

Considerations for Backing Up the model and msdb Databases

Overwriting Backup Sets

Differential Database Backups

Working with Transaction Log Backups*

Read How to Backup Database in SQL Server to know the steps to take a Full Database Back up in SQL Server.

http://www.MyTechMantra.com/LearnSQLServer/How-to-Backup-Database-SQL-Server-P1.html

enter link description here

SEND BITCOIN TIPS
0

Too many commands? Learning new syntax?

FavScripts.com is a free tool to save your favorite scripts and commands, then quickly find and copy-paste your commands with just few clicks.

Boost your productivity with FavScripts.com!

Post Answer