Feb 28, 2013

Set up auto only schema or script backup in sql server


Some times we need to backup the only schema of tables, stored procedures, sql functions of given database. We can do it by generating the scrip of above objects and saving in a text file. Also we need to keep the old backups in specific period of time. By following steps we can easily setup above requirements.

Step 1: Create a backup history table:

CREATE TABLE tblBackupHistory(
ntBackupHistory BIGINT PRIMARY KEY IDENTITY,
vcDbName SYSNAME,
vcBackupFile VARCHAR(500),
btBackupExist BIT DEFAULT(1),
dtBackupData DATETIME DEFAULT(GETDATE())
)

Step 2: Create the stored procedure uspGenerateScript:

CREATE PROCEDURE uspGenerateScript(
@vcDatabasesName VARCHAR(500),
@vcFilePath AS VARCHAR(500) = 'C:\Backup\',
@ntBackupExpireDay AS TINYINT = 2
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @vcDBName AS SYSNAME
DECLARE @vcRoutineQuery AS NVARCHAR(4000)
DECLARE @vcTableQuery AS NVARCHAR(MAX)
DECLARE @vcBackupQuery AS VARCHAR(8000)
DECLARE @vcRemoveOldBackupQuery AS VARCHAR(8000)
DECLARE @vcFileName AS VARCHAR(500)
DECLARE @ntSatatus AS INT
BEGIN TRY
DECLARE curDb CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT * FROM dbo.DATA_udfSplit(@vcDatabasesName,',')
OPEN curDb
FETCH NEXT FROM curDb INTO @vcDBName
WHILE @@FETCH_STATUS = 0 BEGIN
IF OBJECT_ID('TEMPDB.DBO.##tblSchema') IS NOT NULL
DROP TABLE ##tblSchema
CREATE TABLE ##tblSchema(vcSchema VARCHAR(MAX))
SET @vcRoutineQuery = N'SELECT ROUTINE_DEFINITION FROM '
+ @vcDBName + '.INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_TYPE DESC,ROUTINE_NAME '
INSERT INTO ##tblSchema
EXECUTE SP_EXECUTESQL @vcRoutineQuery
SET @vcTableQuery =
'SELECT ''create table ['' + so.name + ''] ('' + o.list + '')'' +
CASE WHEN tc.Constraint_Name IS NULL
THEN ''''
ELSE ''ALTER TABLE '' + so.Name + '' ADD CONSTRAINT '' + tc.Constraint_Name + '' PRIMARY KEY '' + '' ('' + LEFT(j.List, LEN(j.List)-1) + '')''
END
FROM ' + @vcDBName + '.SYS.SYSOBJECTS SO CROSS APPLY
(SELECT '' [''+column_name+''] '' + data_type +
CASE data_type
WHEN ''sql_variant'' THEN ''''
WHEN ''text'' THEN ''''
WHEN ''decimal'' THEN ''('' + cast(numeric_precision_radix AS VARCHAR) + '', '' + CAST(numeric_scale AS VARCHAR) + '')''
ELSE COALESCE(''(''+ CASE WHEN character_maximum_length = -1 THEN ''MAX'' ELSE CAST(character_maximum_length AS VARCHAR) END +'')'','''')
END + '' '' +

CASE
WHEN EXISTS (
SELECT id FROM SYSCOLUMNS
WHERE OBJECT_NAME(id)=so.name
AND name=column_name
AND COLUMNPROPERTY(id,name,''IsIdentity'') = 1
) THEN
''IDENTITY('' +
CAST(IDENT_SEED(so.name) AS VARCHAR) + '','' +
CAST(IDENT_INCR(so.name) AS VARCHAR) + '')''
ELSE ''''
END + '' '' +

(CASE
WHEN IS_NULLABLE = ''No'' THEN ''NOT ''
ELSE ''''
END ) + ''NULL '' +

