Backup Restore

Här är lite script för att kontrollera hur lång tid det är kvar på backuper och restore, plus lite annat som kan vara bra att veta kring backuper och restore.

-- 1. FÖR ATT KONTROLLERA TID KVAR PÅ BACKUP ELLER RESTORE AV DB. 
SELECT [command], 
'EstimatedEndTime' = Dateadd(ms, estimated_completion_time, Getdate()), 
'EstimatedSecondsToEnd' = estimated_completion_time / 1000, 
'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60, 
'BackupStartTime' = start_time, 
'PercentComplete' = percent_complete 
FROM   sys.dm_exec_requests 
WHERE  [command] IN ( 'BACKUP DATABASE', 'RESTORE DATABASE', 'RESTORE LOG', 
'BACKUP LOG' ) 

-- 2. FÖR ATT LÄSA VAD SOM ÄR BACKUPDIRECTORY: 
EXEC master.dbo.Xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'BackupDirectory' 

-- 3. För att sätta korrekt BackupDirectory: 
EXEC master.dbo.Xp_instance_regwrite 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'BackupDirectory', 
reg_sz, 
N'P:\DBBackup' 

-- 4. DELETE ALL BACKUP/RESTORE HISTORY PRIOR TO A SPECIFIED DATE 
USE msdb 
go 
EXEC Sp_delete_backuphistory '2009-04-02' 
go 

-- 5. DELETE ALL BACKUP HISTORY FOR A SPECIFIC DATABASE  
USE msdb 
go 
EXEC Sp_delete_database_backuphistory 
'test1' 
go 

-- 6. FÖR ATT KONTROLLERA HUR MYCKET BCK HISTORIKEN ÄR 
SELECT * 
FROM   backupfile 

-- 7. FÖR ATT KONTROLLERA NÄR SENASTE BACKUPER GÅTT 
SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
Max(msdb.dbo.backupset.backup_finish_date)       AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = 
msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D' 
GROUP  BY msdb.dbo.backupset.database_name 
ORDER  BY last_db_backup_date DESC 
--msdb.dbo.backupset.database_name desc 

-- 8. FÖR ATT KONTROLLERA NÄR SENASTE BACKUPER GÅTT LITE ENKLARE ATT LÄSA RESULTATET 
SELECT B.NAME 
AS 
Database_Name, 
Isnull(Str(Abs(Datediff(day, Getdate(), Max(backup_finish_date)))), 
'NEVER') AS 
DaysSinceLastBackup, 
Isnull(CONVERT(CHAR(10), Max(backup_finish_date), 101), 'NEVER') 
AS 
LastBackupDate 
FROM   master.dbo.sysdatabases B 
LEFT OUTER JOIN msdb.dbo.backupset A 
ON A.database_name = B.NAME 
AND A.type = 'D' 
GROUP  BY B.NAME 
ORDER  BY B.NAME 

-- 9. FÖR ATT KONTROLLERA NÄR DB SENAST BLEV RESTORED 
DECLARE @dbname SYSNAME, 
@days   INT 
SET @dbname = NULL --substitute for whatever database name you want 
SET @days = -30 --previous number of days, script will default to 30 
SELECT rsh.destination_database_name AS [Database], 
rsh.user_name                 AS [Restored By], 
CASE 
WHEN rsh.restore_type = 'D' THEN 'Database' 
WHEN rsh.restore_type = 'F' THEN 'File' 
WHEN rsh.restore_type = 'G' THEN 'Filegroup' 
WHEN rsh.restore_type = 'I' THEN 'Differential' 
WHEN rsh.restore_type = 'L' THEN 'Log' 
WHEN rsh.restore_type = 'V' THEN 'Verifyonly' 
WHEN rsh.restore_type = 'R' THEN 'Revert' 
ELSE rsh.restore_type 
END                           AS [Restore Type], 
rsh.restore_date              AS [Restore Started], 
bmf.physical_device_name      AS [Restored From], 
rf.destination_phys_name      AS [Restored To] 
FROM   msdb.dbo.restorehistory rsh 
INNER JOIN msdb.dbo.backupset bs 
ON rsh.backup_set_id = bs.backup_set_id 
INNER JOIN msdb.dbo.restorefile rf 
ON rsh.restore_history_id = rf.restore_history_id 
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bmf.media_set_id = bs.media_set_id 
WHERE  rsh.restore_date >= Dateadd(dd, Isnull(@days, -30), Getdate()) 
--want to search for previous days 
AND destination_database_name = Isnull(@dbname, destination_database_name 
) --if no dbname, then return all 
ORDER  BY rsh.restore_history_id DESC 
go 

-- 10. BACKUP AV SAMTLIGA ANVÄNDAR DATABASER 
DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name 
-- specify database backup directory 
SET @path = 'P:\DBBackup\' 
-- specify filename format 
SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) 
DECLARE db_cursor CURSOR FOR 
  SELECT NAME 
  FROM   master.dbo.sysdatabases 
  WHERE  NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) -- exclude these databases 
OPEN db_cursor 
FETCH next FROM db_cursor INTO @name 
WHILE @@FETCHSTATUS = 0 
  BEGIN 
      SET @fileName = @path + @name + '' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 
      FETCH next FROM db_cursor INTO @name 
  END 
CLOSE db_cursor 
DEALLOCATE db_cursor