SELECT * FROM Vzakladke.net

Статьи об автоматизации и программировании


Запросы MS SQL на бэкап и восстановление и создание моментальных снимков базы данных

 

 

Резервное копирование, восстановление и создание моментальных снимков базы данных на сервере MS SQL

MS SQL backup and resotore database or creating snapshots

 

-- MS SQL - запрос создания устройства резервного копирования:

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NameDeviceBackup', 
@physicalname = N'R:\Backup\NameDeviceBackup.bak'
GO


-- MS SQL - запрос создания моментальных снимков баз данных:

USE [master]
GO

CREATE DATABASE [DbName_2012_01_19] ON
( NAME = N'DbName_dat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DbName_2011_01_17.ss' ) AS SNAPSHOT OF [DbName]
GO




-- сделать бэкап:
BACKUP DATABASE DbNameSuperName
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DbNameSuperName.bak' with FORMAT



-- восстановить из бэкапа:

-- определяем ключи, то есть имена файлов mdf и ldf 
restore filelistonly from disk='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ArchiveDB.bak'
-- подставляем их в запрос и выполняем восстановление
restore database ArchiveDbName_2013 from disk='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ArchiveDB.bak'
with move 'DbName_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ArchiveDbName_2013.mdf',
move 'DbName_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ArchiveDbName_2013.ldf'

 

Если нет файла .ldf то необходимо следующее

  1. Создаем новую базу с таким же именем и такимиже по именам и расположению .mdf и .ldf файлами 
  2. Останавливаем сервер, подменяем файл .mdf 
  3. Стартуем сервер, не обращаем внимания на статус базы 
  4. Выполняем скрипт:

 

Use master 
go 
sp_configure 'allow updates', 1 
reconfigure with override 
go 

-- 4. Там же выполняем 
select status from sysdatabases where name = '<db_name>' 
и запоминаем/записываем значение на случай неудачи ребилда лога 

-- 5.Там же выполняем 
update sysdatabases set status= 32768 where name = '<db_name>' 

-- 6. Перезапускаем SQL Server 

7. В принципе база должна быть видна (в emergency mode). Можно, например, заскриптовать все объекты 

-- 8. Из QA выполняем 
DBCC REBUILD_LOG('<db_name>', '<имя нового лога с указанием полного пути>')
SQL Server скажет - Warning: The log for database '<db_name>' has been rebuilt. 

-- 9. Если все нормально, то там же выполняем 
Use master 
go 
sp_dboption '<db_name>', 'single user', 'true' 
go 
USE <db_name> 
GO 
DBCC CHECKDB('<db_name>', REPAIR_ALLOW_DATA_LOSS) 
go 

-- 9.1.
Если Вам не удалось перевести базу в single user mode, то для проверки целостности данных можно попробовать dbo only mode
sp_dboption '<db_name>', 'dbo use only', 'true' 

-- 10. Если все в порядке, то 
sp_dboption '<db_name>', 'single user', 'false' 
go 
Use master 
go 
sp_configure 'allow updates', 0 
go

 

 

 

По сути механизм копирования баз данных из SSMS выглядит таким образом

 

1. Отсоединить базу данных (правой кнопкой мыши кликнуть на базу и выбрать Detach, убедитесь что нет подключенных пользователей)
2. Копируйте mdf и ldf файлы в Ваше новое место хранения
3. Присоединить базу данных (правой кнопкой мыши кликнуть на базу и выбрать Attach)

 

По сути происхдит следующее:

USE MASTER;
GO
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'
GO

 

Теперь перемещаем файлы из loc1 в loc2. И далее вы можете прикрепить файлы из нового расположения.

 

-- перемещаем файл базы из Loc1 в Loc 2
-- И ретачим базу (Re-Attached)
CREATE DATABASE [TestDB] ON
( FILENAME = N'F:\loc2\TestDB.mdf' ),
( FILENAME = N'F:\loc2\TestDB_log.ldf' )
FOR ATTACH
GO

 

Дата публикации: 2015-05-16 12:28:49

MS SQL

0

Отзывы:

Шурик
А бэкап тут: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
Шурик
короче спасибо, снипшот создал так: из базы dbname выполнил запрос CREATE DATABASE [dbname_2015_05_18] ON ( NAME = N'costdb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db18-05-2015.ss' ) AS SNAPSHOT OF dbname GO
Шурик
А чтобы восстановить базу просто отсоединил ее и выполнил CREATE DATABASE dbname ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_log.ldf' ) FOR ATTACH

Ваше имя:

Ваш e-mail (необязательно):

Сообщение:

Captcha