Use database snapshots in SQL Server to complete a lightweight backup restore of a database in a test environment

Original: A lightweight backup restore operation that uses database snapshots in SQL Server to complete a database in a test environment

In the development or testing environment of the database, often found that there are developers or testers mistakenly delete the table or data situation,
for development or test libraries, there is usually no scheduled backup task to back up the database,
On the one hand, because of the limited storage resources, it is unlikely to prepare a large amount of storage space for the development or test environment.
The second is that the necessity is not very strong, the database object of the development or test library changes too much, by restoring the backup way it is possible to flush out its newly created database object.
But the problem that has to be faced is when someone else "forgets where to add" when performing an update or a delete operation.

This is a pain point in the development or testing environment, I believe most people have experienced it,
When faced with the developers to say "I just delete the table when I forgot to add where conditions, the results are all deleted, can you restore it?" "The question of time,
In fact, a little awkward, can only tell that cannot be restored, SQL Server does not support the Flashback feature, and because the test database is not backed up, there is no way.
At this time, developers will often report to despise + helpless eyes, as a fake dba, really difficult to help.

In order to effectively solve this problem, at the same time can be achieved at a minimum cost, you may periodically give the test library to take a snapshot of this lightweight way to solve the problem of accidental deletion of data.
One is that the snapshot takes up little space and
Second, you can restore the data of a single table in a lightweight way
Took some time to write a full instance of the user database to generate snapshot operations, you can schedule a scheduled task to complete the timing of the database to generate a snapshot of the way to make a lightweight backup.
Therefore, it is convenient to solve the situation where the update or delete operation "forgot the Where condition" mistakenly deletes the data or the table.

By timing the database snapshot of the development environment, in the case of accidental deletion of data, you can restore the data mistakenly deleted according to the data in the recent snapshot, and can delete the created expired snapshot according to the actual situation.

Simple test, should be able to achieve the goal, did not do a complete test, the principle is very simple, double-layer while loop, is to cycle through each database corresponding to the physical file, dynamically generate a database snapshot of the statement
This approach is only suitable for lightweight backup of development or test environment databases and is not suitable for production environments.

--to generate a database snapshot under a full instanceCREATE proc [dbo].[Createsnapshotforbackup](    --Database Snapshot file location    @p_FilePath varchar( -),    --keep snapshots created within the last n hours, in hours    @p_RetainTime int) asbegin    SetNocount on; Declare @strSql            varchar( -)    Declare @strDatetime    varchar( -)    Declare @strDBFileName    varchar( $)    Set @strDatetime =  Replace(Replace(Replace(CONVERT(varchar,getdate(), -),'-',"'),' ',"'),':',"')        if object_id('tempdb: #databaseName') is  not NULL    begin         Drop Table#databaseNameEnd    Selectname into#databaseName fromsys.databaseswheredatabase_id>6  andsource_database_id is NULL         Declare @databaseName varchar( $); Declare @databaseCnt int; Select @databaseCnt = Count(1) from#databaseName;  while @databaseCnt>0    begin        Select Top 1 @databaseName =Name from#databaseNameif object_id('tempdb: #dbFiles') is  not NULL        begin             Drop Table#dbFilesEnd        SelectConcat'(','name =', Name,', FileName =" ",@p_FilePath, Name,'_',@strDatetime,'. SS"' )') asstrFileName into#dbFiles fromSys.sysaltfileswheredbid=db_id(@databaseName) andStatus= 2        DECLARE @dbFileCnt int = 0; SELECT @dbFileCnt = COUNT(1) from#dbFiles while @dbFileCnt>0        begin            Select Top 1 @strDBFileName =strFileName from#dbFiles; Set @strSql=CONCAT (@strSql,',',@strDBFileName,Char(Ten)); Delete Top(1) from#dbFiles; Select @dbFileCnt = Count(1) from#dbFiles; End        Set @strSql=Stuff(@strSql,1,1,"')        Set @strSql =CONCAT ('CREATE DATABASE',@databaseName,'_',@strDatetime,Char(Ten),                             ' on',Char(Ten),@strSql                             ,'As snapshot of',@databaseName)        beginTryPrint @strSql            exec(@strSql)            Select @databaseName+'Snapshot Create successful:'+@databaseName+@strDatetime            Set @strSql = "'        EndTrybeginCatchSelect 'Snapshot Create fail'ThrowEndCatchDelete Top(1) from#databaseNameSelect @databaseCnt = Count(1) from#databaseNameEnd    --Delete an expired db snapshot    beginTryif object_id('tempdb: #snapshotname') is  not NULL            Drop Table#snapshotnameSelectname into#snapshotname fromsys.databaseswheresource_database_id is  not NULL  andCreate_date<DateAdd(HH,-@p_RetainTime,getdate()); Declare @cnt int = 0; Declare @strDBName varchar( $)= "'        Declare @strDropDatabase varchar(Max)=  "'        Select @cnt = Count(1) from#snapshotname while @cnt>0        begin            Select Top 1 @strDBName =Name from#snapshotname; Set @strDropDatabase = 'DROP Database' +@strDBName; Print @strDropDatabase            exec(@strDropDatabase); Delete Top(1) from#snapshotname; Select @cnt = Count(1) from#snapshotnameEnd    EndTrybeginCatchSelectN'Snapshot Delete fail'ThrowEndCatchEnd

To be honest, we have completely forgotten the syntax of T-SQL Midstream, and by the way of a while loop temporary table can also achieve the effect of the cursor loop, and this syntax is more clear and simple structure of logic, do not forget, hehe.

Use database snapshots in SQL Server to complete a lightweight backup restore of a database in a test environment