Setting up a Testing System

Rate This
  • Comments 1

I build a new SQL Server just about every other day. As we write new software or fix bugs on old software, we create a new installation of SQL Server. Those of us who are testing the fixes, creating new features and so on have to install these new versions on a system to check the software.

To do this I normally create a few Virtual Machines: one with Windows 2003 Enterprise, one with XP, and one with Vista. I then install the software and run my tests. Of course, we have far more testing than this, but mine are included in the battery of what we run.

(By the way, this helps explain a little of "why can't you just add this little thing right here - I'm a developer and it would only take two seconds to code it" question I get a lot of. Every time we make any change, we have quite a few building and testing processes we have to re-run on multiple platforms and in multiple languages. That doesn't even count the design time. But I digress....)

Next, I download and install a few sample databases. Since I go way back to Sybase and the early versions of SQL Server, I still use "pubs" for some of my scripts, and hey, let's not forget "NorthWinds" for those Access scripts I still have. I also use AdventureWorks, the AdventureWorksBI OLAP database, and the smaller AdventureWorksLT database for quicker testing that I'm trying to wean off of pubs. you can get those here:

Pubs and NorthWind

All the AdventureWorks DB's

Once I download them and install them, I back them up. I then store those backups on a common site, and then I run this script to install them all. You'll notice that only one has the "WITH MOVE" statement, since I had it on a different drive. I'll leave it in there for you so that you can see the format:

USE master;

GO

RESTORE DATABASE [AdventureWorks] FROM DISK = N'\\CentralStorageServer\temp\Backups\ADVENTUREWORKS.BAK'

WITH FILE = 1

, NOUNLOAD

, REPLACE

, STATS = 10

GO

 

RESTORE DATABASE [AdventureWorksDW] FROM DISK = N'\\CentralStorageServer\temp\Backups\ADVENTUREWORKSDW.BAK'

WITH FILE = 1

, NOUNLOAD

, REPLACE

, STATS = 10

GO

RESTORE DATABASE [AdventureWorksLT] FROM DISK = N'C:\temp\AdventureWorksLT.BAK'

WITH FILE = 1

, MOVE N'AdventureWorksLT_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf'

, MOVE N'AdventureWorksLT_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Log.ldf'

, NOUNLOAD

, REPLACE

, STATS = 10

GO

RESTORE DATABASE [NorthWind] FROM DISK = N'\\CentralStorageServer\temp\Backups\NORTHWIND.BAK'

WITH FILE = 1

, NOUNLOAD

, REPLACE

, STATS = 10

GO

RESTORE DATABASE pubs

FROM DISK = N'\\CentralStorageServer\temp\Backups\pubs.bak'

WITH FILE = 1

, NOUNLOAD

, REPLACE

, STATS=10

GO

But that's not all. I also need a large number of databases or tables sometimes to make sure we load things fast or display them properly. I use a "script that creates a script" to create the Dynamic Data Language (DDL) statements I need to create the objects. Here's an example for a ten databases with 10 tables (change the WHILE statements for as many as you like, add more inner WHILE statements to create other Objects):

USE master

GO

SET NOCOUNT ON

DECLARE @DBCounter int

SET @DBCounter = 0

DECLARE @TableCounter int

WHILE @DBCounter < 10

BEGIN

SELECT 'CREATE DATABASE Test' + CONVERT(varchar, @DBCounter) + ';'

SELECT 'GO'

SELECT 'USE Test' + CONVERT(varchar, @DBCounter) + ';'

SELECT 'GO'

SET @TableCounter = 0

WHILE @TableCounter < 10

BEGIN

SELECT 'CREATE TABLE TestTable' + CONVERT(varchar, @TableCounter) + ' ( TestColumn int);'

SELECT 'GO'

SET @TableCounter = @TableCounter + 1

END

SET @DBCounter = @DBCounter + 1

END

 

I just copy the results out (run the query to text or a file) and run that on my test system, and then I can add that to the Backup/Restore Process I mentioned.

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post
  • I build a new SQL Server just about every other day. As we write new software or fix bugs on old software

Page 1 of 1 (1 items)