Advertisements
Home > Denali, Powershell, SQL Server, SQL Server 2008 R2 > Powershell – Powershell Script to backup all user DBs in SQL Server

Powershell – Powershell Script to backup all user DBs in SQL Server

Powershell is Microsoft’s task automation framework.

The following is a simple powershell script to take full backup of all users database of MS SQL Server, this script is tested on MS SQL Server 2008R2 and MS SQL Server 2012(Denali).

  • Copy the following script into a .txt file and save as BackupAllDatabase.PS1 on your preferred location ( PS1 is powershell extenstion )
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

$sqlobject = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MSSQLSERVER\SQL2012"

write-host "Simple Powershell script to backup all user database in MS SQL Server"

$Databases = $sqlobject.Databases
$backuppath = "D:\Backup"
foreach ($Database in $Databases)
{
 if($Database.Name -ne "tempdb" -and $Database.Name -ne "Master" -and $Database.Name -ne "Model" -and $Database.Name -ne "MSDB")
 #To skip system databases
 {
 write-host ".... Backup in progress for " $Database.Name "...."

$dbname = $Database.Name
 $dt = get-date -format MMddyyyy-HHmm
 $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
 $dbBackup.Action = "Database" # For full database backup, can also use "Log" or "File"
 $dbBackup.Database = $dbname
 $dbBackup.Devices.AddDevice($backuppath + "\" + $dbname + "-" + $dt + ".bak", "File")
 $dbBackup.SqlBackup($sqlobject)
 }
}
write-host "...........Finished..........."
  • Open Powershell, Go to START – ALL Programs – Accessories – Windows Powershell – Windows Powershell.
  • Execute the script saved on Step 1, as follows.
  • Once it starts executing you will see the output as follows.
  • Confirm backup files.

You are done with your backup… Good Luck

Thanks

Manish Kaushik (Poweshell Expert)

Advertisements
  1. Sandip
    May 25, 2012 at 12:29 pm

    Hi Manish,

    I am facing issue while executing you BackUp scripts using POWERSHELL window. Kindly find below details for the same.

    File C:\Users\Top.Jack\Desktop\BackupAllDatabase.PS1 cannot be loaded because the execution of scripts is disabled
    on this system. Please see “get-help about_signing” for more details.
    At line:1 char:24
    + .\BackupAllDatabase.PS1 <<<<
    + CategoryInfo : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException

    • Manish Kaushik
      May 25, 2012 at 1:24 pm

      Hi Sandip,

      Thanks for visiting https://bi-bigdata.com/.
      Sandip, you need to set execution policy of scripts in powershell, by default execution policy in RESTRICTED which means No scripts can be run. You need to set it to UNRESTRICTED by issuing following command on your powershell windows. For more information about the execution policy please visit http://technet.microsoft.com/en-us/library/ee176961.aspx

      Set-ExecutionPolicy Unrestricted

      C:\Users\YourName> Set-ExecutionPolicy Unrestricted

      Thanks
      Manish

  2. Anonymous
    May 31, 2012 at 8:23 pm

    Thanks alot issue resolved at my end too, i am greatful for your guidelines.

  3. Biswajit
    September 12, 2012 at 1:27 pm

    Hi Manish,
    I’ve 25 user & they are using MSOutlook for his mail client . I want to take pst file backup using powershell script without closing & disturbing user works . Can you help me .

    B

    • Manish Kaushik
      September 13, 2012 at 9:50 pm

      Biswajit,
      Thanks for visiting https://bi-bigdata.com/.
      I will try to provide you solution at my earliest.

      Thanks
      Manish

    • Manish Kaushik
      September 14, 2012 at 12:51 pm

      Biswajit,

      I tried to backup all my outlook files from default to a new location, using this following Powershell script.

      write-host "Script to copy files from a folder to another in Powershell"
      
      $ToFolder = "C:\New\" 
      $FromFolder = Get-ChildItem -Path "C:\Users\Manish\AppData\Local\Microsoft\Outlook"
      
      foreach ($file in $FromFolder )
      {
      
              $NewFolder = $ToFolder+$file.Name
       
              Write-Host $file.FullName
              Write-Host $Destination           
              Copy-Item $file.FullName -destination $NewFolder
      }
      

      But unfortunately like other applications and languages even simple copy/paste technique in file server, Powershell too generates an exception as ” Copy-Item : The process cannot access the file because another process has locked a portion of the file. “. And I think even if it allows to backup when MS Outlook is running then there will be no guarantee that backup which we will have, will be consistent.

      Thanks
      Manish

  4. thota
    November 7, 2012 at 12:53 am

    can we backup to more than one location? i mean, can i add more devices? i tried this on my computer it doesnot work.

    • Manish Kaushik
      December 16, 2012 at 2:25 am

      Hi Thota,
      Thanks for visiting https://bi-bigdata.com/. and after a long time I happened to work on power shell and it is pretty easy to add more devices, please try as follows, and let us know if you face any problems.

      
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
      
      $sqlobject = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MSSQLSERVER\SQL2012"
      
      write-host "Simple Powershell script to backup all user database in MS SQL Server"
      
      $Databases = $sqlobject.Databases
      $backuppath = "D:\Backup"
      $backuppath1 = "C:\Backup"
      foreach ($Database in $Databases)
      {
       if($Database.Name -ne "tempdb" -and $Database.Name -ne "Master" -and $Database.Name -ne "Model" -and $Database.Name -ne "MSDB")
       #To skip system databases
       {
       write-host ".... Backup in progress for " $Database.Name "...."
      
      $dbname = $Database.Name
       $dt = get-date -format MMddyyyy-HHmm
       $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
       $dbBackup.Action = "Database" # For full database backup, can also use "Log" or "File"
       $dbBackup.Database = $dbname
       $dbBackup.Devices.AddDevice($backuppath + "\" + $dbname + "-" + $dt + ".bak", "File")
        $dbBackup.Devices.AddDevice($backuppath1 + "\" + $dbname + "-" + $dt + ".bak", "File")
        $dbBackup.SqlBackup($sqlobject)
       }
      }
      write-host "...........Finished..........."
      
      

      Thanks
      Manish

  5. April 20, 2013 at 6:29 pm

    This information is worth everyone’s attention. When can I find out more?

    • Manish Kaushik
      April 22, 2013 at 12:20 pm

      Thanks for visiting https://bi-bigdata.com/, very soon we will come up with something more interesting.

  6. July 20, 2013 at 8:04 am

    Wow, superb blog layout! How long have you been blogging for?
    you make blogging look easy. The overall look of your site is wonderful, let alone the content!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: