Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Restore Options and Commands Tutorial



By:

Overview

What good is a backup if you do not know to restore the backup.  In this tutorial we will look at what restore options are available and which options are only accessible using T-SQL commands.

As you will see there are many options that can be used, but just like the BACKUP commands there are just a few parts of the RESTORE command that are needed to do a successful restore.

Take the time to review each of these topics to get a good understanding of what is available.

You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.






More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, December 28, 2015 - 8:48:54 AM - Adnan Obaidulla Back To Top

Hi,

 

I am facing issue in stored procedure

 

USE [RP32TEST]
GO
/****** Object:  StoredProcedure [dbo].[AAAAAGPS]    Script Date: 12/28/2015 4:42:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AAAAAGPS]
 -- Add the parameters for the stored procedure here
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
declare @strquery as varchar(5000)
declare @routecode as nvarchar(50)
declare @weekcount as int
declare @filename as varchar(500)
declare @fileupdated as int
declare @Updatedate as date
declare @FileQuery as varchar (5000)
declare @filePath as nvarchar(max)
declare @companyCode as numeric (18)
declare @RouteCategory as numeric(18)
Declare @emailBody as nvarchar (500)

 SET NOCOUNT ON;

insert into ExcelFilePath (filename)
exec master..xp_cmdshell 'DIR E:\GPS\*.* /TC'

DELETE  FROM excelfilepath
WHERE   filename NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR filename LIKE '%<DIR>%'
OR filename IS NULL

update ExcelFilePath set Filename = Substring(Filename,40,100),Date = SYSDATETIME(),updated = 0

set @FileQuery = 'SELECT     Filename
FROM         ExcelFilePath
where updated = 0'

EXEC('DECLARE route_cursor1 CURSOR FOR ' + @FileQuery)
 
 OPEN route_cursor1
--Fetch first rec in givne variable

 FETCH NEXT FROM route_cursor1
 INTO @filename
  WHILE @@FETCH_STATUS = 0
  BEGIN   

set @filePath = 'Excel 12.0;IMEX=1;Database=E:\GPS\' + @filename
print(@filepath)

exec ('INSERT INTO New_temp_table
    -- Insert statements for procedure her
 
 select [ROUTE CODE],[VSR NAME],[USER ID],[CUST NO],[CUSTOMER NAME],[LATITUDE],[LONGTITUDE],[ANCUSTOMER]
from
( SELECT  a.[RT NO#] as RouteCode,a.[VSR Name] as NAME,a.[USER ID] as id,a.[CUST NO#],a.[CUSTOMER NAME],a.LAT,a.LONG,a[ANCUSTOMER]
 

FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''' + @filepath + ''',''
                select * from [JPLAN$]'') as a
                where  LAT > 0 )test
                 ')

   --- paste here

update ExcelFilePath set updated = 1 , UpdateDateTime = sysdatetime() where filename = @filename
declare @DelFilequery as varchar(300)
declare @DelFilePath as varchar(400)
DECLARE @hr int
DECLARE @ole_FileSystem int
set @DelFilePath = 'E:\GPS\' + @filename
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@ole_FileSystem OUT
EXEC @hr = sp_OAMethod @ole_FileSystem, 'DeleteFile',
NULL, @DelFilePath
EXEC @hr = sp_OADestroy @ole_FileSystem  

--update CustomerMaster set ActiveCustomer = 1 where CustomerCode in (select CustomerCode from RouteSequence )
--update CustomerMaster set ActiveCustomer = 0 where CustomerCode not in (select CustomerCode from RouteSequence ) and ActiveCustomer = 1
FETCH NEXT FROM route_cursor1
   INTO @filename

 

 
              
  END --while
 


CLOSE route_cursor1
DEALLOCATE route_cursor1

END

Regards

Adnan

 

 


Learn more about SQL Server tools