Differences

This shows you the differences between two versions of the page.

--- software:microsoft:sqlserver [2017/11/27 06:10] – [Backup Overview (SQL Server)] superwizard
+++ software:microsoft:sqlserver [2018/09/13 20:28] (current) – superwizard
@@ Line 1: / Line 1: @@
+====== 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! ======
@@ Line 53: / Line 170: @@
 <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.
+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
@@ Line 68: / Line 187: @@
 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>