I work for a company that sells software. And SOME people, because of their untrusing nature, need to be convinced that the software that we want to sell them actually works. This process, in some large, bureaucratic institutions, involves salesmen and conference calls and schmoozing and all of those things, that I, as a technical guy, like to leave to the more sociable ranks in the company.
Part of the process of demonstrating that the software that we produce meets certain functional criteria may involve webcasts or meetings where we show the product, in all it's glory, working flawlessly. This means having a server always available for this purpose and in tip-top working order.
The fly in the ointment is this: how to allow non-technical people to run software on this machine and potentially modify (a polite way of saying BREAK) the SQL Server, its' databases, and operating environment, and then later in the day allow someone else to run the same demonstartions on a server that's restored to its' original environment. I know what you're thinking and this was my first though as well: virtual server! You could create a virtual server and roll back the entire environment to an earlier state without a lot of trouble.
The virtual server idea was shot dead like a wounded racehorse because we need to demonstrate the increased performance gains of our SQL Backup software: we needed a high-performance environment with the quickest disks, more RAM than a virtual environment can provide, and ultra-quick GUI response time. What we needed was SQL Server with a rewind button so any atrocities committed against the server environment could be rolled back at will, or better yet on a schedule so I don't have to tinker around with the server ever again.
Ideally, I thought, leveraging backup technology would work. Assuming that all of the SQL Server configuration is kept in the system databases, it would 'simply' be a matter of taking a one-time, incorruptable backup of these databases and restoring it back to the platform.
So I backed up all of the databases on the demo server and saved the backups to an out of the way path (NOT the default SQL Server backup path!!!). The backup file names for all demo databases also correspond to the database names, making it easier to automate the restore job.
But since you need to restart SQL multiple times and restore system databases, a SQL Script is impractical for this task. I need to work outside the SQL Server and for that reason I implemented rewind as a VBScript. The first thing a coder needs is a plan, so I wrote out my strategy as pseudo-code:
- If SQL Server is started, stop it and all dependent services
- Start SQL Server in single-user mode
- Restore the master database
- Shut down the server and bring it up in multi-user mode
- Restore model and msdb
- Restore all user databases (the list of databases is in our newly-restored master database)
- Start SQL Server and all dependent services
- Delete all backup files from the default backup location to prevent demonstrators from filling up the disk with SQL Backup demo data
This script has been running as a Windows Scheduled Task at midnight for about six months, and has saved a lot of time maintaining a SQL Server 2005 demonstration environment for sales and marketing concerns. There is only one basic flaw and that is that if the SQL Server software is updated, for instance service packs and Microsoft hotfixes, the system databases need to be backed up again, or the system database restores will fail.
'recoversql.vbs
'given a sql server and its' backups, restore the server and all dbs.
'Assumptions:
' 1. Backups are taken from the same server (the logical and psysical filenames match)
' 2. Backups are named after the database that they came from (ie MASTER is restored from master.bak)
'
' NOTE WELL! Part of this script will delete files from the default backup folder!
' This is a bit outside the realm of recovering sql, so remove these bits
' if you want to re-use this script for something else!
'
'------------- Things you can set -------------------
Dim strBackupFolder 'the folder with the master backups in it w/trailing backslash
strBackupFolder="d:\demo databases\"
Dim strServerCommand 'Path to sqlservr command for single user startup
strServerCommand="""C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr"""
Dim strInstance 'The name of the SQL Server instance, MSSQLSERVER for the default
strInstance="MSSQLSERVER"
Dim strDefaultBackupFolder 'The name of the folder where backups go by default (trailing backslash)
strDefaultBackupFolder="d:\MSSQL.1\MSSQL\Backup\" 'we will delete all of these as part of maint.
' ------------------ GLOBALS -------------------------
Dim obShell
Set obShell=WScript.CreateObject("WScript.Shell")
Dim obFSO
Set obFSO=WScript.CreateObject("Scripting.FileSystemObject")
Dim iResult
Dim serverName
serverName=obShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
if strInstance="MSSQLSERVER" Then strInstance=serverName
WScript.Echo("Recovering " &strInstance &" using backups from " &strBackupFolder)
'If SQL Server running, stop it
StopSqlServer strInstance
'Start SQL Server (single-user)
StartSqlServer strInstance, True
'restore system databases
RestoreSystemDatabases strInstance
'read database list from master, restore each database (exc. tempdb)
RestoreAllDatabases strInstance
'Start SQL Server (multi-user)
StartSqlServer strInstance, False
'Start SQL Server support services
obShell.Run "NET START SQLSERVERAGENT", 1, True
obShell.Run "NET START MSSQLServerOLAPService", 1, True
obShell.Run "NET START SQLBROWSER", 1, True
obShell.Run "NET START MSDTSSRVR", 1, True
obShell.Run "NET START MSFTESQL", 1, True
'Delete old backups
WScript.Echo("Deleting backup files from " &strDefaultBackupFolder)
obShell.Run "cmd /c ""del /q /f /s "&strDefaultBackupFolder &"*.*""",1,True
'-- Functions --
'-----------------------------------RestoreAllDatabases----------------------------------
'Restore all databases but master, model, msdb
Function RestoreAllDatabases(InstanceName)
Dim stdin, strBackupType, obDatabasesRS, obMasterConnection, strRestoreCommand, DatabaseName, iResultCode
'Get the list of databases
Set obMasterConnection=WScript.CreateObject("ADODB.Connection")
obMasterConnection.CommandTimeout=9000 'command timeout 15 minutes, hopefully enough?
obMasterConnection.Open="Provider=SQLOLEDB;Data Source="&InstanceName&_";Initial Catalog=master;Integrated Security=SSPI"
Set obDatabasesRS=WScript.CreateObject("ADODB.Recordset")
Set obDatabasesRS.ActiveConnection=obMasterConnection
obDatabasesRS.Open "Select [name] FROM sysdatabases WHERE [name] NOT IN ('master','tempdb', 'msdb', 'model')"
obDatabasesRS.MoveFirst
Do Until obDatabasesRS.EOF
DatabaseName=obDatabasesRS.Fields(0)
obMasterConnection.Execute "RESTORE DATABASE ["&DatabaseName&"] FROM DISK='"&
_strBackupFolder &DatabaseName&".bak' WITH REPLACE"
WScript.Echo("Restored " &DatabaseName &" from " &strBackupFolder &DatabaseName &".bak")
obDatabasesRS.MoveNext
Loop 'databases
obDatabasesRS.Close
obMasterConnection.Close
Set obDatabasesRS=Nothing
Set obMasterConnection=Nothing
End Function
'End Restore database
'-------------------------------- RestoreSystemDatabases --------------------------------
' Ask for location of last good master, model, and msdb SQL Backups
' Restore these and restart the server in multi-user mode
Function RestoreSystemDatabases(InstanceName)
'Attempt restore of master (exit script on fail)
RestoreSystemDatabase "master", InstanceName, strBackupFolder &"master.bak", True
WScript.Echo("Restored Master from " &strBackupFolder &"master.bak")
'Start the SQL Server; restoring MASTER will stop the server.
StartSqlServer InstanceName, False
'Attempt restore of msdb (exit script on fail)
RestoreSystemDatabase "msdb", InstanceName, strBackupFolder &"msdb.bak", True
WScript.Echo("Restored MSDB from " &strBackupFolder &"msdb.bak")
'Attempt restore of model (allow fail?)
RestoreSystemDatabase "model", InstanceName, strBackupFolder &"model.bak", False
WScript.Echo("Restored MODEL from " &strBackupFolder &"model.bak")
END FUNCTION
'------------------------------------ End restoresystemdatabases-----------------------------
'-------------------------------------RestoreSystemDatabase-------------------------------
'Support function for RestoreSystemDatabases
Function RestoreSystemDatabase(DatabaseName, InstanceName, BackupFileName, ExitOnFail)
Dim retCode, stdin, obDBConn
Dim strBackupCommand
strBackupCommand="RESTORE DATABASE ["&DatabaseName&"] FROM DISK='"&BackupFileName&"' WITH REPLACE"
Set obDBConn=WScript.CreateObject("ADODB.Connection")
obDBConn.Open="Provider=SQLOLEDB;Data Source="&InstanceName&";Integrated Security=SSPI"
obDBConn.Execute strBackupCommand
obDBConn.Close
SET obDBConn=Nothing
End Function
'----------------------------- StartSqlServer --------------------------------
' Modify the registry to start sql server in single-user if needed.
' Net Start the SQL Server Service
Function StartSqlServer(InstanceName, SingleUser)
Dim retVal
IF InstanceName=serverName Then
If Not SingleUser Then
retVal=obShell.Run("NET START MSSQLSERVER", 1, True)
End If
If SingleUser Then
obShell.Run strServerCommand &" -m", 1, False
WScript.Sleep(20000) 'Wait for server to start
End If
ELSE: retVal=obShell.Run("NET START MSSQL$"&InstanceName, 1, True)
End If
If retVal > 2 Then
WSCript.Echo("Could not start SQL Server """&InstanceName&
_""". Command Returned "&retVal)
WScript.Quit(1)
End If
End Function
'End modify startup parameters
'----------------------------- StopSQLServer ---------------------------
'Run NET STOP for an instance of SQL Server.
Function StopSQLServer(InstanceName)
obShell.Run "NET STOP SQLSERVERAGENT", 1, True
obShell.Run "NET STOP MSSQLServerOLAPService", 1, True
obShell.Run "NET STOP SQLBROWSER", 1, True
obShell.Run "NET STOP MSDTSSRVR", 1, True
obShell.Run "NET STOP MSFTESQL", 1, True
IF InstanceName=serverName Then
obShell.Run "NET STOP MSSQLSERVER", 1, True
ELSE: obShell.Run "NET STOP MSSQL$"&InstanceName, 1, True
End If
End Function
'-----------------------------End StopSqlServer --------------------------------------