schema only backups

Excerpt from http://www.sqlsoldier.com/wp/sqlserver/schemaonlybackupsandrestores

Schema-only Backups

Alternatively, there is an option for exporting a data-tier application which has lots of options and allows you to export only part of a database and includes the data. That’s a discussion for another day.If you right-click on a database and highlight Tasks in SQL Server 2008 and newer, you will see several options for the dac. We’re not interested in this post in most of those options. We’re only interested in “Extract Data-tier Application”. This task will export the data tier application with no data. It’s basically just the database as an empty shell. Extracting the dacpac is very simple and there aren’t really any options for it other than where to save it.

Restoring a Dacpac

So let’s suppose you had a very large database and planned to re-import the data from source systems if you lose the database. Now, let’s assume the database is completely lost, and you want to start over with an empty version for the database, or what we now might call the dacpac. Restoring via the command line is actually very easy as well. The steps to restore the dacpac would be:

  1. Right-click on the Databases node
  2. Click on Deploy Data-tier Application… (not import)
  3. Browse for and select the dacpac you want to deploy
  4. Click Next
  5. You have to delete the pre-existing version of the database, if there is one, or give it a different name to be able to deploy it
  6. Click Next
  7. View the Summary and click Next to start the deployment
  8. Click Finish if the deployment completed successfully.

default trace in SQL Server

–enabling default trace

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘default trace enabled’, 1;
GO
RECONFIGURE;
GO

–querying default trace
SELECT *
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
AND name = ‘Object:Deleted’