#sqlcmd

[doesnetflixcostthesameusingvpn]

can t connect to sql server through vpn

🔒🌍✨ Get 3 Months FREE VPN - Secure & Private Internet Access Worldwide! Click Here ✨🌍🔒

can t connect to sql server through vpn

Troubleshooting SQL server connection issues

Troubleshooting SQL Server Connection Issues

Encountering connectivity problems with your SQL Server can be frustrating, especially when it disrupts critical operations. Whether you're a seasoned database administrator or a novice user, understanding common connection issues and how to troubleshoot them is essential for maintaining smooth database functionality.

One of the primary issues users face is the inability to establish a connection to the SQL Server. This could be due to various reasons such as network issues, firewall restrictions, or incorrect server configurations. Start by verifying the network connectivity between the client and the server. Ensure that the server is reachable from the client machine and that there are no network interruptions or DNS resolution problems.

Firewall settings can also block SQL Server connections. Check the firewall rules to ensure that the necessary ports (usually 1433 for default instances) are open for inbound connections. Additionally, consider any antivirus software that might be blocking SQL Server traffic and adjust the settings accordingly.

Another common culprit is incorrect server configurations. Double-check the server name, instance name (if applicable), and authentication method used in the connection string. Ensure that the SQL Server Browser service is running if connecting to a named instance.

If you're still unable to establish a connection, consider checking the SQL Server error logs for any relevant error messages. These logs can provide valuable insights into what might be causing the connection failures.

In some cases, the issue may lie with the client-side configuration. Ensure that the SQL Server Native Client or ODBC driver is installed and up-to-date on the client machine. Test the connection using tools like SQL Server Management Studio or the sqlcmd utility to isolate client-specific issues.

By systematically troubleshooting these common connection issues, you can quickly identify and resolve SQL Server connectivity problems, ensuring smooth database operations and minimizing downtime. Remember to document any changes made during the troubleshooting process for future reference.

VPN configuration for SQL server access

In the modern digital age, cybersecurity is of utmost importance to safeguard sensitive information. When it comes to accessing SQL servers remotely, configuring a Virtual Private Network (VPN) can greatly enhance the security of the connection.

A VPN creates a secure encrypted tunnel between the user and the server, ensuring that all data transmitted is protected from potential threats like hackers or snoopers. By utilizing a VPN for SQL server access, businesses can ensure that their valuable data remains confidential and safe from unauthorized access.

Setting up a VPN for SQL server access involves a few key steps. Firstly, you need to choose a reliable VPN service provider that supports secure protocols like OpenVPN or IPsec. Next, configure the VPN client on the user's device and connect to the VPN server using the provided credentials.

Once the VPN connection is established, users can securely access the SQL server as if they were physically present in the same location. This not only provides a layer of security for the data being transmitted but also allows for seamless remote work capabilities.

Overall, configuring a VPN for SQL server access is a proactive measure to bolster cybersecurity defenses and protect sensitive information from potential security breaches. By implementing this extra layer of protection, businesses can operate with peace of mind knowing that their data is secure and inaccessible to malicious entities.

Secure access to SQL server over VPN

When it comes to securing access to a SQL server, utilizing a Virtual Private Network (VPN) can be a highly effective solution. A VPN creates a secure and encrypted connection between the user and the SQL server, ensuring that data transmitted between the two points is protected from unauthorized access or interception.

One of the primary benefits of using a VPN for accessing a SQL server is the added layer of security it provides. By encrypting the connection, VPNs help to prevent unauthorized parties from intercepting and viewing sensitive data transmitted between the user and the server. This is especially important when dealing with sensitive information stored in a SQL database, such as customer data, financial records, or proprietary business information.

Additionally, using a VPN to access a SQL server allows for secure remote access. This means that authorized users can safely connect to the server from virtually anywhere, without compromising the security of the data being transmitted. This is particularly useful for organizations with remote employees or multiple offices that need to access the SQL server securely.

