-- In SQL Server 2000, how to back up the database, compress the backup, and copy it to another location
-- 1: (Backup)
-- 1.1 create a Test Database
Use master
Go
If db_id ('dbtest') is not null
Drop database dbtest
Go
Create Database dbtest
Go
-- 1.2 set the backup path and file name
Declare @ path varchar (200)
Set @ Path = 'C:/bak/dbtest' + convert (varchar (10), getdate (), 23) + '. Bak'
-- 1.3 backup
Backup database dbtest to disk = @ path
-- 2: (compressing backup database files)
Declare @ archive varchar (200), @ cmd varchar (200)
-- 2.1 set the compressed file name
Set @ archive = 'C:/dbtest' + convert (varchar (10), getdate (), 23) + '. cab'
Exec xp_makecab @ archive, mszip, 1, @ path -- xp_makecab does not appear to be available in SQL 2005. It can be replaced by xp_mongoshell 'makecab.exe '.
-- 2.2 set other copies to the directory of the shared computer on the network, such as // server2/backups/sqldumps.
Set @ cmd = 'copy' + @ archive + 'd :/'
Exec xp_cmdshell @ cmd
---- The result is as follows:
/*
Deleting database file 'd:/program files/Microsoft SQL Server/MSSQL/data/dbtest_log.ldf '.
Deleting database file 'd:/program files/Microsoft SQL Server/MSSQL/data/dbtest. MDF '.
The create database process is allocating 0.75 MB space on the disk 'dbtest.
The create database process is allocating 0.49 MB space on the disk 'dbtest _ log.
96 pages have been processed. These pages belong to the database 'dbtest' file 'dbtest' (located on file 8 ).
One page has been processed, which belongs to the database's 'dbtest' file 'dbtest _ log' (located on file 8 ).
The backup database operation successfully processed 97 pages and took 0.205 seconds (3.846 MB/second ).
Message 2001, Level 5, status 2001
Adding the file 'C:/bak/DBTEST2010-06-14.bak 'to the package.
Message 2002, Level 5, Status 2002
File C:/bak/DBTEST2010-06-14.bak added to the zip package.
Output
---------------------------------------------------------------------------------
1 file has been copied.
Null
(The number of affected rows is 2)
*/