Jump to content

SQL Script to quickly backup and restore Passwordstate database


support

Recommended Posts

Here at Click Studios we frequently do testing of certain features, and we use a handy script to quickly restore your database.  We thought we'd share this script with our community which can help speed up testing.

 

An example when we use this script is we are writing an API script to import bulk passwords into the system, but we did something wrong in our script and it imported a whole bunch of incorrect data for what ever reason.  We then decide to roll back the database to the state it was in just prior to running the script, and then test our API script again until we get it right.

 

Running the script below only takes a few seconds and although there are other ways to do this, we find this is the quickest way.

 

How to Use?

First, on your database server, create a folder called Passwordstate in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup.

 

Now run the following SQL scripts with SQL Management Studio Tools, when connected to your database server.

 

SQL Script 1 - Backup your database as a once off process just prior to performing your testing

 

-------------------------------------------------------------------------------------------------------------------------------------
-- Database Backup
-------------------------------------------------------------------------------------------------------------------------------------
USE passwordstate
DECLARE @BackupSQLScript nvarchar(max)
DECLARE @DatabaseName sysname
DECLARE @SQLBackupFileName nvarchar(400)
DECLARE @SQLBackupFolder nvarchar(400)

SET @DatabaseName = db_name() --DB name is current DB of user (better than hard coding in case you change DB name in future versions) 
SET @SQLBackupFileName = 'passwordstate\' + @DatabaseName + '.bak'
SET @BackupSQLScript = ' BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @SQLBackupFileName + ''' WITH FORMAT'
EXEC sp_executesql @BackupSQLScript

 

 

 

SQL Script 2 - Run this script to revert your database to the state your database was in just before you started testing, ie roll back to the backup performed in SQL Script 1, above.  Repeat this as necessary

 

 

USE passwordstate
DECLARE @BackupSQLScript nvarchar(max)
DECLARE @DatabaseName sysname
DECLARE @SQLBackupFileName nvarchar(400)
DECLARE @SQLBackupFolder nvarchar(400)

SET @DatabaseName = db_name() --DB name is current DB of user (better than hard coding in case you change DB name in future versions) 
SET @SQLBackupFileName = 'passwordstate\' + @DatabaseName + '.bak'
SET @BackupSQLScript = ' BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @SQLBackupFileName + ''' WITH FORMAT'

-------------------------------------------------------------------------------------------------------------------------------------
-- Take the Database Offline
-------------------------------------------------------------------------------------------------------------------------------------
-- Step 1: Retrive the Logical file name of the database from backup
ALTER DATABASE [passwordstate] SET OFFLINE WITH
ROLLBACK IMMEDIATE


-------------------------------------------------------------------------------------------------------------------------------------
-- Restore Database
-------------------------------------------------------------------------------------------------------------------------------------
RESTORE DATABASE passwordstate
FROM DISK = @SQLBackupFileName 
WITH NOUNLOAD, REPLACE, STATS = 5

 

 

 

We hope this helps:)

 

Support

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...