DBCC,Backup, Verify and create Agent Job to restore
This is a script to automatically backup, drop and create an Agent Job to restore from that backup.
USAGE - You need to Change the Database Name after " insert #vars values (' "
You also need to check that the folder after " Set @Path = (SELECT ' " is correct and exists and Find and Replace both entries for THEBEARD\Rob with the account that will be the owner of the job and the database owner.
/***
Rationalisation Script
Script to Automatically Backup, Drop and create Agent Job to restore from that backup
AUTHOR - Rob Sewell http://sqldbawithabeard.com
DATE - 19/01/2014
USAGE - You need to Change the Database Name after " insert #vars values (' "
You also need to check that the folder after " Set @Path = (SELECT ' " is correct and exists
and Find and replace both entries for THEBEARD\Rob with the account that will be the owner of the job and the database owner
Once this has been run AND you have checked that it has successfully backed up the database and created the job and you have checked the job works
You may delete the backups but keep the backup folder
***/
--Drop temp table if it exists
IF OBJECT_ID('tempdb..#vars') IS NOT NULL
DROP TABLE #vars
--Create table to hold global variable
create table #vars (DBName nvarchar(50), PATH nvarchar(300),DataName nvarchar(50),LogName nvarchar (50),DataLoc nvarchar (256),LogLoc nvarchar (256))
insert into #vars (DBName) values ('SQL2012Ser2012DB'
)
--Declare and set variables
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)
DECLARE @PATH nvarchar(300)
Set @Path = (SELECT 'PATH TO RATIONALISATION FOLDER' + @DBName + '_LastGolden_' + + convert(varchar(50),GetDate(),112) + '.bak' )
DECLARE @DataName nvarchar(50)
Set @DataName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
--Print @DataName
DECLARE @LogName nvarchar (50)
Set @LogName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
--PRINT @LogName
Declare @DataLoc nvarchar (256)
Set @DataLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
--Print @DataLoc
Declare @LogLoc nvarchar (256)
Set @LogLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
--Print @LogLoc
update #vars Set PATH = @PATH
update #vars Set DataName = @DataName
update #vars Set LogName = @LogName
update #vars Set DataLoc = @DataLoc
update #vars Set LogLoc = @LogLoc
-- Select * from #vars
-- DBCC
DECLARE @DBCCSQL nvarchar (4000)
SET @DBCCSQL = '
USE [' + @DBName + ']
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
'
-- Print @DBCCSQL
EXECUTE(@DBCCSQL)
-- Break out if error raised We need to do some work if there are errors here
if @@error != 0 raiserror('Rationalisation Script failed at DBCC', 20, -1) with log
GO
-- Declare and set variables
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)
DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)
Declare @BKUPName nvarchar(300)
Set @BKUPName = (SELECT 'Last Golden Backup For ' + @DBName + '- Full Database Backup')
DECLARE @BackupSQL nvarchar (4000)
SET @BackupSQL = '
BACKUP DATABASE [' + @DBName + '] TO DISK = N''' + @PATH + '''
WITH INIT, NAME = N''' + @BKUPName + ''',
CHECKSUM, STATS = 10
'
--- PRINT @BackupSQL
-- Backup database to Golden backup location
EXECUTE(@BackupSQL)
GO
-- Break Out if there are errors here - If there is no backup we don't want to continue
if @@error != 0 raiserror('Rationalisation Script failed at Backup', 20, -1) with log
GO
DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)
RESTORE VERIFYONLY
FROM DISK = @PATH;
if @@error != 0 raiserror('Rationalisation Script failed at Verify Restore', 20, -1) with log
GO
-- Declare variables for dropping database
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)
DECLARE @DROPSQL nvarchar (4000)
SET @DROPSQL = '
USE [master]
ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [' + @DBName + ']
'
-- PRINT @DROPSQL
--Drop database
EXECUTE(@DROPSQL)
GO
if @@error != 0 raiserror('Rationalisation Script failed at Drop Database', 20, -1) with log
GO
--Declare variables for creating Job
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)
DECLARE @PATH nvarchar(300)
Set @Path = (Select PATH from #vars)
DECLARE @DataName nvarchar(50)
Set @DataName = (Select DataName from #vars)
DECLARE @LogName nvarchar (50)
Set @LogName = (Select LogName from #vars)
Declare @DataLoc nvarchar (256)
Set @DataLoc = (Select DataLoc from #vars)
Declare @LogLoc nvarchar (256)
Set @LogLoc = (Select LogLoc from #vars)
DECLARE @RestoreCommand nvarchar(4000)
Set @RestoreCommand = '''RESTORE DATABASE [' + @DBName + ']
FROM DISK = N''''' + @PATH + '''''
WITH FILE = 1,
MOVE N''''' + @DataName + ''''' TO N''''' + @DataLoc + ''''',
MOVE N''''' + @LogName + ''''' TO N''''' + @LogLoc + ''''',
NOUNLOAD, REPLACE, STATS = 10
'''
--print @RestoreCommand
--Create Job creation tsql
DECLARE @JOBSQL nvarchar (4000)
SET @JOBSQL = 'USE [msdb]
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/18/2014 14:12:04 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''[Uncategorized (Local)]''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @JOBNAME nvarchar(300)
set @JOBNAME = ''Rationlised - - Restore ' + @DBName + ' from Last Golden Backup''
Declare @JobDesc nvarchar(300)
Set @JobDesc = '' Rationalised Database Restore Script for ' + @DBName + '''
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name= @JOBNAME,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=@JobDesc,
@category_name=N''[Uncategorized (Local)]'',
@owner_login_name=N''THEBEARD\Rob'', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Restore Database] Script Date: 01/18/2014 14:12:04 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Restore Database'',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N''TSQL'',
@command= ' + @RestoreCommand + ',
@database_name=N''master'',
@flags=4
/****** Object: Step [Set Owner] Script Date: 01/19/2014 10:14:57 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Set Owner'',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N''TSQL'',
@command=N''USE [' + @DBName + ']
EXEC sp_changedbowner @loginame = N''''THEBEARD\Rob'''', @map = false'',
@database_name=N''master'',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
'
--PRINT @JOBSQL
--Create Agent Job
EXECUTE(@JOBSql)
if @@error != 0 raiserror('Rationalisation Script failed at Create Job', 20, -1) with log
GO
DROP Table #vars