1C is an electronic accounting system that has knocked out all other competitors in recent years. The program often stores data in DBF files, but there is also an SQL version. They often switch to SQL with a large number of users, more than 15 people, to improve the stability of the server. There are several ways to transfer MS SQL database from one server to another.
It is necessary
- - installed program "1C: Enterprise";
- - a computer.
Instructions
Step 1
One of the fastest ways to transfer SQL is to detach the database from the server and transfer to a new one along with the log. First, you need to detach the database, changing its name accordingly. To do this, follow these steps: Use Master, GO, "Exec sp_detach_db 'database_name', 'true'", GO, where sp_detach_db is used to detach the database from the source. It has the following parameters: @dbname - name and @skipchecks - indication for updating statistics. Set to ‘true’ to ensure that statistics update is updated upon attachment.
Step 2
Then run: Use Master, GO, "PRINT 'Attaching Database'", "EXEC sp_attach_db @dbname = 'database_name'", "@ filename1 = 'c: / mssql7 / data / database_name.mdf'", "@ filename2 = ' d: / mssql7 / data / database_name_log.ldf '". This will attach the database and logs to the new server.
Step 3
Use the DTS Import and Export Wizard to copy information from server to server. Use the DTS Designer or the Copy Database Wizard to create a task to move the database and logins.
Step 4
Create a data transfer engine that uses bulk insert / bcp. Using a script, make the schema on the target server and then use bulk insert / bcp to copy the information. When choosing what to apply, remember that bulk insert, unlike bcp, cannot export data.
Step 5
Use distributed queries. After creating the schema on the target server, organize the linked server and write the insert statements using the openquery and openrowset functions. Before uploading data, be sure to disable check constraints and foreign key and reconnect them after the operation is complete.
Step 6
Use Backup and Restore. Make a copy of the database and then restore it to the new server.
