SQL Server database bcp export backup file application example, serverbcp

SQL Server database bcp export backup file application example, serverbcp

/*** Authorize */EXEC sp_configure 'show advanced options', 1; goreconfigure; goexec sp_configure 'xp _ Role shell', 1; goreconfigure; go/** import the text file of the specified table */EXEC master .. xp_mongoshell 'bcp dbname .. tablename in d: \ DT.txt-c-Sservername-Usa-ppassword' exec master .. xp_mongoshell 'bcp "select * from dbname .. tablename "queryout" D: \ 20140528.xls"-c-Sservername-Uuser-ppassword'

Xp_cmdshell parameter description

The following is a stored procedure written by myself. You can use it directly.
Step 1: authorize first. There is an authorized SQL code

If exists (select * from sysobjects where type = 'p' and name = 'SP _ export_posm_data ') begindrop procedure sp_export_posm_data; end; gocreate procedure success @ file_path varchar (200) /* path of the exported file */asdeclare @ exec_ SQL varchar (1000); declare @ file_name varchar (200);/* file name, time format, it is mainly used to record when data is exported */declare @ table_name varchar (100);/* Name of the table to export data */declare @ SQL varchar (1000 ); /* SQL statement for executing business data query * // * name of the business table to back up data */declare cur_tables cursor forselect name from sysobjects where 1 = 1 and type = 'U' and name like 'wm _ ORDER % 'or name like 'wm _ PICKING %' or name like 'rp _ % 'begin tryopen cur_tables; fetch next from cur_tables into @ table_name; while @ FETCH_STATUS = 0 beginset @ file_name = ''; set @ file_path =''; set @ SQL = 'select * from DHL_POSM_WS .. '+ @ table_name; set @ SQL + = 'where 1 = 1 and DATEDIFF (MONTH, MODIFY_TIME, GETDATE ()> 10'; print @ SQL; set @ exec_ SQL = 'bcp "'+ @ SQL +'" queryout '; if ''= @ file_path beginset @ file_path = 'd: \ Program Files (x86) \ Microsoft SQL Server \ '; end; print '000000'; set @ file_name = @ table_name +' _ '+ CONVERT (varchar (111111), GETDATE (), invalid values }'.xls '; set @ file_path = @ file_path + @ file_name;/* file path */print '000000'; set @ exec_ SQL = @ exec_ SQL + '"' + @ file_path + '"'; set @ exec_ SQL = @ exec_ SQL + '-c-S "127.0.0.1 \ SQLEXPRESS"-U "DHL_POSM_WS"-P "DHLposm"'; print @ exec_ SQL; -- export data to the local file exec master .. xp_cmdshell @ exec_ SQL; fetch next from cur_tables into @ table_name; end; close cur_tables; -- close the cursor deallocate cur_tables; -- release the cursor end vertex catchclose cur_tables; -- close the cursor deallocate cur_tables; -- release the cursor end catch; go -- executes the stored procedure and tests exec sp_export_posm_data '';

Note:

1. query statement syntax select * from [database name]... [Table name];
If the SQLState = S1000 and NativeError = 0 error occurs during running, it indicates that your database name or table name is incorrect.
2. bcp 'SQL statement 'queryyout-c-S' IP \ Database Service instance'-U' database logon Username'-p' database logon password'
If the SQLState = S0002 and NativeError = 208 error occurs during the running process, it indicates that your-S service name is incorrect,
Generally, the error is caused by the absence of database service instances. You can refer to the connection of your database and write it according to the database connection.
It's my local database connection, so I can write-S in two ways:-S '2017. 0.0.1 \ SQLEXPRESS 'or-S' PED-VICKY-251 \ SQLEXPRESS'

3. Chinese garbled characters of exported files. Solution
Bcp 'SQL statement 'queryyout-c-S' IP \ Database Service instance'-U' database logon Username'-p' database logon password' is changed
Bcp 'SQL statement 'queryyout-w-S' IP \ Database Service instance'-U' database logon Username'-p' database logon password'
Change-c to-w.

4. the exported file storage directory must be the directory installed in the SQL Server database. Otherwise, an error will occur.

Import SQL Server backup files,

First, you need to create a new database in your Enterprise Manager. The database name should be the same as the database name backed up by the database. You can also create a different database, but the recovery is a bit complicated.

After the database is created, if the database name is the same as the backup database name, right-click the new database and choose all tasks> Restore database, select a device from the device, add, and select the backup, just be sure all the way

If the database name is different from the backup database name, right-click the new database and choose all tasks> Restore database, select a device from the device, add, and select the backup, stop on the "Restore database" page, click the "options" tab, and change the file name to the name of the database you created after moving to the physical file name. Remember that there is an underscore '_' + 'data. MDF '............
Change both, one Data. MDF and one Log. LDF. Then select force restore. OK.

Use the bcp command to import table files to SQL server

Try:
Bcp "data2005.. dbo.2009 _ 12_31_C" in "2009_12_31_C.xls"-c-t-U "sa"-P "_ SQL"-S "nanhui"

Data2005 should be followed by two points