Backup-sqldatabase, cannot connect to server

PowerShell Help
1

I’m trying to take backup of a database, but it keep saying this below. What am I doing wrong?
rei-epicor-db is the hostname of the server that have the database

This YouTube video is my reference Powershell To Back up SQL Server Database

Backup-SqlDatabase : Failed to connect to serve
At line:1 char:1
+ Backup-SqlDatabase -ServerInstance $Server -D
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:)
    + FullyQualifiedErrorId : Microsoft.SqlServ
   agement.PowerShell.BackupSqlDatabaseCommand

This is my PowerShell script

$Server = "REI-Epicor-db\SQLSERVER2016"
$Database = "EpicorERPPilot"
$BackupFolder = "C:\Backup"
New-Item -Path "C:\Backup" -ItemType Directory
$DT = Get-Date -Format MM-dd-yyyy
$FilePath = "$($BackupFolder)$($Database)_db_$($dt).bak"

But if I do this, I’m able to see the database info. I’m not passing the -credentials here either.
Get-SqlDatabase -ServerInstance 'rei-epicor-db'

2

Hi, welcome to the forum :wave:

Is the instance actually called SQLSERVER2016, or have you just updated the version because the guy in the video is using SQLSERVER2014 in his example?

The default instance will be called MSSQLSERVER. You don’t usually (ever?) need to specify the default instance name unless it’s not running on port 1433, you can just specify the server name.

If the default instance name has been changed, a quick way to find the instance name (or names if you have multiple instances) is to view what’s running under services.msc

3

@matt-bloomfield
I’m not sure what the instance is called, and yes, I just updated it to SQLSERVER2016 because that’s the version. So I’ll try $Server = "REI-Epicor-db\MSSQLSERVER" ?

So if its not MSSQLSERVER (default) check services.msc

4

If it’s MSSQLSERVER and running on the default port, you just use the server name:

$Server = 'REI-Epicor-db'
5

@matt-bloomfield

I think I tried just the server name $Server = 'REI-Epicor-db', and I was getting the same results as in the original question.

6

@matt-bloomfield
Actually, using just the server name worked. I had to login using a different for it to work.
Thanks

1 Like
7

Great, glad it’s working.

If you’re going to being doing MS SQL administration, Don Jones, who wrote the PowerShell in a Month of Lunches book also authored a SQL admin book which is an excellent intro to managing SQL Server: Learn SQL Server Administration in a Month of Lunches.