Reseeding MSSQL tables

Getting the current primary key seed value for a table

To get the next value from the primary key column of the database, either use the verbose descriptive CHECKIDENT method, or if you want to select it into a variable, use the IDENT_CURRENT function.

> DBCC CHECKIDENT ('TableName', NORESEED)
Checking identity information: current identity value '12345', current column value '56789'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

> SELECT IDENT_CURRENT('TableName')
12345

Updating the primary key seed for a table

You can use either CHECKIDENT again to set the value.

DBCC CHECKIDENT ('TableName', RESEED, 12345)

Updating the primary key seed automatically

DECLARE @tableName VARCHAR(255); SET @tableName = 'TableName';
DECLARE @currentIdentity INT; SET @currentIdentity = (SELECT IDENT_CURRENT(@tableName))
DBCC CHECKIDENT (@tablename, RESEED, @currentIdentity);

Creating database snapshots using Microsoft SQL Server

Finding data files

Before you create a snapshot you need to know the files used by the database. Here’s a query to get the current list of files associated with a mounted database:

SELECT
	db.name AS DBName,
	mf.name as [Filename],
	type_desc AS FileType,
	Physical_Name AS Location
FROM sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
WHERE db.name LIKE 'ExampleDatabase'

Example output:

DBname   Filename     FileType  Location
Contoso	 Contoso      ROWS      C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Contoso.mdf
Contoso	 Contoso_log  LOG       C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Contoso_log.ldf

Make a note of all the ROWS and FILESTREAM entries. You cannot create a snapshot of LOG entries.

If you want to use the SQL Server Management Studio GUI, right click on the database and click on the Files section.

Creating a snapshot

Make sure the folder exists and your credentials can write to it otherwise you’ll get an error. Remember this is all happening on the server the command is running on, and this might not be your local machine.

You can only have one snapshot for a database, and snapshots cannot chain be chained together. You cannot create a snapshot of a snapshot.

Creating a snapshot via T-SQL:

CREATE DATABASE [201607041516_ExampleDatabase_Snapshot]
ON
( NAME = 'ExampleDatabase_rows', FILENAME = 'F:\Snapshots\201607041516_ExampleDatabase_Snapshot\ExampleDatabase_rows.ss' ),
( NAME = 'ExampleDatabase_files', FILENAME = 'F:\Snapshot\201607041516_ExampleDatabase_Snapshot\ExampleDatabase_files.ss' )
AS SNAPSHOT OF ExampleDatabase

Make sure to add an entry for each data file.

Restoring a snapshot

When you’ve got a snapshot and need to restore a database to it, use the below SQL.

The “WITH ROLLBACK IMMEDIATE” statement rolls back any currently open transactions. The database must be in single user mode to be restored. After it’s been restored, it’s set to multi-user mode.

USE master; 

ALTER DATABASE ExampleDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE ExampleDatabase from 
DATABASE_SNAPSHOT = '201607041516_ExampleDatabase_Snapshot';
GO

ALTER DATABASE ExampleDatabase
SET MULTI_USER;
GO