Man!!!! Why is the code to create a database snapshot so ridiculously complicated? Actually, I guess I understand why but how about allowing it to be done through SSMS and then setting a bunch of the parameters as defaults for us? Come on Microsoft! Help a SQL brother out!<\/p>\n
Well luckily, where Microsoft fails, I'm there for you. Here's my brilliant proc which will automatically create a database snapshot for you. It places the snapshot in the same physical location as the physical file in the snapshot. I think this will be fine in most environments but if you're running tight on disk space or expect your snapshots to get pretty big you've been warned!<\/p>\n
I create the proc in a database to hold various utility scripts. I creatively called my database: DBA. Catchy right?<\/p>\n
USE DBA\r\nGO\r\n\r\n\r\nCREATE PROCEDURE usp_CreateDBSnapshot @DBName sysname\r\nAS \r\n\r\n\/******************************************************************\r\nSample exec:\r\nexec usp_CreateDBSnapshot 'MyDatabaseName'\r\n\r\n2016-07-29\u00a0\u00a0 \u00a0Steffek\u00a0\u00a0 \u00a0Initial Version. sqldbpros.com\r\n******************************************************************\/\r\n\r\n\u00a0\u00a0\u00a0 DECLARE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sql NVARCHAR(MAX)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0-- holds dynamic sql\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Params NVARCHAR(MAX)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0-- hold dynamic sql parameters\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @SnapshotDate VARCHAR(8)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , -- current date\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @DBFiles NVARCHAR(MAX)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0-- holds list of data files and paths to create\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @DBSnapshotName NVARCHAR(MAX);\r\n\r\n\r\n\u00a0\u00a0\u00a0 SET @SnapshotDate = CONVERT(VARCHAR(8), GETDATE(), 112)\r\n\u00a0\u00a0\u00a0 SET @DBSnapshotName = @DBName + '_Snapshot_' + @SnapshotDate\r\n\r\n-----------------------------\r\n-- get snapshot files from database_files\r\n-----------------------------\r\n\u00a0\u00a0\u00a0 SELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sql = N'\r\n\r\n\u00a0\u00a0 \u00a0SELECT @DBFiles = \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0(SELECT '','' +\u00a0 ''(Name = '' + NAME + '', FILENAME = '' + CHAR(39) +\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 REVERSE(SUBSTRING(REVERSE(physical_name)--Reverse the string since the data we want to remove is at the end.\r\n\u00a0\u00a0 \u00a0, PATINDEX(''%.%'', REVERSE(physical_name)) + 1--parse off the file extension by looking for the period.\r\n\u00a0\u00a0 \u00a0,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 len (physical_name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )) +''_'' +\u00a0 @SnapshotDate + ''.ss'' + CHAR(39) +\u00a0 '')''\r\n\u00a0\u00a0 \u00a0FROM ' + @DBName + '.sys.database_files\r\n\u00a0\u00a0 \u00a0WHERE TYPE = 0\r\n\u00a0\u00a0 \u00a0FOR XML PATH('''') )\u00a0 '\r\n\r\n--PRINT @sql\r\n\r\n\u00a0\u00a0\u00a0 SELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Params = N'@DBFiles nvarchar(MAX) OUTPUT, @SnapshotDate varchar(8)'\r\n\r\n\u00a0\u00a0\u00a0 EXEC sp_executesql\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sql\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,@Params\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,@DBFiles = @DBFiles OUTPUT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,@SnapshotDate = @SnapshotDate;\r\n\r\n\u00a0\u00a0\u00a0 SELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sql = N'\r\nUSE ' + @DBName + ';\r\n\r\nCREATE DATABASE ' + @DBSnapshotName + '\r\nON ' + STUFF(@DBFiles, 1, 1, '') + '\r\nAS SNAPSHOT OF ' + @DBName + ';'\r\n\r\n-------------------\r\n-- create snapshot\r\n-------------------\r\n--PRINT @Sql\r\n\r\n\u00a0\u00a0\u00a0 EXEC sp_executesql\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sql \r\n\u00a0\u00a0\u00a0 PRINT 'created database snapshot: ' + @DBSnapshotName\r\n\r\n<\/pre>\n <\/p>\n
What do you think? Handy? Suggested improvements? Bring it on!<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Man!!!! Why is the code to create a database snapshot so ridiculously complicated? Actually, I guess I understand why but how about allowing it to be done through SSMS and then setting a bunch of the parameters as defaults for us? Come on Microsoft! Help a SQL brother out! Well luckily, where Microsoft fails, I'm […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[86,43,3,51],"tags":[10,6,191,9,12,11,117,173],"class_list":["post-883","post","type-post","status-publish","format-standard","hentry","category-dba","category-microsoft-sql-server","category-scripts","category-sql-server-management-studio","tag-ms-sql","tag-script","tag-snapshot","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2012","tag-t-sql"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=883"}],"version-history":[{"count":1,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/883\/revisions"}],"predecessor-version":[{"id":884,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/883\/revisions\/884"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=883"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}