Not sure if this is the correct Forum, anyway lemme me know your views. I have SQL Server running in two remote machines (in different geographical locations). I need to import a database from one SQL Server installation to another installation. What is best possible solution:
1) Transfering the .mdf and .ldf file from one installation to another installation
2) Generating the records of all the tables in the database to a common file format CSV and then loading it in destination database. If this is ok then how to generate import and export data using CSV format
3) Any other options?
Srikanth
If you need all data and structures, then the best method is a backup. MDF and LDF will do much the same, it covers everything too, but those files also include free space within the DB, so normally are much larger than required just for data transfer, and for running a system that is correct, just not so nice for copying files around.
|||also when you back up or move your database files ( I would suggest a backup, it is cleaner) do a database and log shrink to reduce the size of the backup.
Performing a zip file afterwards on the bak will reduce the size more for travel.
|||I think shrinking database and logs is a very bad idea. It can have serious impact on a production system, such as block transactions if you go too small, and consuming lots of IO. It can also cause fragmentation in the files themselves, and on disk. Good management of files is essential for performance and availability, and shinking does not normally fit with that. Saying that for a small system it may not matter, but it should not be seen as a easy or cheap option.
Zipping will help a lot, you may even want to consider some of third-party tools that compress backups, and genarlly run faster as well compared to native backup. Quest has LiteSpeed, Idera has something and Red Gate have SQL Backup.
|||Thanks a lot for your suggestions. How is CSV formating of the tables in the database. Is it possible? If so how?|||
You can export data from a table to a CSV, but I think we said backups would be better for all tables etc.
The simplest way to export a table to CSV would be to use the SSIS Import/Export Wizard. There are several ways to invoke this, but try right-clicking the database node in SSMS and select Tasks - Export Data
