Restore all the backup files from inside a folder on SQL Server – TSQL Script

First, you will need to enable xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Next, you will need to change the path to the directories:

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

-- 2 - Initialize variables 
SET @backupPath = 'F:\baks\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

SELECT 'RESTORE DATABASE [' + LEFT(backupFile, CHARINDEX('_', backupFile, 12)-1) + 
'] FROM DISK = '''  
       + @backupPath + backupFile + ''' WITH RECOVERY, REPLACE
	 GO'  from @fileList

Copy the generated script into a new window and voila! Your script is done. Just press F5 to run it.
Don’t forget to turn off xp_cmdshell when done.

Advertisements

How to script all indexes from a table (or an entire database)

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' +    
    I.name  + '] ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    --' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , [' + C.name  + ']'
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   WHERE IC2.Object_id = object_id('tblERPInvoiceData') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Continue reading “How to script all indexes from a table (or an entire database)”

Upload File to FTP Server via Command Shell

How to FTP using Windows Command Prompt

Go to the Start Menu and Click Run…
kb-global-start-run

In the Run dialog box, type cmd and click OK

kb-global-cmd-300x148

The Windows Command Prompt will open. Type the following command to connect to your server:

ftp yourdomain.com

Where yourdomain.com is your domain or the FTP Server IP Address found in the HELM Control Panel under the FTP Account Details.

kb-cmdftplogin

Once you hit Enter it will attempt to connect to the server. If it is successful, you will be prompted for a Username and Password. Enter the FTP username and password information to login.

Once you have Logged in, you should see a line like ftp>

To list the Directory Contents, use the command dir

kb-cmdftplogindir-300x149

You have successfully connected to your server through FTP using Windows Command Prompt.

More FTP Commands:

ABOR – abort a file transfer
CWD – change working directory
DELE – delete a remote file
LIST – list remote files
MDTM – return the modification time of a file
MKD – make a remote directory
NLST – name list of remote directory
PASS – send password
PASV – enter passive mode
PORT – open a data port
PWD – print working directory
QUIT – terminate the connection
RETR – retrieve a remote file
RMD – remove a remote directory
RNFR – rename from
RNTO – rename to
SITE – site-specific commands
SIZE – return the size of a file
STOR – store a file on the remote host
TYPE – set transfer type
USER – send username

Automating File Uploads

Create a commands file (in my example it is named commands.txt).

Type the following in your commands.txt:

open FTP_ADDRESS
FTP_USERNAME
FTP_PASSWORD
put FILENAME

!del FILENAME
bye

Any commands starting with “!” will be executed on the local server rather than the remote server. Remove the line marked in Red if you would like to keep the files on the source server after the upload has been finished.

To execute these commands type in the windows command prompt:

ftp -s:commands.txt

This command will execute the script.

If you would like to automate the file upload, create a MS-DOS batch file with notepad ( extension needs to be .cmd or .bat) and save the above FTP command. You can now use this file to create scheduled tasks and run it at specific intervals.

To find out how to use VBScript instead, check out yesterday’s article on FTP File Uploads.

Upload file to FTP Server via VBS Script

We always requirements to put files on a server without installing additional software on the source server.

An easy way to accomplish this is by using VBS (Visual Basic Scripts).

 

Using Chilkat FTP

One method is to use the Chilkat FTP Software (1 license costs about $289). You can install a 30-day trial version by clicking here.

 

Dim fso, outFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set outFile = fso.CreateTextFile("output.txt", True)

' This CreateObject statement uses the new single-DLL ActiveX for v9.5.0
set ftp = CreateObject("Chilkat_9_5_0.Ftp2")

'  Any string unlocks the component for the 1st 30-days.
success = ftp.UnlockComponent("Anything for 30-day trial")
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

ftp.Hostname = "ftp.chilkatsoft.com"
ftp.Username = "****"
ftp.Password = "****"

'  The default data transfer mode is "Active" as opposed to "Passive".

'  Connect and login to the FTP server.
success = ftp.Connect()
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

'  Change to the remote directory where the file will be uploaded.
success = ftp.ChangeRemoteDir("junk")
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

'  Upload a file.
localFilename = "c:/temp/hamlet.xml"
remoteFilename = "hamlet.xml"

success = ftp.PutFile(localFilename,remoteFilename)
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

ftp.Disconnect

MsgBox "File Uploaded!"
outFile.Close

Free Version

The example VBS script below shows how to upload and save a local file to a remote webserver using Microsoft VBS scripting. The upload method in this example is HTTP PUT, meaning that the webserver needs to be WebDAV-enabled. Since file upload is typically password-protected, the script implements access with basic authentication.

'======================================================================
' https-upload.vbs 1.0  @2009 by Frank4dd http://www.frank4dd.com/howto
' This script demonstrates a file upload to a WebDAV enabled webserver,
' using https (and proxy settings from IE) with basic web authentication
'
' Original authors and code references:
' - "ASP - File upload with HTTP Put" by Martin Clark
'
' This program comes with ABSOLUTELY NO WARRANTY. You may redistribute
' copies of it under the terms of the GNU General Public License.
'======================================================================

'======================================================================
' Global Constants and Variables
'======================================================================
Const scriptVer  = "1.0"
Const UploadDest = "https://mywebdavserver.com/uploadurl"
Const UploadFile = "localpath-and-file"
Const UploadUser = "username"
Const UploadPass = "password"
Const UploadType = "binary"
dim strURL

function sendit()
  sData = getFileBytes(UploadFile, UploadType)
  sfileName= mid(UploadFile, InstrRev(UploadFile,"")+1,len(UploadFile))

  dim xmlhttp
  set xmlhttp=createobject("MSXML2.XMLHTTP.3.0")
  strURL = UploadDest & "/" & UploadFile
  msgbox "Upload-URL: " & strURL
  xmlhttp.Open "PUT", strURL, false, UploadUser, UploadPass
  xmlhttp.Send sData
  Wscript.Echo "Upload-Status: " & xmlhttp.statusText
  set xmlhttp=Nothing
End function

function showresult()
  Wscript.Echo "Complete. Check upload success at: " & strURL
end function

function getFileBytes(flnm, sType)
  Dim objStream
  Set objStream = CreateObject("ADODB.Stream")
  if sType="binary" then
    objStream.Type = 1 ' adTypeBinary
  else
    objStream.Type = 2 ' adTypeText
    objStream.Charset ="ascii"
  end if
  objStream.Open
  objStream.LoadFromFile flnm
  if sType="binary" then
    getFileBytes=objStream.Read 'read binary'
  else
    getFileBytes= objStream.ReadText 'read ascii'
  end if
  objStream.Close
  Set objStream = Nothing
end function

'=======================================================================
' End Function Defs, Start Main
'=======================================================================
' Get cmdline params and initialize variables
If Wscript.Arguments.Named.Exists("h") Then
  Wscript.Echo "Usage: https-upload.vbs"
  Wscript.Echo "version " & scriptVer
  WScript.Quit(intOK)
End If

sendit()
showresult()
Wscript.Quit(intOK)
'=======================================================================
' End Main
'=======================================================================

Script to check if websites are running

If you have one or more websites online, you would not want to check their online status every morning, you would like to create a script that will check this for you.
We have thought of this and created this ASP.NET script that will read the websites from a file and then check the status for each.

 

<%@ Page Language=”VB” MasterPageFile=”MasterPage.master” AutoEventWireup=”false” title=”Software courses!” %>
<%@ Import Namespace=”System.Io” %>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) handles me.load
if not page.isPostBack then
dim dr as new streamReader(“websites.txt”)
txtSites.text = dr.ReadToEnd
dr.close
dr.dispose
end if

end Sub

Protected Sub btnDone_click(ByVal sender As Object, ByVal e As ImageClickEventArgs) handles btnDone.click
dim websites() as string
lblMessage.text =””
websites = txtSites.text.Split(new char(){vbCrlf})
if websites.length>0
for i as integer=0 to websites.length-1
lblMessage.text += websites(i)
if websites(i).trim”” then
IF CheckWebsiteExists(websites(i).trim) then
lblMessage.text += ” – up and running”
else
lblMessage.text += ” – down”
end if
lblMessage.text += “

end if
next
end if
End Sub

Private Function CheckWebsiteExists(ByRef website As String) As Boolean
‘check url
If website = “” Then Return True
Dim netCall As System.Net.HttpWebRequest
Try
netCall = System.Net.HttpWebRequest.Create(website)
Catch ex1 As Exception
If Not website.Contains(“http”) Then
While website.StartsWith(“/”)
website = Right(website, website.Length – 1)
End While
website = “http://&#8221; & website
End If
Try
netCall = System.Net.HttpWebRequest.Create(website)
Catch ex As Exception

End Try
End Try

Dim resp As System.Net.HttpWebResponse = Nothing
Try
resp = netCall.GetResponse()
If resp.StatusCode = System.Net.HttpStatusCode.OK Then
‘MsgBox(“Found file”)
End If
Catch
If resp Is Nothing Then
lblError.Text = “Specified URL was not found! Please re-check the URL address!”
lblError.Visible = True
Return False
End If
If resp.StatusCode = System.Net.HttpStatusCode.NotFound Then
lblError.Text = “Specified URL was not found! Please re-check the URL address!”
lblError.Visible = True
Return False
End If
End Try
Return True
End Function

<asp:Content ID=”Content3″ ContentPlaceHolderID=”ContentPlaceHolder2″ Runat=”Server”>
Please enter the websites to check in the textbox below separated by enter (one on each line) and press done!
<br />
<asp:textbox runat=”server” id=”txtSites” textMode=”MultiLine” height=”400px” width=”500px” />
<br />
<asp:imagebutton runat=”server” id=”btnDone” imageUrl=”images/done.png” ToolTip=”Start” />
<br />
<asp:label runat=”server” id=”lblMessage” text=”” cssClass=”SubTitle” />
<asp:label runat=”server” id=”lblError” text=”” cssclass=”ErrorText” />
</div>
</asp:Content>