Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to compress the backup database in sql server. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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.

On how to compress the backup database in sql server to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.