Automatic Backup of mssql server database and compressed batch processing script

Source: Internet
Author: User
Tags mssql server

1. set bakupfolder = F: \ backup \
Backup files are stored in the directory F: \ backup \ (this directory must be created in advance)
2. By default, each database is placed in a subdirectory with the same name under bakupfolder (automatically created by the script); When lay_in_subfolder is set to 0, it is directly placed in bakupfolder
3. The time string when the backup is automatically added to the backup file name. You do not have to worry about duplicate names in the Backup Directory, which is convenient for management.
4. call: backupone foo
Foo is the database to be backed up. To back up other databases, write one row in the same way.
5. Command Line-driven backup operation: sqlcmd-U sa-P "sa"-S localhost-Q "xxx"
The username and password used to connect to the database are all sa. Please change them to your actual username and password. If the mssql password contains some special characters, an error may be reported. Therefore, double quotation marks are added. If the password is simple, you can do it without adding it. However, the sa password is generally abnormal ~~

Copy codeThe Code is as follows:
@ ECHO ON
Set d = % date :~ 0, 10%
Set d = % d:-= %
Set t = % time :~ 0, 8%
Set t = % t: = %
Set stamp = % p % d % t %
Set bakupfolder = F: \ backup \
Rem 1 saves backup files by subdirectory; 0 does not press
Set lay_in_subfolder = 1
Call: backupone foo
Call: backupone foo2
Call: backupone foo3
Call: backupone foo4
Goto: EOF
@ ECHO OFF
: Backupone
Setlocal
Echo % 1
Set dbname = % 1
If not exist % bakupfolder % dbname % mkdir % bakupfolder % dbname %
If % lay_in_subfolder % = 1 (
Set subfolder = % dbname % \
) Else set subfolder =
Rem echo % bakupfolder % subfolder % dbname % stamp %. bak
Sqlcmd-U sa-P "sa"-S localhost-Q "backup database % dbname % to disk = '% bakupfolder % subfolder % dbname % stamp %. bak '"
"C: \ Program Files \ WinRAR \ RAR.exe" a-ep1-r-o +-m5-s-df "% bakupfolder % subfolder % dbname % stamp % ". rar "% bakupfolder % subfolder % dbname % stamp %. bak"
Endlocal & goto: EOF

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.