Exporting a database from one SQL Server to another SQL Server

Question

I have a test database that I need to export to our client test environment.

It will be a one-time job.

I am using SQL Server 2005 (my test db is SQL Server 2005 Express)

What is the best way to do this?

Answer 1

The easiest way

Back up the database to SSMS and then restore the database on the target machine.

SSMS (SQL Server Management Studio), , , Tasks->Backup, .bak. (.bak) / . "Restore".

.bak, .

,

, T-SQL . , , ... , , , , . , - , , :

DECLARE @strRootPath varchar(50)
DECLARE @BackupFile varchar(100)
DECLARE @strDB varchar(25)

SELECT @strRootPath = 'C:\SQL_BACKUPS\MyDBFolder\'
SELECT @strDB = db_name()

SELECT @BackupFile = 
      @strRootPath
    + db_name()
    + '_'
    + CONVERT(varchar(8), GetDate(), 112)               -- yyyymmdd
    + '_'
    + REPLACE(LEFT(CONVERT(varchar(8), GetDate(), 108), 5), ':', '')    -- hh:mm:ss
    + '.BAK'

BACKUP DATABASE @strDB TO  DISK =@BackupFile WITH RETAINDAYS = 10, NAME = N'MyDB_DATA-Full Database Backup', STATS = 10

BACKUP LOG MyDB
   TO MyDB_Log;

Answer 2

, , SQL Server Management Studio (SSMS), , , -. , :)