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
Solved

Best way to back up SQL server on remote server.

Posted on 2016-08-17
5
35 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
  • 3
5 Comments
 
LVL 39

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 12

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 12

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 12

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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