Shulou(Shulou.com)05/31 Report--

How to use the CMD command to operate the MSSQL2005 database, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Open the CMD command to execute: sqlcmd/? This is some help for the sqlcmd command through which you can know how to connect to the database. Execute: sqlcmd-S server address-d database name-U user password-P password

The following is the command to manipulate the database. The copy code is as follows:

0. Create the database CREATE DATABASE DB001 1. Create user CREATE LOGIN USER1 WITH PASSWORD = 'USER_PW'; 2. Modify the owner of the data USE DB001 exec sp_changedbowner 'USER1' GO 3. Set READ_COMMITTED_SNAPSHOT ALTER DATABASE [DB001] SET READ_COMMITTED_SNAPSHOT ON GO 4. Modify the character set ALTER DATABASE DB001 COLLATE SQL_Latin1_General_CP437_CS_AS GO 5. Get all database names: Select Name FROM Master..SysDatabases 6. Get all table names: select name from sysobjects where type='U' XType='U': represents all user tables; XType='S': represents all system tables; 7. Get all field names: Select Name from SysColumns Where id=Object_Id ('TableName') 8. View views, stored procedures, and functions related to a table, select a. * from sysobjects a, syscomments b where a.id = b.id and b.text like'% TableName%' 9. Query the fields and data types of a table select column_name,data_type from information_schema.columns where table_name = NumberTable name'10. Get the database file path select ltrim (rtrim (filename)) from dbname.. sysfiles where charindex ('MDF',filename) > 0 or select ltrim (rtrim (filename)) from dbname.. sysfiles where charindex (' LDF',filename) > 0

MSSQL2005_ database backup statement-full backup Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Full_20070908.bak'-differential backup Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Diff_20070908.bak' With Differential-log backup, default truncation log Backup Log NorthwindCS To disk='G:\ Backup\ NorthwindCS_Log_20070908.bak'-log backup Do not truncate log Backup Log NorthwindCS To disk='G:\ Backup\ NorthwindCS_Log_20070908.bak' With No_Truncate-truncated log does not retain Backup Log NorthwindCS With No_Log-or Backup Log NorthwindCS With Truncate_Only-log file does not become smaller after truncation-it is necessary to shrink-file backup Exec Sp_Helpdb NorthwindCS-view data file Backup Database NorthwindCS File='NorthwindCS'-logical name of data file To Disk='G:\ Backup\ NorthwindCS_File_20070908.bak'-filegroup backup Exec Sp_Helpdb NorthwindCS-View data file Backup Database NorthwindCS FileGroup='Primary'-logical name of data file To disk='G:\ Backup\ NorthwindCS_FileGroup_20070908.bak' With init-split backup to multiple targets-No target Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Full_1.bak' is allowed to be lost during recovery Disk='G:\ Backup\ NorthwindCS_Full_2.bak'-- Mirror backup-- each destination is the same Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Mirror_1.bak' Mirror To disk='G:\ Backup\ NorthwindCS_Mirror_2.bak' With Format-- format the target when you do the first mirror backup-- Mirror backup to local and remote Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Mirror_1. Bak' Mirror To disk='\\ 192.168.1.200\ Backup\ NorthwindCS_Mirror_2.bak' With Format-A backup file Declare @ Path Nvarchar (2000) Set @ Path ='G:\ Backup\ NorthwindCS_Full_' + Convert (Nvarchar) is generated every day Getdate () + '.bak' Backup Database NorthwindCS To disk=@Path-- restore the database from NoRecovery or-- Standby mode to available Restore Database NorthwindCS_Bak With Recovery-- View the backup set in the target backup Restore HeaderOnly From Disk ='G:\ Backup\ NorthwindCS_Full_20070908.bak'-- View the information of the first backup set of the target backup Restore FileListOnly From Disk ='G:\ Backup\ NorthwindCS_Full_20070908_2.bak' With File=1-- View destination Volume label Restore LabelOnly From Disk ='G:\ Backup\ NorthwindCS_Full_20070908_2.bak'-backup settings password protection backup Backup Database NorthwindCS To disk='G:\ Backup\ NorthwindCS_Full_20070908.bak' With Password = '123' Init Restore Database NorthwindCS From disk='G:\ Backup\ NorthwindCS_Full_20070908.bak' With Password = '123'

After reading the above, have you mastered how to use the CMD command to operate the MSSQL2005 database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.