SQL Server database bcp export backup file application, serverbcp

SQL Server database bcp export backup file application, serverbcp
/**
* Authorization
*/
EXEC sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Exec sp_configure 'xp _ Your shell', 1;
Go
Reconfigure;
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 ') begin
Drop procedure sp_export_posm_data;
End;
Go

Create procedure sp_export_posm_data
@ File_path varchar (200)/* path of the exported file */
As
Declare @ exec_ SQL varchar (1000 );
Declare @ file_name varchar (200);/* file name, time format, 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 used to query business data */
/* Name of the business table for data backup */
Declare cur_tables cursor
Select name from sysobjects where 1 = 1 and type = 'U'
And name like 'wm _ ORDER % 'or name like 'wm _ PICKING %' or name like 'rp _ %'
Begin try
Open cur_tables;
Fetch next from cur_tables into @ table_name;
While @ FETCH_STATUS = 0 begin
Set @ 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 begin
Set @ file_path = 'd: \ Program Files (x86) \ Microsoft SQL Server \';
End;
Print '20140901 ';
Set @ file_name = @ table_name + '_' + CONVERT (varchar (100), GETDATE (), 1_1_1_'.xls ';
Set @ file_path = @ file_path + @ file_name;/* file path */
Print '20140901 ';
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 a local file
Exec master.. xp_mongoshell @ exec_ SQL;
Fetch next from cur_tables into @ table_name;
End;
Close cur_tables; -- close the cursor
Deallocate cur_tables; -- release the cursor
End try
Begin catch
Close cur_tables; -- close the cursor
Deallocate cur_tables; -- release the cursor
End catch;
Go


-- Execute the stored procedure for testing
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