Solved

Best way to back up SQL server on remote server.

Posted on 2016-08-17
5
38 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 500 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

691 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