Overall, implementing a VPN for secure access to a SQL server is a proactive and effective measure to protect sensitive data and ensure the confidentiality and integrity of information stored in the database. By utilizing a VPN, organizations can enhance their cybersecurity posture and minimize the risk of data breaches or unauthorized access to their SQL server.

Resolving connection problems to SQL server via VPN

When encountering connection problems to a SQL server via VPN, it can be a frustrating experience for users and administrators alike. The ability to securely access the SQL server remotely is essential for many organizations, and any disruptions to this connection can impact productivity and overall operations.

One common issue that may arise when connecting to a SQL server via VPN is network configuration errors. It is crucial to ensure that the VPN settings are correctly configured to allow access to the SQL server. This includes verifying that the VPN connection is stable and that the necessary ports are open to establish a connection.

Another potential cause of connection problems could be firewall settings blocking the communication between the client machine and the SQL server. In such cases, adjusting the firewall settings to allow the SQL server traffic through can help resolve the issue.

Furthermore, issues with the VPN client software or outdated drivers can also lead to connection problems. Keeping the VPN client up to date and ensuring that the network drivers are functioning correctly is crucial for establishing a stable connection to the SQL server.

Additionally, network latency or bandwidth limitations can impact the VPN connection to the SQL server. Monitoring the network performance and addressing any bottlenecks can help improve the connection quality and stability.

In conclusion, resolving connection problems to a SQL server via VPN requires a systematic approach to troubleshooting network, firewall, and software issues. By identifying and addressing the root cause of the problem, users can ensure a reliable and secure connection to the SQL server for seamless remote access.

Network settings for connecting to SQL server through VPN

Title: Essential Network Settings for Connecting to SQL Server Through VPN

In today's interconnected digital landscape, ensuring secure and seamless access to SQL Server databases is paramount for businesses relying on critical data management. With the increasing adoption of remote work and the necessity of safeguarding sensitive information, configuring network settings for connecting to SQL Server through Virtual Private Networks (VPN) has become a fundamental practice.

To establish a robust connection to SQL Server via VPN, meticulous attention to network configurations is indispensable. Here are the key steps to optimize network settings for seamless SQL Server access:

VPN Configuration: Begin by configuring the VPN client on the accessing device. Ensure that the VPN client is compatible with the operating system and configured to establish a secure connection to the corporate network where the SQL Server resides.

Firewall Settings: Adjust firewall settings to permit VPN traffic and SQL Server communication. Open the necessary ports for SQL Server (typically TCP port 1433 for default instances) within the corporate firewall to facilitate data transmission between the client and server.

Network Address Translation (NAT): If the SQL Server is behind a NAT device, configure port forwarding or VPN passthrough to allow inbound traffic to reach the server. Verify that NAT traversal mechanisms are enabled to seamlessly navigate network address translation challenges.

VPN Tunneling Protocols: Select VPN tunneling protocols that prioritize security and performance. Common protocols such as OpenVPN, IPsec, and SSL/TLS offer varying levels of encryption and authentication, so choose the protocol that aligns with your organization's security policies.

DNS Resolution: Ensure proper DNS resolution for SQL Server hostname resolution over the VPN connection. Configure DNS servers to resolve internal hostnames, enabling clients to connect to SQL Server instances using domain names rather than IP addresses.

By meticulously configuring network settings in conjunction with VPN connections, organizations can fortify the integrity and accessibility of their SQL Server infrastructure. This proactive approach not only enhances data security but also fosters productivity by enabling remote access to critical databases with minimal disruption.

[brassaikao]

定期將遠端SQL SERVER資料庫備份還原到本地端SQL SERVER