CASE
WHEN INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT IS NOT NULL
THEN ''DEFAULT ''+ information_schema.columns.COLUMN_DEFAULT
ELSE ''''
END + '', ''
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('''')) o (list)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.Table_name = so.Name
AND tc.Constraint_Type = ''PRIMARY KEY''
CROSS APPLY (SELECT ''['' + Column_Name + ''], ''
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('''')) j (list)
WHERE xtype = ''U''
AND name NOT IN (''dtproperties'') ORDER BY so.name'
INSERT INTO ##tblSchema
EXECUTE SP_EXECUTESQL @vcTableQuery
IF @ntBackupExpireDay = 0 BEGIN
SET @vcFileName = @vcFilePath + 'Script_'+ @vcDBName +'.txt'
END ELSE BEGIN
SET @vcFileName = @vcFilePath + 'Script_'+ @vcDBName + '_' + REPLACE(LOWER(REPLACE(
CONVERT(VARCHAR,GETDATE(),113),SPACE(1),'_')),':','_') + '.txt'
END
SET @vcBackupQuery = 'BCP "SELECT * FROM ##tblSchema " QUERYOUT ' + @vcFileName + ' -T -c -t'

EXECUTE @ntSatatus = xp_cmdshell @vcBackupQuery , NO_OUTPUT
IF OBJECT_ID('TEMPDB.DBO.##tblSchema') IS NOT NULL
DROP TABLE ##tblSchema
IF @ntSatatus = 0 BEGIN
INSERT INTO tblBackupHistory(vcDbName,vcBackupFile)
VALUES(@vcDBName,@vcFileName)
END
IF @ntBackupExpireDay <> 0 BEGIN
UPDATE tblBackupHistory SET
btBackupExist = 0,
@vcRemoveOldBackupQuery = ISNULL(@vcRemoveOldBackupQuery + ';','') + 'DEL ' + vcBackupFile
WHERE vcDbName = @vcDBName
AND btBackupExist = 1
AND dtBackupData < GETDATE() - @ntBackupExpireDay
IF ISNULL(@vcRemoveOldBackupQuery,'') <> ''
EXECUTE @ntSatatus = xp_cmdshell @vcRemoveOldBackupQuery , NO_OUTPUT
END
SET @vcFileName = ''
SET @vcRemoveOldBackupQuery = ''
FETCH NEXT FROM curDb INTO @vcDBName
END
END TRY
BEGIN CATCH
IF OBJECT_ID('TEMPDB..##tblSchema') IS NOT NULL
DROP TABLE ##tblSchema
SELECT ERROR_MESSAGE()
END CATCH
END

Step 3: Enabling the xp_cmdshell:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE;
GO

Step 4: Create the sql job which will execute the above stored procedure daily basis:

USE [msdb]
GO

DECLARE @ExecuteProc AS NVARCHAR(MAX)
DECLARE @DbName AS NVARCHAR(50)

SET @DbName = N'master'
SET @ExecuteProc = N'EXECUTE MASTER.dbo.uspGenerateScript ''Db1,db2'',''C:\Backup\'',5'


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

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 @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SchemaBackup_Daily',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute procedure',
@step_id=1,
@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=@ExecuteProc,
@database_name=@DbName,
@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_jobschedule @job_id=@jobId, @name=N'SchemaBackupDailySchedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130228,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'869d50ce-503f-4b1f-b098-87eef455c840'
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:

GO

Note in the above script you have to change following things:

1.

SET @DbName = N'master'

Write down the database where you have created the stored procedure uspGenerateScript.

2.

SET @ExecuteProc = N'EXECUTE dbo.uspGenerateScript ''Db1,db2'',''C:\Backup\'',5'

Pass parameters to the stored procedures uspGenerateScript according to your requirements:

@vcDatabasesName: Name of databases delimited by comma for which you want to create schema backup.

@vcFilePath: File path where you want to keep the backups. It is necessary that file you have specified which exits. It will not create any folder etc. Also file path cannot have file name.