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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
The virtual server idea was shot dead like a wounded racehorse because we need to demonstrate the increased performance gains of our SQL Backup<\/em> 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.<\/p>\n 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. <\/p>\n 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.<\/p>\n 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:<\/p>\n 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.<\/p>\n 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……<\/p>\n","protected":false},"author":27149,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2546","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/27149"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2546"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2546\/revisions"}],"predecessor-version":[{"id":41561,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2546\/revisions\/41561"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2546"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
'recoversql.vbs \n'given a sql server and its' backups, restore the server and all dbs. \n'Assumptions:\n' 1. Backups are taken from the same server (the logical and psysical filenames match)\n' 2. Backups are named after the database that they came from (ie MASTER is restored from master.bak)\n'\n'\tNOTE WELL! Part of this script will delete files from the default backup folder!\n' This is a bit outside the realm of recovering sql, so remove these bits\n' if you want to re-use this script for something else!\n'\n'------------- Things you can set -------------------\nDim strBackupFolder 'the folder with the master backups in it w\/trailing backslash\nstrBackupFolder=\"d:demo databases\" \nDim strServerCommand 'Path to sqlservr command for single user startup\nstrServerCommand=\"\"\"C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr\"\"\"\nDim strInstance 'The name of the SQL Server instance, MSSQLSERVER for the default\nstrInstance=\"MSSQLSERVER\"\nDim strDefaultBackupFolder 'The name of the folder where backups go by default (trailing backslash)\nstrDefaultBackupFolder=\"d:MSSQL.1MSSQLBackup\" 'we will delete all of these as part of maint.\n\n' ------------------ GLOBALS ------------------------- \nDim obShell \nSet obShell=WScript.CreateObject(\"WScript.Shell\") \n\nDim obFSO \nSet obFSO=WScript.CreateObject(\"Scripting.FileSystemObject\") \n\nDim iResult \nDim serverName\nserverName=obShell.ExpandEnvironmentStrings(\"%COMPUTERNAME%\")\n\nif strInstance=\"MSSQLSERVER\" Then strInstance=serverName\nWScript.Echo(\"Recovering \" &strInstance &\" using backups from \" &strBackupFolder) \n'If SQL Server running, stop it \n StopSqlServer strInstance \n'Start SQL Server (single-user) \n StartSqlServer strInstance, True \n'restore system databases \n RestoreSystemDatabases strInstance \n'read database list from master, restore each database (exc. tempdb) \n RestoreAllDatabases strInstance \n'Start SQL Server (multi-user) \n StartSqlServer strInstance, False\n'Start SQL Server support services\n\tobShell.Run \"NET START SQLSERVERAGENT\", 1, True\n\tobShell.Run \"NET START MSSQLServerOLAPService\", 1, True \n\tobShell.Run \"NET START SQLBROWSER\", 1, True \n\tobShell.Run \"NET START MSDTSSRVR\", 1, True \n\tobShell.Run \"NET START MSFTESQL\", 1, True \n'Delete old backups\n WScript.Echo(\"Deleting backup files from \" &strDefaultBackupFolder)\n obShell.Run \"cmd \/c \"\"del \/q \/f \/s \"&strDefaultBackupFolder &\"*.*\"\"\",1,True\n\n\n'-- Functions -- \n\n'-----------------------------------RestoreAllDatabases---------------------------------- \n'Restore all databases but master, model, msdb\n\nFunction RestoreAllDatabases(InstanceName) \nDim stdin, strBackupType, obDatabasesRS, obMasterConnection, strRestoreCommand, DatabaseName, iResultCode \n\n\n'Get the list of databases \nSet obMasterConnection=WScript.CreateObject(\"ADODB.Connection\") \nobMasterConnection.CommandTimeout=9000 'command timeout 15 minutes, hopefully enough?\nobMasterConnection.Open=\"Provider=SQLOLEDB;Data Source=\"&InstanceName&<\/pre>\n
_\";Initial Catalog=master;Integrated Security=SSPI\" \nSet obDatabasesRS=WScript.CreateObject(\"ADODB.Recordset\") \nSet obDatabasesRS.ActiveConnection=obMasterConnection \nobDatabasesRS.Open \"Select [name] FROM sysdatabases WHERE [name] NOT IN ('master','tempdb', 'msdb', 'model')\" \nobDatabasesRS.MoveFirst \n\n\tDo Until obDatabasesRS.EOF \n\t\tDatabaseName=obDatabasesRS.Fields(0) \n\t\tobMasterConnection.Execute \"RESTORE DATABASE [\"&DatabaseName&\"] FROM DISK='\"&\n_strBackupFolder &DatabaseName&\".bak' WITH REPLACE\" \n\t\tWScript.Echo(\"Restored \" &DatabaseName &\" from \" &strBackupFolder &DatabaseName &\".bak\")\n\t\tobDatabasesRS.MoveNext\t\n\tLoop 'databases \n\nobDatabasesRS.Close \nobMasterConnection.Close \nSet obDatabasesRS=Nothing \nSet obMasterConnection=Nothing \nEnd Function \n'End Restore database \n\n\n'-------------------------------- RestoreSystemDatabases -------------------------------- \n' Ask for location of last good master, model, and msdb SQL Backups \n' Restore these and restart the server in multi-user mode \n\nFunction RestoreSystemDatabases(InstanceName) \n 'Attempt restore of master (exit script on fail) \n RestoreSystemDatabase \"master\", InstanceName, strBackupFolder &\"master.bak\", True \n\tWScript.Echo(\"Restored Master from \" &strBackupFolder &\"master.bak\")\n 'Start the SQL Server; restoring MASTER will stop the server.\n\tStartSqlServer InstanceName, False \n 'Attempt restore of msdb (exit script on fail) \n RestoreSystemDatabase \"msdb\", InstanceName, strBackupFolder &\"msdb.bak\", True \n\tWScript.Echo(\"Restored MSDB from \" &strBackupFolder &\"msdb.bak\")\n 'Attempt restore of model (allow fail?) \n RestoreSystemDatabase \"model\", InstanceName, strBackupFolder &\"model.bak\", False \n\tWScript.Echo(\"Restored MODEL from \" &strBackupFolder &\"model.bak\")\nEND FUNCTION \n'------------------------------------ End restoresystemdatabases----------------------------- \n\n'-------------------------------------RestoreSystemDatabase------------------------------- \n'Support function for RestoreSystemDatabases \n\nFunction RestoreSystemDatabase(DatabaseName, InstanceName, BackupFileName, ExitOnFail) \nDim retCode, stdin, obDBConn\n\nDim strBackupCommand\nstrBackupCommand=\"RESTORE DATABASE [\"&DatabaseName&\"] FROM DISK='\"&BackupFileName&\"' WITH REPLACE\"\nSet obDBConn=WScript.CreateObject(\"ADODB.Connection\") \nobDBConn.Open=\"Provider=SQLOLEDB;Data Source=\"&InstanceName&\";Integrated Security=SSPI\" \nobDBConn.Execute strBackupCommand\nobDBConn.Close\nSET obDBConn=Nothing\n\nEnd Function \n\n'----------------------------- StartSqlServer -------------------------------- \n' Modify the registry to start sql server in single-user if needed. \n' Net Start the SQL Server Service \n\nFunction StartSqlServer(InstanceName, SingleUser) \nDim retVal \n IF InstanceName=serverName Then \n\tIf Not SingleUser Then\n\t retVal=obShell.Run(\"NET START MSSQLSERVER\", 1, True) \n\tEnd If\n\tIf SingleUser Then\t\n \t\tobShell.Run strServerCommand &\" -m\", 1, False\n\t\tWScript.Sleep(20000) 'Wait for server to start\n\tEnd If\n ELSE: retVal=obShell.Run(\"NET START MSSQL$\"&InstanceName, 1, True) \n End If \n If retVal > 2 Then \n WSCript.Echo(\"Could not start SQL Server \"\"\"&InstanceName&\n_\"\"\". Command Returned \"&retVal) \n WScript.Quit(1) \n End If \nEnd Function \n'End modify startup parameters \n\n'----------------------------- StopSQLServer --------------------------- \n'Run NET STOP for an instance of SQL Server. \n\nFunction StopSQLServer(InstanceName) \n obShell.Run \"NET STOP SQLSERVERAGENT\", 1, True \n\tobShell.Run \"NET STOP MSSQLServerOLAPService\", 1, True \n\tobShell.Run \"NET STOP SQLBROWSER\", 1, True \n\tobShell.Run \"NET STOP MSDTSSRVR\", 1, True \n\tobShell.Run \"NET STOP MSFTESQL\", 1, True \n IF InstanceName=serverName Then\n obShell.Run \"NET STOP MSSQLSERVER\", 1, True\n ELSE: obShell.Run \"NET STOP MSSQL$\"&InstanceName, 1, True \n End If \nEnd Function \n\n'-----------------------------End StopSqlServer --------------------------------------<\/pre>\n","protected":false},"excerpt":{"rendered":"