software:microsoft:sqlserver
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
software:microsoft:sqlserver [2016/09/20 19:32] – created 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:// | ||
+ | |||
+ | < | ||
+ | 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 " | ||
+ | Exit | ||
+ | |||
+ | |||
+ | Cmd file | ||
+ | powershell.exe -executionpolicy bypass | ||
+ | -file " | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | From: https:// | ||
+ | |||
+ | < | ||
+ | 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 " | ||
+ | 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, | ||
+ | all future new features. (For example, using this cmdlet to support Azure SQL Database etc.) | ||
+ | </ | ||
+ | |||
+ | ====== restoring SQL backup files to new filepaths ====== | ||
+ | |||
+ | From: http:// | ||
+ | |||
+ | < | ||
+ | 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: | ||
+ | | ||
+ | FROM DISK = ' | ||
+ | |||
+ | Use RESTORE WITH MOVE to move and/or rename database files to a new path. | ||
+ | Example: | ||
+ | | ||
+ | FROM DISK = ' | ||
+ | WITH MOVE ‘ImportantDB’ TO ‘F: | ||
+ | MOVE ‘ImportantDB_log’ TO ‘G: | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== sqlcmd - Backup and Restore Your SQL Server Database from the Command Line ====== | ||
+ | |||
+ | From: https:// | ||
+ | |||
+ | Also: https:// | ||
+ | |||
+ | < | ||
+ | |||
+ | sqlcmd -S < | ||
+ | |||
+ | SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X: | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | ====== How do I run a ps1 elevated as a scheduled job ====== | ||
+ | |||
+ | From: https:// | ||
+ | |||
+ | VeryGood: https:// | ||
+ | |||
+ | |||
+ | < | ||
+ | powershell.exe -executionpolicy bypass -file " | ||
+ | |||
+ | 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/ | ||
+ | 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 " | ||
+ | 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:// | ||
+ | |||
+ | < | ||
+ | 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:// | ||
+ | |||
+ | |||
+ | ====== Tips and Tricks to fix SQL Connectivity Issues ====== | ||
+ | |||
+ | |||
+ | From: https:// | ||
+ | |||
+ | |||
+ | < | ||
+ | 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:// | ||
+ | |||
+ | < | ||
+ | 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) ====== | ====== Backup Overview (SQL Server) ====== | ||
+ | |||
+ | From: https:// | ||
+ | |||
+ | |||
+ | < | ||
+ | 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:// | From: https:// | ||
Line 9: | Line 187: | ||
The topic also introduces SQL Server backup devices and backup media. | The topic also introduces SQL Server backup devices and backup media. | ||
</ | </ | ||
+ | |||
+ | ====== SQLBackupandFTP ====== | ||
+ | |||
+ | From: https:// | ||
+ | |||
+ | < | ||
+ | Run Differential and Transaction log backup | ||
+ | Full backups are available in all editions | ||
+ | </ | ||
+ |
software/microsoft/sqlserver.1474399957.txt.gz · Last modified: 2016/09/20 19:32 by superwizard