How to List Snapshot Data Files in SQL Server

snapshotsql serversql-server-2012t-sql

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from within a query.

Essentially we have several environments and they are not consistent on where they place these files so I need my code to be smart enough to put these files back where they were.

Just to clarify: When I say the snapshot files I mean the .ss files.

Best Answer

You can identify database snapshots in sys.databases by the column source_database_id being not null.

Join it to sys.master_files and you're done:

SELECT mf.database_id,
    dbs.source_database_id,
    mf.type_desc,
    mf.name, 
    mf.physical_name
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
    ON mf.database_id = dbs.database_id
WHERE source_database_id IS NOT NULL

In case you need to filter for one specific source database, you can add that predicate to the WHERE clause.

Related Solutions

Sql-server – Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS 
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.

Is possible do an online snapshot backup , without archive logs at Oracle Database

Yes, you can do an online backup without archivelogs if you have a storage system or backup tool that can take a point in time snapshot that will be consistent across all your datafiles and control files. It sounds like you have such a tool and are using it for cold backups. If that is the case, your documentation will still need to change. If there is a problem with the system and you need to restore the backup, starting up the restored database will be as though the power plug was pulled a the time of the backup, therefore crash recovery will likely be necessary.

Given that your documentation needs to change anyway and that you want zero downtime backups, you should probably re-think your resistance to archive logs. It limits your recovery possibilities greatly. For example, if due to a disk error you get a bad block in one of your datafiles, and you have older consistent copies of that datafile, you can recover it using archive logs. With snapshots taken online you would probably end up doing lengthy extracts from multiple snapshots of the system (assuming you can tolerate the downtime) and would probably still loose data.

Best Answer

Related Solutions

Sql-server – Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

Is possible do an online snapshot backup , without archive logs at Oracle Database

Related Question