Click here to Skip to main content
15,937,127 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to create <pre lang="c#"></pre>Database backup in sql server 2012 with pass
Posted
Comments
CHill60 19-Feb-15 7:21am    
What on earth is all that code for? It has nothing to do with your question
King Fisher 21-Feb-15 7:38am    
its looks terrible .
Project CSE 15-Mar-15 3:11am    
CREATE FUNCTION [dbo].[Split]
( @Delimiter varchar(5),
@List varchar(8000)
)
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
[Value] varchar(50)
)
AS
BEGIN

DECLARE @LenString int

WHILE len( @List ) > 0
BEGIN

SELECT @LenString =
(CASE charindex( @Delimiter, @List )
WHEN 0 THEN len( @List )
ELSE ( charindex( @Delimiter, @List ) -1 )
END
)

INSERT INTO @TableOfValues
SELECT substring( @List, 1, @LenString )

SELECT @List =
(CASE ( len( @List ) - @LenString )
WHEN 0 THEN ''
ELSE right( @List, len( @List ) - @LenString - 1 )
END
)
END

RETURN

END

GO
Project CSE 15-Mar-15 3:11am    
CREATE FUNCTION [dbo].[GetAttInfo](@AttValue [varchar](50), @WhichOne [int])
RETURNS [varchar](50) WITH EXECUTE AS CALLER
AS
begin
set @AttValue=Ltrim(Rtrim(@AttValue))
Declare @Name as VARCHAR(150)
DECLARE @Count AS INT

SET @Count=( SELECT COUNT(*) from [dbo].[Split](',',@AttValue))

if @Count >= @WhichOne
Begin
set @Name=(SELECT [Value] from [dbo].[Split](',',@AttValue) WHERE RowID=@WhichOne)
End
Else
begin
set @Name=''
end

return @Name
end

GO
Project CSE 15-Mar-15 3:12am    
-----------------------XXXXXXXXXX---------------------------------

CREATE PROCEDURE [dbo].[SP_AttendanceDataProcessText]
@file_Path AS NVARCHAR (500)=null,
@userName nvarchar(50)=null

--With Encryption
AS

Set nocount ON

if isnull(@file_Path,'')=''
Begin

select @file_Path=AttendanceAppPath+cast(year(getdate()) as varchar(4))+Right('00'+cast(month(getdate()) as varchar(4)),2)+'\'++Right('00'+cast(day(getdate()) as varchar(4)),2)+'.txt' from BranchModuleFor
set @userName='AutoProcess'
End


DECLARE @file_exists AS INT

SET NOCOUNT ON

Declare @SadPIN nvarchar(8),@MID nvarchar(10),@attTime DATETIME,@Attdate DATETIME
declare @LastTime as int
select @LastTime=datepart(hh,UploadTime)*10000+datepart(mi,UploadTime)*100+datepart(ss,UploadTime) from [dbo].[CompanyDailyAttendanceLog] where year(convert(DateTime,UploadDate,103))*10000+month(convert(DateTime,UploadDate,103))*100+day(convert(DateTime,getdate(),103))= year(convert(DateTime,UploadDate,103))*10000+month(convert(DateTime,getdate(),103))*100+day(convert(DateTime,getdate(),103))

EXEC [master].dbo.xp_fileexist @file_Path,@file_exists OUTPUT
IF @file_exists != 0
BEGIN
DECLARE @strSQL VARCHAR (MAX)
set @strSQL=''
CREATE TABLE #tempAttendanceData(line VARCHAR(50))
CREATE TABLE #tempAttendanceRecord(EmpID VARCHAR(8),AttDate DateTime,AttTime DateTime,TerminalID varchar(10))

SET @strSQL = 'bulk Insert #tempAttendanceData from ' + ''''+@file_Path+''''
EXEC (@strSQL)
Insert into #tempAttendanceRecord
Select Right('00000000'+ [dbo].[GetAttInfo](line, 5),8)EmpID,Convert(DateTime,[dbo].[GetAttInfo](line, 3),103)AttDate,Convert(DateTime,[dbo].[GetAttInfo](line, 3)+ ' ' + SUBSTRING([dbo].[GetAttInfo](line, 4),1,2)+':'+ SUBSTRING([dbo].[GetAttInfo](line, 4),3,2)+':'+ SUBSTRING([dbo].[GetAttInfo](line, 4),5,2),103)AttTime, [dbo].[GetAttInfo](line, 6)TerminalID from #tempAttendanceData
inner join StaffStatus on SsPIN=Right('00000000'+ [dbo].[GetAttInfo](line, 5),8)

INSERT INTO StaffAttendanceDetails
Select EmpID ,AttDate,AttTime,'P',TerminalID from #tempAttendanceRecord
inner join StaffStatus on SsPIN=EmpID

if not exists(Select * from [dbo].[CompanyDailyAttendanceLog] where year(convert(DateTime,UploadDate,103))*10000+month(convert(DateTime,UploadDate,103))*100+day(convert(DateTime,getdate(),103))= year(convert(DateTime,getdate(),103))*10000+month(convert(DateTime,getdate(),103))*100+day(convert(DateTime,getdate(),103)))
Begin
INSERT INTO [dbo].[CompanyDailyAttendanceLog]([UploadDate],[UploadTime],[UserName]) VALUES(convert(DateTime,getdate(),103),getdate(),@userName)
End
Else
Begin
Update [dbo].[CompanyDailyAttendanceLog]
set [UploadTime]=getdate()
where year(convert(DateTime,UploadDate,103))*10000+month(convert(DateTime,UploadDate,103))*100+day(convert(DateTime,getdate(),103))= year(convert(DateTime,UploadDate,103))*10000+month(convert(DateTime,getdate(),103))*100+day(convert(DateTime,getdate(),103))
End

I don't quite understand why you posted the create scripts for the database but if you want to take a backup, follow the instructions in Create a Full Database Backup (SQL Server)[^]
 
Share this answer
 
v2
Quote:
Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups.
How to create full database backup in SQL 2012[^]

Quote:
The WITH PASSWORD feature didn't really encrypt your backup. It just made it difficult for someone to accidentally restore the backup when they were not allowed to and the password option was weak and could be broken easily. The only true way to protect the data is to use a 3rd party backup tool that creates an encrypted backup or to backup to an encrypted media. For sensitive data you should be backing up to encrypted tape anyway as tapes easily get lost and go missing.

Otherwise you can encrypt the data at the source by encrypting the database.
Sean Massey on the SQL Forum[^]
 
Share this answer
 
Comments
Project CSE 1-Mar-17 5:08am    
DA=?=?@=@@?=<>=@ZA>==?@=>??3B?===@=@?>?@1@C=>>>@<@@<=??-CD=@=>@>@?=>>@<=@@M>A=<>=>???aDD=??><=<?=<<@@<=<@rDD=@<<@<?=>>>?=?<>>-BD=@==?<>@=><=@@?2?D=??>><><?>?<0CB==<<<=>>@><=@>1>B=?=<=@@==7CC=@?@@>@><???<>>A
CHill60 1-Mar-17 11:25am    
What?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900