====== 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
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;
====== Backup with powershell ======
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"
From: https://dba.stackexchange.com/questions/154642/sql-server-backup-powershell-vs-python
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.)
====== restoring SQL backup files to new filepaths ======
From: http://www.midnightdba.com/Jen/2010/09/tip-restore-with-filelistonly-with-move/
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’
====== 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
sqlcmd -S \ -i -o
SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”
====== 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
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.
====== Test remote SQL connectivity EASILY! ======
From: https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/
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.
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/
Explained a few common scenarios;
Approached these connectivity issues; and
Included fixes for these issues.
====== 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/
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”
====== Backup Overview (SQL Server) ======
From: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server
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
From: https://msdn.microsoft.com/en-us/library/ms175477.aspx
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.
====== SQLBackupandFTP ======
From: https://sqlbackupandftp.com/features
Run Differential and Transaction log backup
Full backups are available in all editions