Marcus Hall

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== I can't connect to my servers SQL database via an IP Address ======

From: https://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address

<code>
open SQL Server Configuration Manager;
switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
double-click the TCP/IP protocol;
select the Yes value in the Enabled field;
switch to the IP Addresses tab; 
</code>

====== Backup with powershell ======
<code>
ps1 script
Backup-SqlDatabase -ServerInstance Machine096\SSERVICES -Database IServices -BackupAction Database -BackupFile "F:\MSSQL14.SSERVICES\MSSQL\Backup\Backup_Machine\InspectionServices.bak"
Exit


Cmd file
powershell.exe -executionpolicy bypass 
               -file "C:\Users\Public\Downloads\SQLInspectionServicesBackup Script\BackupInspectionServices.ps1"
</code>



From: https://dba.stackexchange.com/questions/154642/sql-server-backup-powershell-vs-python

<code>
I am not familiar with Python, but for your current requirement (i.e. backup). I'd strongly 
recommend you to use PowerShell for two reasons:

MS in its SQLPS module (since SQL Server 2012), has a native cmdlet called

Backup-SQLDatabase

You may run this cmdlet on one central server to do the backup against multiple sql instances 
at once because this Backup-SQLDatabase can accept multiple sql instances, like the following example

Backup-SQLDatabase -Database master -Server "my_server1", "my_server2" ...
This 2nd point may help you to centralize all those backup scripts to one administration 
server (dedicated for DBA use) instead of sparsely stored on each sql server instance.

Also with MS providing Backup-SQLDatabase, you can be assured that it will be supported for 
all future new features. (For example, using this cmdlet to support Azure SQL Database etc.)
</code>

====== restoring SQL backup files to new filepaths ======

From: http://www.midnightdba.com/Jen/2010/09/tip-restore-with-filelistonly-with-move/

<code>
Use RESTORE FILELISTONLY to get the logical names of the data files in the backup. 
This is especially useful when you’re working with an unfamiliar backup file.
Example:
   RESTORE FILELISTONLY
   FROM DISK = '\\srv1\sql\ImportantDB.bak'

Use RESTORE WITH MOVE to move and/or rename database files to a new path.
Example:
   RESTORE DATABASE ImportantDB
   FROM DISK = '\\srv1\sql\ImportantDB.bak’
   WITH MOVE ‘ImportantDB’ TO ‘F:\SQL\ImportantDB.mdf’,
   MOVE ‘ImportantDB_log’ TO ‘G:\SQL\ImportantDB_log.LDF’
</code>


====== sqlcmd - Backup and Restore Your SQL Server Database from the Command Line ======

From: https://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/

Also: https://docs.microsoft.com/en-us/sql/relational-databases/scripting/sqlcmd-use-the-utility

<code>

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”


</code>

====== How do I run a ps1 elevated as a scheduled job ======

From: https://social.technet.microsoft.com/Forums/ie/en-US/97a24d7e-6e9e-4eb0-932e-b9d2e7ddf195/how-do-i-run-a-ps1-elevated-as-a-scheduled-job?forum=winserverpowershell

VeryGood: https://community.spiceworks.com/how_to/17736-run-powershell-scripts-from-task-scheduler


<code>
powershell.exe -executionpolicy bypass -file "Set-PowerShellScript.ps1"

On the system that the task will be run from, open the Windows Task Scheduler. This can be found 
in the Start menu, under Start > Administrative Tools.
In the Task Scheduler, select the Create Task option under the Actions heading on the right-hand 
side.
Enter a name for the task, and give it a description (the description is optional and not required).
In the General tab, go to the Security options heading and specify the user account that the task 
should be run under. Change the settings so the task will run if the user is logged in or not.
Next, select the Triggers tab, and click New to add a new trigger for the scheduled task. This new 
task should use the On a schedule option. The start date can be set to a desired time, and the 
frequency and duration of the task can be set based on your specific needs. Click OK when your 
desired settings are entered.
Next, go to the Actions tab and click New to set the action for this task to run. Set the Action 
to Start a program.
In the Program/script box enter "PowerShell."In the Add arguments (optional) box enter the value ".
Then, in the Start in (optional) box, add the location of the folder that contains your PowerShell 
script. In this example, the script is in a folder called "Script" that is off the root C: drive.  
Note: The location used in the Start in box will also be used for storing the scheduled task run times, 
the job history for the copies, and any additional logging that may occur. Click OK when all the desired 
settings are made.
Next, set any other desired settings in the Conditions and Settings tabs. You can also set up additional 
actions, such as emailing an Administrator each time the script is run.
Once all the desired actions have been made (or added), click OK. The task will be immediately set, and is 
ready to run.
</code>

====== Test remote SQL connectivity EASILY! ======

From: https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/

<code>
Just go anywhere on your system and create an empty text file named anything but instead of txt 
make sure the extension is UDL.  Then, double-click on the file and up pops a SQL connectivity 
window to allow testing of remote SQL connections.
</code>


From: http://www.diaryofaninja.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed


====== Tips and Tricks to fix SQL Connectivity Issues ======


From: https://blogs.msdn.microsoft.com/docast/2014/10/24/tips-and-tricks-to-fix-sql-connectivity-issues/


<code>
Explained a few common scenarios;
Approached these connectivity issues; and
Included fixes for these issues.
</code>


====== Hiding and Showing a SQL Instance on the Network ======

From: http://www.jameswiseman.com/blog/2012/02/22/hiding-and-showing-a-sql-instance-network/

<code>
SQL Server Properties
In Management Studio, click on the server root, and select properties.
From the resulting dialog, select the ‘Connections’ page.
Ensure the checkbox “Allow remote connections to this server” is checked.
SQL Server Configuration
Run SQL Server Configuration Manager (located in within the SQL shortcuts under the windows start menu)
Navigate to “SQL Server Network Configuration” and then “Protocols for SQL2008R2”
Ensure TCP/IP is “Enabled”
Right-click on “Protocols for SQL2008R2” and select “Properties”
Ensure “Hide Instance” is set to “no”
 SQL Browser Service
Navigate to the Service Control Manager through control panel (or run “Services.msc”)
Ensure the “SQL Server Browser” service is started. (It may be best to have the Startup Type as “Automatic”
</code>

====== Backup Overview (SQL Server) ======

From: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server


<code>
A. Full local backup
The following example creates a full database backup of the MyDB database to the default backup 
location of the server instance Computer\Instance. Optionally, this example specifies -BackupAction 
Database.
PowerShell

Copy
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  
</code>

From: https://msdn.microsoft.com/en-us/library/ms175477.aspx


<code>
This topic introduces the SQL Server backup component. Backing up your SQL Server database is 
essential for protecting your data. This discussion covers backup types, and backup restrictions. 
The topic also introduces SQL Server backup devices and backup media.
</code>

====== SQLBackupandFTP ======

From: https://sqlbackupandftp.com/features

<code>
Run Differential and Transaction log backup
Full backups are available in all editions
</code>