SqlServer one-click Copy database script

Assume there are two servers: 192.168.0.1 and 192.168.0.2. The database name to be copied is TestDB. Now, the database is copied from 192.168.0.1 to 192.168.0.2, where % source_database_backup_file_dir % is the folder for storing the backup file of the TestDB database on 192.168.0.1, % target_database_file_dir % is 192.168.0.2

Assume there are two servers: 192.168.0.1 and 192.168.0.2. The database name to be copied is TestDB. Now, the database is copied from 192.168.0.1 to 192.168.0.2, % source_database_backup_file_dir % is the folder for storing the backup files of the TestDB database on 192.168.0.1, and % target_database_file_dir % is 192.168.0.2

Assume there are two servers: 192.168.0.1 and 192.168.0.2.CopyOfDatabaseThe name is TestDB.CopyTo 192.168.0.2, where % source_database_backup_file_dir % is the folder for storing the backup files of the TestDB database on 192.168.0.1, and % target_database_file_dir % is TestDB on 192.168.0.2.DatabaseFile folder, BATScriptAs follows:

@echo offset source_server=192.168.0.1set source_server_user=saset source_server_password=saset target_server=192.168.0.2set target_server_user=saset target_server_password=saset database=TestDBset source_database_backup_file_dir=\\Systemname\ShareName\Pathset target_database_file_dir=\\Systemname\ShareName\Pathecho Start to backup source database...osql -S"%source_server%"  -U"%source_server_user%" -P"%source_server_password%" -n -h-1 -d"%database%" -Q"BACKUP DATABASE %database% TO DISK = '%source_database_backup_file_dir%\%database%.bak' WITH INIT;"echo Disconnect all existing connections of target database.osql -S"%target_server%"  -U"%target_server_user%" -P"%target_server_password%" -Q"declare @i int declare cur cursor for select spid from sysprocesses where db_name(dbid)= '%database%' open cur fetch next from cur into @i while @@fetch_status=0 begin exec('kill '+@i) fetch next from cur into @i end close cur deallocate cur"echo Start to resotre target database from source database backup file...osql -S"%target_server%"  -U"%target_server_user%" -P"%target_server_password%" -n -h-1 -d"Master" -Q"RESTORE DATABASE %database% FROM DISK = '%source_database_backup_file_dir%\%database%.bak' WITH REPLACE,MOVE '%database%' TO '%source_database_backup_file_dir%\%database%.mdf',MOVE '%database%_Log' TO '%source_database_backup_file_dir%\%database%_log.ldf';"echo DONE!!pause

BenScriptUse the BACKUP command to generate a BACKUP file for the original database, and then use the RESTORE command to RESTORE the BACKUP file to the target database.DatabaseCopy. For details about the BACKUP command, refer to snapshot.

There are two details:

1. The with init statement in the BACKUP statement overwrites the data in the original BACKUP file during each BACKUP.

2. Because both servers need to accessDatabaseBackup file. The folder for storing backup files must be set to "shared.