SQL database backup script

Greetings,

I'm trying to backup databases from remote SQL server to specific location trough powershell and set it as automated task. I have sysadmin on SQL server and full access to folder where i want to put backups. I'm not familiar with scripting so any help would be welcome. Until now i searched couple of sites but all the examples were only focused on local database. So all i need is that i run script from server X which will backup all databases from server A to shared folder on server B using powershell.

Any help would be appreciated.

Thanks in advance
LVL 1
DewicedAsked:
Who is Participating?
 
Daniel_PLDB Expert/ArchitectCommented:
0
 
Umesh_MadapCommented:
PowerShell makes it easier to manage even your database backups and restore.

To do a SQL Server backup in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:

?123456 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null#Need SmoExtended for smo.backup [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") | Out-Null
Also another point to note is the types of backup you can do. BackupActionType specifies the type of backup. Valid values for this option are Database, Files, Log

Here’s the script. This script is for one specific database. If you want to use this for several database, you will just need to use this code inside a loop.
Better yet, put this in a function, and call this in a loop. I will try to do that sometime soon.

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657 #============================================================ # Backup a Database using PowerShell and SQL Server SMO # Script below creates a full backup # Donabel Santos #============================================================   #specify database to backup #ideally this will be an argument you pass in when you run #this script, but let's simplify for now $dbToBackup = "test"  #clear screen cls  #load assemblies #note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008 #otherwise may get this error #Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure #the assembly containing this type is loaded.   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null#Need SmoExtended for smo.backup [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") | Out-Null  #create a new server object $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"$backupDirectory = $server.Settings.BackupDirectory   #display default backup directory "Default Backup Directory: " + $backupDirectory  $db = $server.Databases[$dbToBackup] $dbName = $db.Name   $timestamp = Get-Date -format yyyyMMddHHmmss $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")   #BackupActionType specifies the type of backup. #Options are Database, Files, Log #This belongs in Microsoft.SqlServer.SmoExtended assembly   $smoBackup.Action = "Database"$smoBackup.BackupSetDescription = "Full Backup of " + $dbName$smoBackup.BackupSetName = $dbName + " Backup"$smoBackup.Database = $dbName$smoBackup.MediaDescription = "Disk"$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File") $smoBackup.SqlBackup($server)   #let's confirm, let's list list all backup files $directory = Get-ChildItem $backupDirectory  #list only files that end in .bak, assuming this is your convention for all backup files $backupFilesList = $directory | where {$_.extension -eq ".bak"} $backupFilesList | Format-Table Name, LastWriteTime
0
 
DewicedAuthor Commented:
Thank you.

You both were very helpfull and i managed to fix the whole thing by following the instructions from Daniel.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.