Tag: SQL

If you need to get the last time that a database is restored from log shipping based off the transaction log file name, here is a function to do so. It parses out the date from the "last_restored_file" column, then converts it from UTC to your regular time.

CREATE FUNCTION [dbo].[GetLogShippingFileDate]
(
    @DatabaseName varchar(100)
)
RETURNS smalldatetime
AS
BEGIN

    DECLARE @Result smalldatetime

    SELECT  @Result =
    CAST(SUBSTRING(fds,5,2)+’/’+SUBSTRING(fds,7,2)+’/’+LEFT(fds,4) + ‘ ‘ + SUBSTRING(fds,9,2) + ‘:’ + SUBSTRING(fds,11,2) AS SMALLDATETIME)
    FROM (
    SELECT secondary_database AS  ‘DatabaseName’,
    LEFT(RIGHT(last_restored_file,18),14) AS ‘fds’
        FROM  
        msdb.dbo.log_shipping_secondary_databases
    ) a
    WHERE DatabaseName = @DatabaseName

    — convert the UTC file time to regular time
    SELECT @Result = DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),@Result)

    RETURN @Result

END

I am sure there are other SQL procedures that take parameters the same way, but I recently have been dealing with sp_add_job. You can call the procedure and pass in parameters

sp_add_job @job_name = ‘My Job Name’

for an example. But..you cannot do this

DECLARE @test AS VARCHAR(50)

SET @test = ‘ Test ‘

sp_add_job @job_name = ‘My Job Name’ + @test

You will get an error, because you cannot concatonate a string while passing it in as a parameter. What you need to do is this.

DECLARE @test AS VARCHAR(50)

SET @test = ‘My Job Name Test’

sp_add_job @job_name = @test

* Note there are other params you need to pass to the procedure, but I just passed the one for example’s sake

So today, Chris asked me how to move log files or data files on SQL to another drive. My first response was to use the GUI and just detach and then move the files, reattach using the GUI. But he wanted to move just the log files, I don’t think it is very intuitive with the GUI, if even possible.

The KB article from Microsoft shows you how to do with SQL commands (sp_attach_db and sp_detach_db), which is really the correct way, although, it doesn’t say how to do it if you have multiple log files or data files.

Books online shows us:

sp_attach_db [ @dbname= ] ‘dbname’         , [ @filename1= ] ‘filename_n’ [ ,…16 ]

After all that, you should be good to, moving your data and log files around all you want! And as a side note, if you have SQL 2000, use Query Analyzer to connect and run the commands, if you have SQL 2005, use SQL Server Management Studio (SSMS) or SQL Server Management Studio Express (SSMSE) to connect and run the commands.

As a short follow up to a post I did a little while ago on SELECT’ing the instance and machine names from SQL Server, here is how you get the Current Database Name

SELECT db_name()

Funny because I was looking for that online, and my Google search led me to my past blog post, so I figured I would actually blog about this specific function as well.

to get the instance name: SELECT @@ServerName

to get the machine name of the server: SELECT SERVERPROPERTY(“MachineName”)

to get the machine name of the computer running the query: SELECT host_name()

one tip: cast the serverproperty as a varchar if you want to use it in a string or you will get errors in your stored procs/queries.