SINGLE_USER => nur noch Admin in der DB
USE [master];
GO
ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
wenn möglich jetzigen Stand sichern (Transaktionslog)
USE [master];
GO
BACKUP LOG [db_name] TO
DISK = N'P:\MSSQL\Backup\db_name.20141007_1545.trn'
WITH NOFORMAT, NOINIT, NAME = N'db_name-Transaction Log Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
;
GO
Datenbank am alten Ort mit .BAK File neu aufbauen
USE [master];
GO
RESTORE DATABASE [db_name] FROM
DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141005_204550_1068033.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
;
GO
-- oder ev nötig:
-- Datenbank an neuem Ort mit .BAK File neu aufbauen
RESTORE DATABASE [db_name] FROM
DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141005_204550_1068033.bak'
WITH FILE = 1
, MOVE N'db_name_PRIMARY' TO N'F:\MSSQL\Data\db_name_PRIMARY.mdf'
, MOVE N'db_name_DATA_01' TO N'F:\MSSQL\Data\db_name_DATA_01.ndf'
, MOVE N'db_name_INDX_01' TO N'F:\MSSQL\Data\db_name_INDX_01.ndf'
, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
;
GO
nun alle Transaktionslog (die nach dem .BAK erstellt wurden) anwenden
RESTORE LOG [db_name] FROM DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141005_210000_5266249.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
RESTORE LOG [db_name] FROM DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141005_220000_5345678.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
-- usw...
RESTORE LOG [db_name] FROM DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141007_140006_5833635.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
RESTORE LOG [db_name] FROM DISK = N'P:\MSSQL\Backup\restore\db_name_backup_20141007_150006_5898765.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
GO
-- das letzte, das erstellt wurde, nachdem in den SINGLE_USER Modus gewechselt wurde:
RESTORE LOG [db_name] FROM DISK = N'P:\MSSQL\Backup\db_name.20141007_1545.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
GO
Erst jetzt Recovery (wenn bisher keine Fehler)
RESTORE DATABASE [db_name] WITH RECOVERY;
GO
und sofort ein volles Backup erstellen mit (Checksumme und Validierung)
BACKUP DATABASE [db_name] TO
DISK = N'P:\MSSQL\Backup\db_name.20141007_1606.bak'
WITH NOFORMAT, NOINIT, NAME = N'db_name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
;
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset
where database_name=N'db_name'
and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'db_name' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''db_name'' not found.', 16, 1) end
RESTORE VERIFYONLY
FROM DISK = N'P:\MSSQL\Backup\db_name.20141007_1606.bak'
WITH FILE = @backupSetId
, NOUNLOAD, NOREWIND
;
GO
wenn alles OK => Apps wieder rein lassen
ALTER DATABASE [db_name] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO