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)
Categories: Denali, Powershell, SQL Server, SQL Server 2008 R2
#Powershell, Denali, powershell for
Leave a Reply
BI Big Data Authors
Sandip Shinde
Manish Kaushik
Suhas Kudekar(MCC)
Umesh M Patil
YashwantOur Visitors
Big Data, Data Analytics and Data Science | BI-BIGDATA.com 
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
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
Thanks alot issue resolved at my end too, i am greatful for your guidelines.
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
Biswajit,
Thanks for visiting https://bi-bigdata.com/.
I will try to provide you solution at my earliest.
Thanks
Manish
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
can we backup to more than one location? i mean, can i add more devices? i tried this on my computer it doesnot work.
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
This information is worth everyone’s attention. When can I find out more?
Thanks for visiting https://bi-bigdata.com/, very soon we will come up with something more interesting.
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!
Thanks for your good words and visiting https://bi-bigdata.com/.