Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Best way to back up SQL server on remote server.

Posted on 2016-08-17
5
Medium Priority
?
46 Views
Last Modified: 2016-08-23
Experts,

I have a domain running SQL Server 2008R2 (no Backup-SqlDatabase cmdlet) and I need to do the following:

Connect to the remote SQL server from another computer as a local administrator.  Connect to the server/port for a company (there are a few hundred) then back up all the databases that exist in that instance.  I also need to store somewhere (a text file in the backup location or etc) whether the databases are SQL express or SQL standard (part 2 of the script moves them and installs a new instance then restores the .baks which is not included in this question).

What I have so far is getting the DB server and port and creating a new PSSession on the target server, but not sure how to do the connect, get all the databases and store the sql version part.  Advice appreciated.

function MigrateSQLBackup($c, $oldINI)
{
    $portNumber = Get-Content $oldINI | ?{$_ -like "DBServer=*"}
    $portNumber = $portNumber.Replace("DBServer=","").Split(',')
    $sqlS = $portNumber[0]
    $port = $portNumber[1]
    $unameLA = "$sqlS\Administrator"
    $pFileLA = $credentialFolder + "Users_localAdmin_" + ([Environment]::UserName) +"_password.pwd"
    $passwordLA = Get-Content $pFileLA | ConvertTo-SecureString
    $credentialLA = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $unameLA,$passwordLA
    $usersSession = New-PSSession -ComputerName $sqlS -Credential $credentialLA
    #..... do the sql stuff
}

Open in new window

0
Comment
Question by:Dustin Saunders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 2000 total points
ID: 41761080
To get the "express" VS "standard" SQL databases list from that instance you'll need to run a query like below:

SELECT name, collation_name FROM sys.databases where collation_name like '%Compatibility_52_409_30003%'
SELECT name, collation_name FROM sys.databases where collation_name NOT like '%Compatibility_52_409_30003%'


Not sure though what you need exactly when you say that "... not sure how to do the connect" - you mean to connect to that SQL instance via Powershell?  To put that in perspective the script below provides easy variables to add server name, db name and run the query above to retrieve list of all "Express" databases from that instance:


$SQLServer = "YourSQLServer" #use Server\Instance for named instances
$SQLDBName = "YourDBName"
$SqlQuery = "SELECT name, collation_name FROM sys.databases where collation_name like '%Compatibility_52_409_30003%'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" #you can use SQL login as well here

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

clear
$DataSet.Tables[0]




To connect with SQL login remove Integrated Security above and use a line like below:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = false; User ID = $uid; Password = $pwd;"
0
 
LVL 13

Author Comment

by:Dustin Saunders
ID: 41761114
Thanks for the response, I don't have an issue getting the info from SQL- what I intended to ask was how to do it through a remote PS Session?  The target server is on a different domain and I need to use Windows credentials to authenticate.  For example, in SSMS I have to open it with:

runas /netonly /user:users\dsaunders "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Open in new window


Is there a way with powershell I can use windows credentials for another domain to authenticate like I do here with SsMS?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 41761140
Suggest you check out http://sqlbak.com
0
 
LVL 13

Accepted Solution

by:
Dustin Saunders earned 0 total points
ID: 41761172
Thanks Aaron, but this is for a onetime migration of approximately 400 companies and 1200 databases to a new domain/sql server.  I merely need to connect to each instance, backup the files once, then I'll be moving them to the new domain share and restoring from there.

I think the way I'm going to go is to write a powershell script on the remote server that does the backup, then execute that via Invoke-Command to perform the backups.  Then when it's done, I'll have the first script check for the backup's existence and then move it.
0
 
LVL 13

Author Closing Comment

by:Dustin Saunders
ID: 41766627
Decided this would be the best approach to tackle considering both remote and on different domain.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question