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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.