Post reply

Create Database Snapshot Dynamically

  • November 7, 2016 at 5:23 pm

    #314768

    Comments posted to this topic are about the item Create Database Snapshot Dynamically

  • November 16, 2016 at 6:48 am

    #1912772

    Thanks for the script.

  • November 16, 2016 at 8:26 am

    #1912815

    Nice Start, however.

    SELECT TOP(1)

    -- Get the path location by trimming the file name and file extension.

    LEFT(physical_name,LEN(physical_name) - (LEN([name]) + 4) )

    FROM sys.database_files

    WHERE [name] = @SourceDatabase -- Use the location of the existing files.

    This retrieves the Logical name and not the actual file name.

  • November 16, 2016 at 9:44 am

    #1912847

    Budd (11/16/2016)

    Nice Start, however.

    SELECT TOP(1)

    -- Get the path location by trimming the file name and file extension.

    LEFT(physical_name,LEN(physical_name) - (LEN([name]) + 4) )

    FROM sys.database_files

    WHERE [name] = @SourceDatabase -- Use the location of the existing files.

    This retrieves the Logical name and not the actual file name.

    Hi Budd,

    Thank you for your feedback. I'm trying to retrieve the physical file path and not the logical file name. On MSDN this states it needs to be 'os_file_name', so I thought getting the physical would be the right thing to do?

    CREATE DATABASE database_snapshot_name

    ON

    (

    NAME =logical_file_name,

    FILENAME ='os_file_name'

    ) [ ,...n ]

    AS SNAPSHOT OF source_database_name

  • November 16, 2016 at 10:54 am

    #1912874

    Hi, Try something like this.

    SELECT DbBsName = mf.name

    ,PhysicalName = mf.physical_name

    ,LogicalName = db.name

    FROM sys.master_files mf

    JOIN sys.databases db ON db.database_id=mf.database_id

    WHERE db.state = 0

    AND mf.file_id = 1

  • November 17, 2016 at 2:57 am

    #1912989

    Ah! I see.

    Thank you, Budd 🙂

  • January 10, 2019 at 5:41 pm

    #2018647

    I came across your script and have implemented it as a system stored procedure. I use it to automatically generate snapshots before ETL routines and change scripts. I use the snapshot append parameter to pass in the change script number to make it easy for rolling back changes.

    I did make one change to the default parapeter for adhoc usage:


    SET @SnapshotAppend = ISNULL(@SnapshotAppend,'Snap_' + format(current_timestamp,'yyyyMMdd_HHmmss')).

    Thanks again for the script!

  • January 11, 2019 at 2:10 am

    #2018669

    That's great! I'm glad its helped, and thank you for letting me know.
    I'd like to add your change to the original post if you have no objections?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply