Production releases on to multiple databases - Part I

May 19, 2004

In certain environments, there will be multiple databases with the same structure. When Database Administrators make the same kind of changes or production releases on those databases it is tedious to open different windows in Query analyzer. In addition, it is tedious to open different query analyzers and apply changes. This article guides the database administrators to use a simple batch file to do such changes.

The following steps explain how to apply similar changes on multiple databases on the same server.

Pre-requisites

You have the SQL Server 2000 client installed on the machine from where you need to apply SQL changes to multiple databases. The user-ID used to login to that machine has the necessary permissions to the servers and databases listed.

Step1

Create a Folder, C:\Changes, and create a file named DatabaseList.txt as shown below with all the databases on a server.

Northwind
PUBS
ArrowPack
Bank

Step2

Create a script file named C:\Changes\Changes.sql as shown below, which will hold all of the changes that you would like to make on the database.

--Backup database before making changes
set quoted_identifier off
declare @backupquery varchar(1000)
set @backupquery = 'backup database '+ db_name() + ' to disk = "d:\'+ db_name() +
+'_'+convert(varchar(10),getdate(),112)+'.bak" with init'
print @backupquery
exec (@backupquery)
if exists (select * from dbo.sysobjects 
	where id = object_id(N'[client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [client]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[client]') 
	and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [client] (
	[client_id] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)
END
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_QueryClient]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_QueryClient]
GO
Create procedure usp_QueryClient as
Select Client_id, FirstName, LastName from Client
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Note: The first portion of the script should be as shown below. This will take backups of the databases involved in the change, before making changes.

set quoted_identifier off
declare @backupquery varchar(1000)
set @backupquery = 'backup database '+ db_name() + ' to disk = "d:\'+ db_name() +
+'_'+convert(varchar(10),getdate(),112)+'.bak" with init '
print @backupquery
exec (@backupquery)

Step3

Create a script file, C:\Changes\ApplyChanges.Bat, as shown below that will apply all of the changes to multiple databases listed in 'DatabaseList.txt'

REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Date: April 2, 2004
REM Apply changes to all the databases
REM Execute osql for DATABASE listed in DatabaseList.txt
REM Note: Server name is hard coded. Please update the server name.
 
for /f "tokens=1,2,3" %%i in (C:\Changes\Databaselist.txt) 
	do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -t0 -E -d%%i -ic:\Changes\Changes.sql

Step4

Execute the C:\Changes\ApplyChanges.Bat as show below, using the MS-DOS command shell.

applychanges.bat >c:\changes\Changelog.txt

This will backup all the databases listed in databaselist.txt as shown below and apply the changes on all of the databases.

Log information is stored in c:\changes\changelog.txt as shown below:

C:\Changes>REM Type: Batch File 
C:\Changes>REM Created by: MAK 
C:\Changes>REM Contact: mak_999@yahoo.com 
C:\Changes>REM Apply changes to all the databases 
C:\Changes>REM Execute osql for DATABASE listed in DatabaseList.txt 

C:\Changes>for /F "tokens=1,2,3" %i in (C:\Changes\Databaselist.txt) 
	do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -E -d%i -ic:\Changes\Changes.sql 
C:\Changes>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -E -dNorthwind -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
	backup database Northwind to disk = "d:\Northwind_20040408.bak"
Processed 528 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 529 pages in 1.890 seconds (2.289
MB/sec).
1> 2> 3> 4> 5> 6> 7> 8> 9> 1> 2> 1> 2> 1> 2> 3> 1> 2> 3> 1> 2> 1> 2> 1> 
C:\Changes>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -E -dPUBS -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
	backup database pubs to disk = "d:\pubs_20040408.bak"
Processed 160 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 161 pages in 1.432 seconds (0.918
MB/sec).
1> 2> 3> 4> 5> 6> 7> 8> 9> 1> 2> 1> 2> 1> 2> 3> 1> 2> 3> 1> 2> 1> 2> 1> 
C:\Changes>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -E -dArrowPack -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
	backup database ArrowPack to disk = "d:\ArrowPack_20040408.bak"
Processed 320 pages for database 'ArrowPack', file 'ArrowPack' on file 1.
Processed 1 pages for database 'ArrowPack', file 'ArrowPack_log' on file 1.
BACKUP DATABASE successfully processed 321 pages in 1.892 seconds (1.386
MB/sec).
1> 2> 3> 4> 5> 6> 7> 8> 9> 1> 2> 1> 2> 1> 2> 3> 1> 2> 3> 1> 2> 1> 2> 1> 
C:\Changes>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
	-SSQL -E -dBank -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
	backup database Bank to disk = "d:\Bank_20040408.bak"
Processed 88 pages for database 'Bank', file 'Bank' on file 1.
Processed 1 pages for database 'Bank', file 'Bank_log' on file 1.
BACKUP DATABASE successfully processed 89 pages in 0.696 seconds (1.038
MB/sec).
1> 2> 3> 4> 5> 6> 7> 8> 9> 1> 2> 1> 2> 1> 2> 3> 1> 2> 3> 1> 2> 1> 2> 1>

Conclusion

As mentioned earlier, this article is to guide Database administrators to implement production changes on multiple databases on the same server. In the next article, I discuss how to implement production changes on multiple databases, on more than one server.

» See All Articles by Columnist MAK