Резервное копирование всех баз в SQL Express 2005

SQL 2005 (and Express) Backup Script

After the last few years of finding some good ideas and tweaking my script I wanted to let everyone use it. If you want.

Here are some directions on how to use it.

1) Create a text file and name it Backup_All_Databases.sql (or what ever you want).

2) Paste the below script in it:

Code:

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = ‘C:\Backuped_SQL_DB\’
–Add a list of all databases you don’t want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> ‘tempdb’ AND name <> ‘model’ AND name <> ‘Northwind’
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

BEGIN
SET @Name = @DB + ‘( Daily BACKUP )’
SET @MediaName = @DB + ‘_Dump’ + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
SET @Description = ‘Normal’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’

IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = ‘master’
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

У меня скрипт заработал только с исправлением sysdatabases на sys.databases

3)    Open scheduler and create a new task that calls the below command line:
Code:
sqlcmd -S . -i “C:\Backups\BackupSettings\Backup_All_Databases.sql”

Note:  I also made a batch file that had the same command line but added a pause so I can test it before the scheduler runs it.
EXAMPLE:
Code:
sqlcmd -S . -i “C:\Backups\BackupSettings\Backup_All_Databases.sql” pause

4)    Just run it every night in a scheduler and you are done.

Clean up Old Backup Files.

If you are running Windows Server 2003 you can also run a command  utility to delete any files older then x number of days.  This helps  keep it cleaned up.  Just paste this in a batch file and schedule the  batch file.
Code:

echo on

rem First Delete old SQL Backup Files

FORFILES /p C:\Backuped_SQL_DB /s /m *.* /d -3 /c “CMD /C del /Q @FILE”

rem pause

This will also work on SQL Express

Rick Toner
MCP, MCSD
http://www.TonerTrail.com

Взято отсюда.
Позже переведу.

Запись опубликована в рубрике MsSQL. Добавьте в закладки постоянную ссылку.