以下為透過Windows網路芳鄰實現自動化異機備份及還原的方案。(網際網路的情境可搭配VPN實現) 1. 在遠端設定排程以自動化備份資料庫到固定位置，這裡可透過維護計畫或是批次檔搭配工作排程器來實現。2. 參考以下批次檔指令，將遠端的備份檔案複製到本地端，再還原到SQL SERVER。 net use \\{SOURCE_SERVER_IP} /user:{SOURCE_SERVER_ACCOUNT} {SOURCE_SERVER_PASSWORD}copy /y "\\{DBBACKUP_FILE_FULL_NETWORK_SOURCE_PATH}" "{DBBACKUP_FILE_FULL_LOCAL_TARGET_PATH}"SqlCmd -U {SQL_SERVER_ACCOUNT} -P {SQL_SERVER_PASSWORD} -Q "ALTER DATABASE…

[sqlcrespi]

Quick Tip 12 - Como Instalar o SQLCMD no MacOS

Quick Tip 12 – Como Instalar o SQLCMD no MacOS

Olá PessoALL! O SQLCMD é instalado através do gerenciador de pacotes HomeBrew,.Se você não tem instalado veja o link: https://brew.sh/  Atualizada e Instala – #Registra o caminho do GitHub #Atualiza a biblioteca local e instala brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update brew install mssql-tools Sabemos que na maioria dos casos, ao iniciar…

[rafaelsantiagoc]

Base de datos

[hiramfleitas]

SQLCMD variables for input SQL file

SQLCMD variables for input SQL file

In short, this is an example of how you do it. Set a couple of variables, use -v parameter sqlcmdVariable=”%batVariable%”.

The following contains more details on how to use SQLCMD variables for an input SQL file. Note, I am not advising you to you use this as your routine backup method. If you’re looking for a backup process, there are much better methods.

0.Run.bat

REM cmd /K "cd /d…

[webmundi]

Instalação do SQL Server no Linux

Neste Post, apresentaremos uma vídeo aula sobre como efetuar a instalação do SQL Server 2017 no Oracle Linux.

http://www.webmundi.com/banco-de-dados/ms-sql-server/instalacao-do-sql-server-no-linux/

[seniordba]

SQL Server TCP and UDP Ports

SQL Server TCP and UDP Ports

SQL Server is a complex application that can be difficult to allow access to through a firewall. The subsystems and features that Microsoft has added—and continues to add—to SQL Server have significantly increased the network connections that the platform uses. Sometime it’s difficult to figure out which firewall ports to open for each SQL Server feature. To help you, here’s a rundown of…

[theolivetress]

Rè-indexer la base de données de WSUS

Rè-indexer la base de données de WSUS

Selon Microsoft, seuls les grandes infra WSUS ont des performances qui se dégradent avec le temps. En réalité, quelque soit la taille de l’infra, les performances de Windows Server Update Services (WSUS) se détérioreront avec le temps si la base de données WSUS n’est pas correctement maintenue. Heureusement, la Microsoft WSUS team a pondu un script…

(more…)

[prashantjayaram]

SQL Server DevOps Tools

SQL Server DevOps Tools

This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations. In this article, I will explore various SQL tools. I will…

[wwwtandemlabtech]

Access locked out SQL instances, i.e. sa account or windows/sql logins with sysadmin passwords are lost or disabled

David Lister from SQL Services ...   Info on Breaking into SQL ;)  PSExec tool gives you an option to run a remote process or an application using System account, if SYSTEM account has permission on that application. Although there are many more features that are  For example, if SQL Server has NT AUTHORITYSYSTEM as one of its logins, then you can use this account to login to SQL Server. However, you can’t use SQL Server Management Studio or sqlcmd to login to SQL Server using NT AUTHORITYSYSTEM. Try it yourself! However, PSExec gives you an opportunity to login to SQL Server using NT AUTHORITYSYSTEM. Once you download PSExec tool bundle command prompt and type following command: D:PSTools>psexec -i -s "C:Program Files (x86)Microsoft SQL Server100ToolsBinnVSShellCommon7IDESsms.exe" Here, –i allow the program to run so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session. -s runs the process in SYSTEM account. The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITYSYSTEM. If NT AUTHORITYSYSTEM has a login authority to the server, you will be able to login to the SQL Server. If you do not use “–i” switch, you won’t be able to launch management studio! So, make sure you use –i and –s both! However, if there is a deny to this login, for example because of Trigger etc., then you won’t be able to login to SQL Server using this technique.

