Solved

Best way to back up SQL server on remote server.

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now