{"id":90,"date":"2018-10-05T05:43:40","date_gmt":"2018-10-05T05:43:40","guid":{"rendered":"http:\/\/www.spktechfit.com\/?p=90"},"modified":"2018-10-05T05:43:40","modified_gmt":"2018-10-05T05:43:40","slug":"how-to-easily-backup-ms-sql-databases-using-powershell","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=90","title":{"rendered":"How to (Easily) backup MS SQL Databases using Powershell"},"content":{"rendered":"

Backing up SQL databases with Powershell is quite useful and is probably easier than you think. This can be used in situations where you only have the SQL Server Express edition installed and can’t manage backups through the SQL Management Studio, or if you just want to do some one off backups<\/p>\n

\n

Install SQLServer Powershell module<\/h3>\n
\u00a01. Open a Powershell window as Administrator<\/div>\n
\u00a0 2. Run the command: Install-Module -Name SqlServer<\/em><\/div>\n<\/div>\n
<\/div>\n
\n
\n

Get a list of all databases on a remote server\u00a0<\/i><\/h3>\n<\/div>\n
\n

This will prompt you for credentials to use, then get a list of all the databases in the remote server SQL instance:
\nGet-SqlDatabase -Credential $Credential -ServerInstance yoursqlserver<\/em><\/p>\n

\n

Backing up a single database<\/i>\u00a0<\/i>\u00a0<\/i><\/h3>\n<\/div>\n
\n

The following will backup a single database to the default MS SQL backup directory, with the default backup name:
\nBackup-SqlDatabase -ServerInstance yourSQLServer -Database yourdatabase<\/em><\/p>\n

By adding the ‘BackupFile’ parameter we can select the location of where to put the backup:
\nBackup-SqlDatabase -ServerInstance yourSQLServer -Database yourdatabase -BackupFile “C:\\Backups\\BackupName.bak”<\/em><\/p>\n<\/div>\n

\n

Backup all databases in an SQL instance and name with todays date<\/i>\u00a0<\/i>\u00a0<\/i><\/h3>\n<\/div>\n
\n

The following gets all the databases in an instance (except the temp database) , then backups these databases and names them with todays date:
\nGet-SqlDatabase -Credential $Credential -ServerInstance yourSQLServer | foreach{<\/em>
\nBackup-SqlDatabase -DatabaseObject $_ -BackupFile “$($_.NAME)_db_$(Get-Date -UFormat %Y%m%d%H%M).bak”}<\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Backing up SQL databases with Powershell is quite useful and is probably easier than you think. This can be used in situations where you only have the SQL Server Express edition installed and can’t manage backups through the SQL Management Studio, or if you just want to do some one off backups Install SQLServer Powershell […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,2,3],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/90"}],"collection":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=90"}],"version-history":[{"count":1,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/90\/revisions"}],"predecessor-version":[{"id":91,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/90\/revisions\/91"}],"wp:attachment":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=90"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=90"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=90"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}