[betapiner]

Sqlectron install ubuntu

#Sqlectron install ubuntu how to#

#Sqlectron install ubuntu install#

#Sqlectron install ubuntu full#

#Sqlectron install ubuntu Offline#

Docker, Docker Compose: Faster build up the working and development environment.

Software & Developmentĭepends on the project or use cases I work on, it may have different tools/software needed however, the most common and must-have for every desktop for my development software/app/tools are as follow:

#Sqlectron install ubuntu install#

The easiest way to install the Dbeaver Database administration tool on Ubuntu is by using. I may spend some time writing my following review about the Fedora for development. Tutorial Install DBeaver pada Windows, SQL Client Ramah Pe. Linux is focused on most other development needed, such as API, web, software, or IoT.Īnd you may ask what distribution of Linux, the Dell XPS 13 because it's a developer edition, so it pre-install as the Ubuntu 20.04.įor the Huawei Matebook was Deepin, and for One Netbook 4, I recently installed the Fedora 34, which I like this Fedora distribution, simple and effective. MacOS, for me, is primarily for iOS / mobile app development. If you’d like to share some practical tips from experience, feel free to add them in the Comments section below.As you can see from the hardware part, there is 2 OS I majority use: macOS and Linux. We hope that this article will help you in your work.

#Sqlectron install ubuntu Offline#

This package also has a dependency on the unixodbc-dev package for Ubuntu.Ĭopy all these downloaded packages to the target Ubuntu server and then run the below commands to install these packages: -Run below commands to install SQL Server Tools in offline mode Make sure to download and copy the dependency packages – the msodbcsql package, which is a dependency. If your Linux system is not connected to the Internet or does not have access to online repositories, you need to include more steps.įirst, download the package from the Microsoft repository and copy that package to the target server. The approach we explored in this article is the online installation where your Ubuntu system is connected to the Internet. We can run queries and database activities. Now, we have successfully installed SQL Server Tools.

#Sqlectron install ubuntu full#

That is because we have not passed the full command line. Now, after executing the above two commands, Ubuntu has recognized SQLCMD.Īlthough, SQLCMD is still throwing an error. Run below command to use SQL Server Tools from the bash shell for interactive/non-login sessionsĮcho 'export PATH="$PATH:/opt/mssql-tools/bin"' > ~/.bashrc Modify the PATH in the ~/.bash_profile fileĮcho 'export PATH="$PATH:/opt/mssql-tools/bin"' > ~/.bash_profile To fix this issue, we’ll add /opt/mssql-tools/bin/ to our PATH environment variable in a bash shell by running the below commands: -Run below command to use SQL Server Tools from the bash shell for login sessions In our case, the Ubuntu system did not recognize SQLCMD. The thing is, we have already installed it as a part of mssql-tool package – SQL Server tools. Type SQLCMD to validate if Ubuntu can recognize this new utility. Configure the Path Environment Variables in a Bash Shell They are very helpful for establishing the SQL Server connection or executing any query locally on the Linux server. The role of these SQL Server tools is crucial when if it comes to Linux-based SQL Server instances.

BCP is one of the SQL Server utilities that serve to process the import and export of data.

SQLCMD is a popular command-line utility that we use to establish SQL Server connections to execute queries.

These tools include sqlcmd and bcp utilities: If you want to connect to SQL Server instances locally on the Ubuntu Linux server, we first need to install the SQL Server command-line tools. Thus, I have already installed SQL Server 2019 on Ubuntu 18.04 and shown its connectivity through SQL Server Management Studio from a remote server.

#Sqlectron install ubuntu how to#

