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 the folder where the testdb database file on 192.168.0.2 is located. The Bat script is as 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

This script uses the BACKUP command to generate a backup file for the original database, and then uses the restore command to restore the backup file to the target database for database replication. 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 access the database backup file, the folder for storing the backup file must be set to "shared.