[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Best way to back up SQL server on remote server.

Posted on 2016-08-17
5
Medium Priority
?
48 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 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 14

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 14

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 14

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

834 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