How to install SQL Server 2019 on Ubuntu 18.04 Server.How to create a virtual machine and install Ubuntu 18.04 Linux server.Install SQL Server Tools on Ubuntu Linux Systemīefore we proceed to explore the current topic, I would suggest you refer to the previous parts of the to refresh your knowledge: Our goal is to demonstrate the step-by-step process of installing SQL Server Tools and its components on the Ubuntu 18.04 Linux system. In the previous articles, we covered sequential steps necessary to build a virtual machine, install Ubuntu 18.04 operating system, and configure SQL Server 2019 on that Ubuntu machine.

[brassaikao]

Aras Innovator - Method臨時提升權限及使用時機

Aras Innovator – Method臨時提升權限及使用時機

當Method需要用到SQL指令時，需透過臨時提升權限的方式來執行。而其他情境則幾乎不適用！即使臨時提升權限，仍無法以目標Identity的權限對ItemType做異動。 Aras.Server.Security.Identity plmIdentity = Aras.Server.Security.Identity.GetByName(“Innovator Admin”);bool PermissionWasSet = Aras.Server.Security.Permissions.GrantIdentity(plmIdentity);Innovator inno = this.getInnovator(); string sqlCmd = “UPDATE Document SET ineffective_date=GETUTCDATE() WHERE…

[computingpostcom]

Welcome to this guide on how to install and use Azure Data Studio on Rocky Linux 8|AlmaLinux 8. Azure Data Studio initially known as SQL Azure, is a professional and cross-platform database tool using on-premises and cloud data platforms. This tool offers the best and most modern editor experience with code snippets, integrated terminal, source control integration e.t.c. It also has an in-built chart of query result sets and customizable dashboards. With Azure Data Studio, you can easily query, design, and manage your databases on local and cloud platforms. The features associated with Azure Data Studio are well elaborated below. Customizable Server and Database Dashboards: It allows one to get rich customizable dashboards that help monitor and troubleshoot performance issues. Connection management: the server groups provide the easiest way to manage connection information for a given server and databases Integrated Terminal: this allows one to use tools such as PowerShell, Bash, bcp, sqlcmd and ssh within the Azure Data Studio. Extensibility and extension authoring: Azure Data Studio functionality can be enhanced through extensions. Smart SQL code snippets: Codes with the proper syntax to create databases, views, tables, stored procedures, logins, users, and roles are generated with the SQL code snippets. SQL code editor with IntelliSense: It offers the most modern editor with keyword completion, source control integration, code navigation, multiple tab windows e.t.c Let’s dive in! System Requirements: For this setup, you need a system with the following specifications: Memory – 4GB (recommended 8GB) CPU cores – 2 (recommended 4) #1. Download Azure Data Studio The latest general availability(GA) version 1.35.1 for Azure Data Studio was released on March 17, 2022. There are two methods one can use to install the Azure Data Studio on Rocky Linux 8|AlmaLinux 8. These are: Using an RPM file Using Linux tar.gz file This guide demonstrates how to install Azure Data Studio using both RPM and TAR.GZ files. Download the preferred file for installation as below: ##For RPM wget https://go.microsoft.com/fwlink/?linkid=2193238 -O azuredatastudio.rpm ##FOR TAR.GZ wget https://go.microsoft.com/fwlink/?linkid=2193237 -O azuredatastudio.tar.gz The latest insider version build from the main can be downloaded by clicking on the below link: Linux TAR.GZ(insiders build) Once the preferred file is downloaded, proceed as below. #2. Install Azure Data Studio on Rocky Linux 8|AlmaLinux 8 Based on the downloaded file, proceed with the appropriate installation option. Option 1 – Install Azure Data Studio using RPM file With the RPM file downloaded, install Azure Data Studio on Rocky Linux 8|AlmaLinux 8 using the command: sudo yum install azuredatastudio.rpm Sample Output: Dependencies resolved. ======================================= Package Arch Version Repository Size ======================================= Installing: azuredatastudio x86_64 1.36.1-1650577003.el7 @commandline 172 M Installing dependencies: libXScrnSaver x86_64 1.2.3-1.el8 appstream 30 k Transaction Summary ======================================= Install 2 Packages Total size: 172 M Total download size: 30 k Installed size: 513 M Is this ok [y/N]: y Option 2 – Install Azure Data Studio using TAR.GZ file Begin by installing the required tool: sudo yum install libXScrnSaver Extract the downloaded .tar.gz file to /opt: tar -xvf azuredatastudio-linux-*.tar.gz sudo mv azuredatastudio-linux-x64 /opt/azuredatastudio Once extracted, export the PATH variable as below: $ sudo vi /etc/profile export PATH="$PATH:/opt/azuredatastudio" Source the profile: source /etc/profile #3. Use Azure Data Studio on Rocky Linux 8|AlmaLinux 8 Once installed with any of the methods above, launch Azure Data Studio using the command:

