SQL Database Full replication

Rarely touches things in the Windows environment, has recently been a big head of the database of MS SQL Server. It's not as pro as MySQL, OrZ ...

I would have thought that since the DTS Data import/Export feature is provided in the Enterprise Manager, the database can be fully replicated to the remote server by using DTS to specify the source and target libraries directly. The results found that the field constraints, key values, default values, and so on, were not passed except for the table's basic structure and data. Ultimately, it's up to the direct disk to copy the data files.

If the database service can stop , the quickest way is to copy the corresponding. mdf and. ldf files directly (in the Enterprise Manager Select database Right-click on the properties ' data files ' entry to determine the file name), and then execute the T-SQL command (such as the new target database named Test):

--If the test database already exists, you need to detach it first.
--If an application is already connected to the database, use the following statement to force an existing connection to be disconnected
ALTER DATABASE Test

SET Single_user
With ROLLBACK IMMEDIATE;
--Separating the TST database
EXEC sp_detach_db
@dbname = ' Test ',
@skipchecks = ' true '

--Attach the copied. mdf file and the. ldf file as data files for the test database
EXEC sp_attach_db

@dbname = ' Test ',
@filename1 = ' E:\db\test.mdf ',
@filename2 = ' E:\db\test.ldf '

--If you do not want to use the original. ldf file, you can also hang the. mdf file separately
EXEC sp_attach_single_file_db

@dbname = ' Test ',
@physname = ' E:\db\test.mdf '

If the current database is in a production environment and is not allowed to shut down the service , you can use the Backup/restore method to complete the copy work:

--Back up the database named Realdb to the specified disk file
--Format the Bak file with the with format parameter for a full rewrite
BACKUP DATABASE realdb to DISK = ' E:\db\BAK_FOR_realdb.bak ' with FORMAT
--Use the RESTORE filelistonly command to view the contents of the database backup file
--Logical name of the data file and log file for the next command
RESTORE filelistonly from DISK = ' E:\db\BAK_FOR_realdb.bak '

--Create a new database using the backup file, Realdb_dat and Realdb_log
--The data file logic identified above using the RESTORE filelistonly command, respectively
--Name
RESTORE DATABASE Test
From DISK = ' E:\db\BAK_FOR_realdb.bak '
With MOVE ' Realdb_dat ' to ' E:\db\test.mdf ',
MOVE ' Realdb_log ' to ' E:\db\test.ldf '
GO