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.

@ntBackupExpireDay: For how may days old schema backup file will not be deleted.

How to recover deleted data from table without using database backup 
Set up auto only schema or script backup in sql server
Sql server export stored procedures