azuredatastudio You can as well launch it from the App menu The Azure Data Studio will start as below. Once launched, connect to an SQL Server. I assume you already have one installed, otherwise use the guides below to install the Microsoft SQL Server on your system. Install Microsoft SQL Server on CentOS / RHEL/Rocky Linux/Alma Linux How to install MS SQL on Ubuntu How to install Microsoft SQL Server on Fedora To connect to a database, click on “new connection“. Proceed and provide the SQL Server details as below: On successful authentication, you should see the below page. Create a database Create a database by right-clicking on the server and selecting New query Enter the below code and click run USE master GO IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N'TestDB' ) CREATE DATABASE [TestDB]; GO IF SERVERPROPERTY('ProductVersion') > '12' ALTER DATABASE [TestDB] SET QUERY_STORE=ON; GO Once the code runs successfully, you will have the database appear after refreshing databases. Create a table To the connected database(master), we will create a table in TestDB. Switch to the created TestDB context. Now add the below code and run it to create a table: -- Create a new table called 'Customers' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers; GO -- Create the table in the specified schema CREATE TABLE dbo.Customers ( CustomerId int NOT NULL PRIMARY KEY, -- primary key column Name nvarchar(50) NOT NULL, Location nvarchar(50) NOT NULL, Email nvarchar(50) NOT NULL ); GO After this, right-click on tables and refresh. You will have the table created in TestDB. Insert rows To insert rows in the created table, use the below query: -- Insert rows into table 'Customers' INSERT INTO dbo.Customers ([CustomerId], [Name], [Location], [Email]) VALUES ( 1, N'Orlando', N'Australia', N''), ( 2, N'Keith', N'India', N'keith0@adventure-works.com'), ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'), ( 4, N'Janet', N'United States', N'janet1@adventure-works.com') GO Run the query. View the data returned by a query Run the following query in the query window. -- Select rows from table 'Customers' SELECT * FROM dbo.Customers; Sample Output: Voila! We have triumphantly walked through how to install and use the Azure Data Studio on Rocky Linux 8|AlmaLinux 8. I hope this was helpful.

[easyinplay]

SQL SERVER 数据库备份到网络硬盘上

定期备份SQL数据库是必须的。 我们用物理备份的方式就可以轻松备份您的所有SQL Server数据库到本地硬盘中，但这并不能防止驱动器和/或系统故障。 作为针对此类灾难的额外保护层，您可以在网络共享上复制或直接创建备份。

在本地备份，然后复制到网络共享

完成此任务的首选和最直接的方法是简单地创建数据库的本地备份，然后将相应的备份文件复制到网络共享。您可以通过创建如下所示的批处理脚本来完成此操作：

SET LocalFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup' SqlCmd -E -Q“备份数据库testDB到磁盘=％LocalFolder％testDB.bak�� XCopy“％LocalFolder％testDB.bak”“\\192.168.0.191\share”/ Z / V DEL“％LocalFolder％testDB.bak”

此脚本执行以下操作（逐行）：

将变量设置为本地SQL备份目录。 创建testDB的SQL备份（使用Windows身份验证）到本地SQL备份目录。 将本地备份文件复制到网络共享。 删除本地备份文件。

