Fully automated backup of vss and SQL databases (including source code download)-mysql tutorial

Preface visualsourcesafe has code and sqlserver has data, which is the whole project. They own everything, so the security of these things is really important. This article implements automatic backup of vss and all SQL databases by batch processing. Back up the database: uses osql to call SQL scripts and automatically backs up the database.

Preface there is code in visual source safe and data in SQL server, which is the whole project. They own everything, so the security of these things is really important. This article implements automatic backup of vss and all SQL databases by batch processing. Back up the database: uses osql to call SQL scripts and automatically backs up the database.

• Preface

Visual source safe has code, and SQL server has data, which is the whole project.

They own everything, so the security of these things is really important.

This article implements automatic backup of vss and all SQL databases by batch processing.

• Back up the database:

Use osql to call SQL scripts to automatically back up all databases and export execution results.

Back up the database's batch file (auto_backup_db.bat). he uses the trusted connection method to call the auto_backup_db. SQL script to implement the backup function,

Success,

Like: backup_database_log_2011-10-09.txt.

@echo off

@echo start to backup database
osql -E -i auto_backup_db.sql -o backup_database_log.txt
@echo finished backup database

FOR /F "tokens=1-3 delims=- " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k

rename "backup_database_log.txt" "backup_database_log_%DATE%.txt"

@echo on

The backup Database SQL script auto_backup_db. SQL is as follows. modify the file backup path as needed:

USE master
Go

DECLARE @ bak_path NVARCHAR (200)
DECLARE @ bak_file_name NVARCHAR (200)

-- Set the file backup path
SET @ bak_path = 'E: \ DataBaseBAK'

-- Uses cursor traversal to back up databases one by one
DECLARE @ db_name SYSNAME
DECLARE cur_database CURSOR
SELECT [name] -- query all databases
FROM sys. databases
WHERE [state] = 0 -- 0 = ONLINE status
AND [name] not in ('master', 'model', 'msdb', 'tempdb', 'reportserver', 'reportservertempdb ')
-- Except system databases and demo databases

OPEN cur_database
Fetch next from cur_database INTO @ db_name
WHILE (@ FETCH_STATUS = 0)
BEGIN
-- Set the backup file name, such as: dbname_2011-10-09.bak
SET @ bak_file_name = @ bak_path + '\' + @ db_name + '_'
+ CONVERT (VARCHAR (10), GETDATE (), 120) + '. bak'
        
-- Start full backup
Backup database @ db_name to disk = @ bak_file_name
Fetch next from cur_database INTO @ db_name
END
    
CLOSE cur_database
DEALLOCATE cur_database

• Automatic backup of vss auto_backup_vss.bat

@ECHO OFF
@TITLE Backing up source safe databases

SET VSS_Install_Path="C:\Program Files\Microsoft Visual SourceSafe\"
SET VSS_DB="\\192.168.0.244\vss"
SET Bak_File="e:\%DATE%_vss_backup.ssa"
SET VSS_Admin_Name="admin"
SET VSS_Admin_Password="your_password"

FOR /F "tokens=1-3 delims=- " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k

%VSS_Install_Path%"ssarc" -d- -y%VSS_Admin_Name%,%VSS_Admin_Password% -s%VSS_DB%  %Bak_File% $/

@ECHO finished backup vss

Modify these entries as needed:

VSS_Install_Path = vss installation path

VSS_DB = vss database path

Bak_File = backup file path

VSS_Admin_Password = vss Super Administrator password