Production Releases on to Multiple Databases - Part 2

June 2, 2004

In the previous article, we discussed how to implement SQL production changes to multiple databases on the same server. In part two we will discuss implementing SQL changes to multiple databases on more than one server.

The following steps explain how to apply similar changes to multiple databases on more than one server.

Pre-requisites

You have SQL Server 2000 client installed on the machine from where you like 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 then create a file named Serverdblist.txt that contains a comma-delimited list of all the Servers and databases on the server:

SQL,Northwind
SQL,PUBS
YUKON,AdventureWorks
YUKON,Pubs
YUKON,NorthWind

Step2

Create a script file C:\Changes\Changes.sql as shown below. This script will hold all of the changes that are to be made to all of the databases.

--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)
GO

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 the 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)
GO

This will take backups of databases as shown below.

Step3

Create a script file C:\Changes\ApplyChanges2.Bat, as shown below, to apply all of the changes to the 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 ServerDBlist.txt
 
for /f "tokens=1,2,3 delims=," %%i in (C:\Changes\ServerDBlist.txt) 
  do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
  -S%%i -t0 -E -d%%j -ic:\Changes\Changes.sql

Step4

Execute the C:\Changes\ApplyChanges2.Bat as show below.

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

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

Server1

Server2

It stores the log information 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 Date: April 2, 2004 
C:\Changes>REM Apply changes to all the databases 
C:\Changes>REM Execute osql for DATABASE listed in ServerDBlist.txt 
C:\Changes>for /F "tokens=1,2,3 delims=," %i in (C:\Changes\ServerDBlist.txt) 
  do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
  -S%i -t0 -E -d%j -ic:\Changes\Changes.sql 
C:\Changes>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
  -SSQL -t0 -E -dNorthwind -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
  backup database Northwind to disk = "d:\Northwind_20040409.bak" with init
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.635 seconds (2.646
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 -t0 -E -dPUBS -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> backup database pubs to disk = "d:\pubs_20040409.bak" with init
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 0.720 seconds (1.821
MB/sec).
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> backup database AdventureWorks to disk = "d:\
AdventureWorks_20040409.bak" with
init
Processed 7888 pages for database 'AdventureWorks', file 'AdventureWorks_Data'
on file 1.
Processed 144 pages for database 'AdventureWorks', file 'WorkOrders' on file 1.
Processed 8 pages for database 'AdventureWorks', file 'TransactionHistory' on
file 1.
Processed 6 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on
file 1.
BACKUP DATABASE successfully processed 8046 pages in 5.399 seconds (12.208
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" 
  -SYUKON -t0 -E -dPubs -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> backup database pubs to disk = "d:\pubs_20040409.bak" with init
Processed 192 pages for database 'pubs', file 'pubs' on file 1.
Processed 7 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 199 pages in 0.593 seconds (2.742
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" 
  -SYUKON -t0 -E -dNorthWind -ic:\Changes\Changes.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
  backup database Northwind to disk = "d:\Northwind_20040409.bak" with init
Processed 360 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 7 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 367 pages in 0.799 seconds (3.757
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, the intent of this article is to guide Database administrators to implement production changes on multiple databases on more than one server.

» See All Articles by Columnist MAK