同样，这是首选方法，因为它的工作原理和备份失败的可能性是最小的，因为备份是在本地磁盘上创建。 但是，如果没有足够的磁盘空间来存储备份文件的本地副本，则此操作将失败。 在这种情况下，您需要添加额外的磁盘空间或直接备份到网络共享。

直接备份到网络共享 通常，当您尝试使用以下命令直接创建备份到网络共享时：

SqlCmd -E -Q“备份数据库testDB到磁盘=\\ 192.168.0.191\share\testDB.bak”

你很可能会得到一个错误的行：

消息3201，级别16，状态1，服务器JF，行1 无法打开备份设备\\192.168.0.191\share\testDB.bak。 操作系统错误5（访问被拒绝。）。 消息3013，级别16，状态1，服务器JF，第1行 BACKUP DATABASE异常终止。

尽管使用Windows身份验证（-E开关）和Windows帐户作为通过Windows资源管理器访问和复制文件到共享的能力运行SQL备份命令，仍会出现此错误。

此操作失败的原因是SQL命令在SQL Server服务运行的帐户的边界内执行。 当您在计算机上查看服务列表时，很可能您将看到运行为（登录为）列的SQL Server服务本地系统或网络服务，它们是没有网络访问权限的系统帐户。

在我们的系统上，网络共享命令的备份失败，因为我们有SQL Server服务作为本地系统运行，再也无法获得任何网络资源。

下面说一下相关的解决方法：

首先，如果没有启用xp_cmdshell,请执行以下启用：

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

1.创建映射：

exec master..xp_cmdshell 'net use \\192.168.0.191\Share 123456 /user:192.168.9.140\administrator'

2.备份数据库：

backup database testDB to disk='\\192.168.0.191\Share\testDB.bak' with init

3.删除备份：

exec master..xp_cmdshell 'net use \\192.168.0.191\Share /delete'

下面提供一个通用的存储过程方便大家调用：

CREATE PROC [dbo].[sp_sys_database_backup_netpath] @SavePath NVARCHAR(150), --@SavePath =>保存的网络地址目录 @IP NVARCHAR(20), --@IP =>IP地址 @UserName NVARCHAR(30), --@UserName =>访问网络地址的用户名 @Password NVARCHAR(20), --@Password =>访问网络地址的密码 @DBName NVARCHAR(50), --@DBName =>需要备份的数据库名字 @SaveFileName NVARCHAR(150) --@SaveFileName =>保存备份文件的名字 WITH ENCRYPTION AS BEGIN DECLARE @TempInfo NVARCHAR(500); SELECT @TempInfo = N'net use z: ' + @SavePath + N' "' + @Password + N'" /user:' + @IP + N'\' + @UserName; EXEC master..xp_cmdshell @TempInfo; SELECT @TempInfo = N'C:\' + @SaveFileName; BACKUP DATABASE @DBName TO DISK = @TempInfo WITH INIT; SELECT @TempInfo = N'copy C:\' + @SaveFileName + N' z:'; EXEC master..xp_cmdshell @TempInfo; EXEC master..xp_cmdshell 'net use z: /delete'; SELECT @TempInfo = N'del C:\' + @SaveFileName; EXEC master..xp_cmdshell @TempInfo; END;

[enterinit]

SQL Server check version with PowerShell

SQL Server check version with PowerShell. NOTE: Better do this from the Windows Server running Microsoft SQL Server 1. Run Windows PowerShell with elevated permissions (as administrator). As an example, you can do this by Right Mouse Button click at Start button and select WindowsPowerShell (Admin); 2. Run next command: sqlcmd -s \ NOTE: If you use only 1 instance at the server you can use next command sqlcmd -s As example: sqlcmd -s 21-cm01 3. At the 1> prompt, type: select @@version 4. At the 2> prompt, type: go As a result, you must get something like this. (With your server name and SQL Server version)

Read the full article

[msgates]

Disney signs on with Microsoft, SQLCMD arrives in Data Studio and Azure goes German Click here for articles September 17, 2019 at 06:00PM