Shulou(Shulou.com)06/01 Report--

Today, you need to back up and copy the database of a non-provincial project from the server to the local machine (transfer database backup files across regions).

Connect to VPN, connect to the server through remote desktop connection, and find that the database file has a size of more than 20g.

The file is too large, and the company network is unstable, so it is impossible to transmit it over the network.

Therefore, set the recovery mode of the database from "complete" mode to "simple" mode, and then shrink the database.

The database changed from 20g to 1G multipoint in an instant.

In SSMS, create a new query window and execute the database backup statement:

-- define variables and assign the QL script of the backup database to the variable declare @ SqlBackupDataBase as nvarchar (1000) set @ SqlBackupDataBase=N'BACKUP DATABASE Northwind TO DISK =''D:\ Northwind-'+CONVERT (varchar (11), GETDATE ()) + REPLACE (varchar (12), GETDATE (),':',') + .bak''--backup file format: Northwind-20170111132424.bakexec sp_executesql @ SqlBackupDataBase-- call the system stored procedure and execute SQL

After a successful backup, the backed up bak file has a size of 1.16G:

Then, excitedly copy the bak file to this machine

Result: always in the process of transmission, the transmission is automatically interrupted.

The reason is that the network is unstable and the network bandwidth is limited.

Think about it, can you back it up to a smaller bak file?

Finally, we use the new feature of sql server 2008 and above: compressed backup database.

In fact, it is to add "with STATS = 1 recording" to the statement of backing up the database.

The full compressed backup database statement is as follows:

-- define variables and assign the QL script of the backup database to the variable declare @ SqlBackupDataBase as nvarchar (1000) set @ SqlBackupDataBase=N'BACKUP DATABASE Northwind TO DISK =''D:\ Northwind-'+CONVERT (varchar (11), GETDATE (), 112) + REPLACE (varchar (12), GETDATE (),':',') + '.bak''+ 'with STATS = 1, Northwind-20170111132424.bakexec sp_executesql @ SqlBackupDataBase-- call the system stored procedure Execute SQL

After the backup is successful, it is found that the backed up bak file is only the size of 160MB:

Finally, the backup file of the database is successfully copied and restored to the local database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.