MS SQL — Backup и restore баз данных

В помощь администраторам и разработчикам при создании и восстановлении Backup на Microsoft SQL Server предлагаю Transact-SQL скрипты.

1. Создание Backup нескольких баз данных в одном скрипте.
В этом скрипте вы можете добавить любую свою базу данных одной строкой. Файл бэкапа содержит имя базы данных, дату и время создания backup.

create procedure #backupDBase @dbName varchar(50), @backupDir varchar(200)
as
begin
	DECLARE @toDisk varchar(255), @backupFileName varchar(100), @backupName varchar(50), @dateTime varchar(30)

	SET @dateTime		= REPLACE(convert(char(19),GetDate(),120),':','-')
	SET @dateTime		= '_' + REPLACE(@dateTime,' ','_')
	SET @backupName         = N'Backup DB ' + @dbName
	SET @backupFileName     = @dbName + @dateTime + '.bak'
	SET @toDisk		= @backupDir + @backupFileName

	BACKUP DATABASE @dbName to disk=@toDisk WITH COMPRESSION, COPY_ONLY, INIT, NOUNLOAD, NAME=@backupName, NOSKIP, NOFORMAT
end;
go

DECLARE @backupDir varchar(200) = 'd:\Backup\'

--В этом блоке указать базы данных, для которых необходимо создать Backup
exec #backupDBase 'DB_Name_1', @backupDir
exec #backupDBase 'DB_Name_2', @backupDir
exec #backupDBase 'DB_Name_3', @backupDir

drop procedure #backupDBase

2. Восстановление базы данных из Backup файла.

use master
DECLARE @DBName varchar(20), @FileBackup varchar(255), @DiskBaseLog varchar(20), @DiskBaseData varchar(20), @DiskBaseTables varchar(20)
DECLARE @NameDataFile varchar(20), @NameLogFile varchar(20), @NameTableLogFile varchar(20), @NameTablesFile varchar(20)
DECLARE @PathDataFile varchar(255), @PathLogFile varchar(255), @PathTableLogFile varchar(255), @PathTablesFile varchar(255)
SET @DBName			= 'ax2012Demo' -- Указать имя базы данных
SET @DiskBaseData		= 'd:\' -- Указать диск, где хранится mdf файл
SET @DiskBaseLog		= 'd:\' -- Указать диск, где хранится ldf файл
SET @FileBackup			= 'z:\BackupDB\Backup_File_Name_2019-03-15_16-25-38.bak'
SET @NameDataFile		= 'ax2012Demo' -- Указать логическое имя Data файла
SET @NameLogFile		= 'ax2012Demo_log' -- Указать логическое имя Log файла
SET @PathDataFile		= @DiskBaseData		+ 'Data\'	+ @DBName + '.mdf'
SET @PathLogFile		= @DiskBaseLog		+ 'Log\'	+ @DBName + '.ldf'
RESTORE DATABASE @DBName FROM DISK = @FileBackup
WITH	MOVE @NameDataFile TO @PathDataFile, 
		MOVE @NameLogFile TO @PathLogFile, 
		REPLACE,
		STATS = 1

В скрипте по умолчанию восстанавливает данные из файла z:\BackupDB\Backup_File_Name_2019-03-15_16-25-38.bak. Файлы базы данных по умолчанию будут созданы в директориях:

• D:\Data\
• D:\Log\

Вам необходимо указать:

1. @DBName — имя базы данных (Пример: ax2012Demo)
2. @FileBackup — путь и название backup файла (Пример: z:\BackupDB\Backup_File_Name_2019-03-15_16-25-38.bak)
3. @DiskBaseData — диск, где хранится .mdf файл (Пример: d:\)
4. @DiskBaseLog — диск, где хранится .ldf файл (Пример: d:\)
5. @NameDataFile — логическое имя Data файла (Пример: ax2012Demo)
6. @NameLogFile — логическое имя Log файла (Пример: ax2012Demo_log)
7. В параметрах @PathDataFile и @PathLogFile можно изменить название папок, где хранятся файлы базы данных.

3. Восстановление базы данных, в которой часть таблиц выделены в отдельный файл (параметр @DiskBaseTables):

use master
DECLARE @DBName varchar(20), @FileBackup varchar(255), @DiskBaseLog varchar(20), @DiskBaseData varchar(20), @DiskBaseTables varchar(20)
DECLARE @NameDataFile varchar(20), @NameLogFile varchar(20), @NameTableLogFile varchar(20), @NameTablesFile varchar(20)
DECLARE @PathDataFile varchar(255), @PathLogFile varchar(255), @PathTableLogFile varchar(255), @PathTablesFile varchar(255)
SET @DBName			= 'axPrework'
SET @DiskBaseData		= 'd:\'
SET @DiskBaseTables		= 'd:\'
SET @DiskBaseLog		= 'd:\'
SET @FileBackup			= 'c:\temp\axWork-2015.09.01-13.44.58.copy.bak'
SET @NameDataFile		= 'axWork'
SET @NameTablesFile		= 'axWorkTables'
SET @NameTableLogFile	        = 'axWorkTableLog'
SET @NameLogFile		= 'axWorkLog'
SET @PathDataFile		= @DiskBaseData		+ 'Data\'	+ @DBName + '.mdf'
SET @PathTablesFile		= @DiskBaseTables	+ 'Data\'	+ @DBName + 'Tables'	+ '.ndf'
SET @PathTableLogFile	= @DiskBaseTables	+ 'Data\'	+ @DBName + 'TableLog'	+ '.ndf'
SET @PathLogFile		= @DiskBaseLog		+ 'Log\'	+ @DBName + '.ldf'
RESTORE DATABASE @DBName FROM DISK = @FileBackup
WITH	MOVE @NameDataFile TO @PathDataFile, 
		MOVE @NameTablesFile TO @PathTablesFile, 
		MOVE @NameTableLogFile TO @PathTableLogFile, 
		MOVE @NameLogFile TO @PathLogFile, 
		REPLACE,
		STATS = 1