Is there one sql command to export the entire database from one sql server to another?

Question

In the interview, I was asked to indicate various ways of exporting a database from one sql server to another, I only knew about creating a .bak file, and then restored it to another sql server, which I told them about. However, they asked me about one SQL INSERT command that would accomplish this task.

I have googled and can't find it. Please tell me if there is such a team?

Answer 1

I have never heard of such a command and this is an MS support article that talks about how to move a database between servers. It gives three options, none of which is a single insertion expression, the closest one uses sp_detach_db and sp_attach_db.

Answer 2

Well with SQL Statement you can backup and restore. Doing this with a single SQL INSERT ... I've never heard anything like it. Maybe one table. But not the whole database.

Another way is to use Copy Wizzard Database.

I also do interviews, and sometimes you just ask what does not exist or does not work, and see what happens.

Answer 3

If you already have a linked server, I would suggest that you can use sp_msforeachtable around INSERT INTO server2.tbl SELECT * FROM tbl .

But this will not work with dependencies or referential integrity scenarios where you might need an IDENTITY INSERT , disabling triggers or something else. Handling trivial cases is usually trivial by definition.

Answer 4

you need to say linked server

http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm