Command Line Management SQL Server

Release a project for the customer, which is based on the SQL Server database. It may be difficult to install the SQL server during deployment, and it is not a genuine version. I started to want to install an MSDE. Later I thought it only supported 2000, so I decided to install SQL server2005 Express. After downloading it back to Microsoft, I got more than 50 m, but it was not long enough. Then I installed it on the Virtual Machine and found that the additional database was a problem. So I created a udl document, test on the local machine and attach it smoothly. However, if the virtual machine fails to survive or die, it is reported that the file cannot be found. No way, this risk cannot be taken. In case the customer does not, it is not good, therefore, you only need to find the command line method,

It is quite simple. After installing SQL Server, SQL Server will add the directory of the sqlcmd.exe file to the environment variable in the system environment variable. Therefore, we can easily use the command line to manage the database:

Sqlcmd-s localhost-U sa-P mypwd-D master

This enters the master database. Here, you can write the create database statement, or start to execute the operations of splitting and attaching the database:

1. Separation: sp_detach_db dbweb, true
2. Add: sp_attach_db 'dbweb', 'c: \ test \ dbweb. MDF ', 'c: \ test \ dbweb_log.ldf'
Or
Create Database teach
On (filename = 'e: \ teach. MDF '),
(Filename = 'e: \ teach \ teach_log.ldf ')
For attach

The above are two additional methods. I use the first method.

3. Backup: sqlcmd-e-s myserver-q "backup database dbname to disk = 'd: \ Backups \ mydb. Bak '"

4. Restore: sqlcmd-e-s myserver-q "Restore database dbname from disk = 'd: \ Backups \ mydb. Bak '"

Note,-EThis option ignores possible user name and password environment variable settings, such as sql1_password. If-EOption and-UOption or-POption to generate an error message.

 

5. Change Password: osql-e sp_password null, 'abc123', 'sa'

At this time, the SA user's password is reset to ABC123

 

After the attachment is complete, the customer's SQL Server should not be taken care of, it should be simple, a 50-to-m SQL Server 2005 express installation still does not charge anything.

 

Note: The general statement for adding, deleting, modifying, and querying records must add "go" after the line feed.