Sure thing.
I do as much work as I can in SQL Server through Query Analyzer rather
than Enterprise Manager, so here are the scripts I use for backing up
and restoring individual databases.
USE master
EXEC sp_addumpdevice 'disk', 'DatabaseBackup', '\\Path\to\database\file.bak'
BACKUP DATABASE Database TO DatabaseBackup
USE master
RESTORE DATABASE Database
FROM DatabaseBackup
WITH RECOVERY, REPLACE, STATS
EXEC sp_dropdevice 'DatabaseBackup'
This is the perl script that writes out all the database changes. The
HMS include is something we use in-house that helps with printing
debug output so that bit would need to be removed before this would
run.
#!/usr/bin/perl -w
use strict;
use Getopt::Std;
use HMS::Configuration;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO';
debug(1);
my %opts;
getopts('S:d:o:u:p:', \%opts); #get command line arguments
my ($servername, $dbname, $user, $password, $output) =
($opts{S}, $opts{d}, $opts{u}, $opts{p}, $opts{o});
#check if the required command-line arguments are present
(defined $servername && defined $output) or printusage();
my $server = Win32::OLE->new('SQLDMO.SQLServer2') or die "***Err:
could not create SQLDMO object.";
if (defined $user) {
$server->{LoginSecure} = 0; #SQL connection
}
else {
$server->{LoginSecure} = 1; #trusted connection
}
#make the connection to the server
$server->connect($servername, $opts{u}, $opts{p});
! Win32::OLE->LastError() or die "***Err: SQLDMO could not connect to
$servername.";
#my $db = $server->Databases($dbname);
##script the database creation
#$db->Script(SQLDMOScript_Default | SQLDMOScript_AppendToFile |
# SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
# SQLDMOScript_ToFileOnly, "$output\\db.sql");
foreach my $db (in ($server->Databases())) {
my $dbname = $db->{Name};
print "scripting $dbname\n" if debug();
my $tables = $server->Databases($dbname)->Tables();
! Win32::OLE->LastError() or die "***Err: could not get the tables
in $dbname.";
#script tables including DRI constraints
foreach my $obj (in($tables)) {
my $name = $obj->{Name};
print "scripting table $name\n" if debug();
if (($obj->TypeOf()) == SQLDMOObj_UserTable) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops | SQLDMOScript_DRI_All |
SQLDMOScript_ToFileOnly |
SQLDMOScript_ObjectPermissions,
"$output\\$dbname\\Tables\\$name.sql");
}
}
#script triggers separately
foreach my $obj (in($tables)) {
if (($obj->TypeOf()) == SQLDMOObj_UserTable) {
my $triggers = $obj->Triggers();
foreach my $tr (in($triggers)) {
my $name = $tr->{Name};
print "scripting trigger $name\n" if debug();
$tr->Script(SQLDMOScript_Default |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops | SQLDMOScript_ToFileOnly |
SQLDMOScript_ObjectPermissions,
"$output\\$dbname\\Triggers\\$name.sql");
}
}
}
#script stored procs
foreach my $obj (in($server->Databases($dbname)->StoredProcedures())) {
my $name = $obj->{Name};
print "scripting stored proc $name\n" if debug();
if (substr($name, 0, 3) ne 'dt_') {
$obj->Script(SQLDMOScript_Default | SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops | SQLDMOScript_ToFileOnly |
SQLDMOScript_ObjectPermissions,
"$output\\$dbname\\StoredProcs\\$name.sql");
}
}
#script UDFs
foreach my $obj (in($server->Databases($dbname)->UserDefinedFunctions())) {
my $name = $obj->{Name};
print "scripting function $name\n" if debug();
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly | SQLDMOScript_ObjectPermissions,
"$output\\$dbname\\Functions\\$name.sql");
}
#script views
foreach my $obj (in ($server->Databases($dbname)->Views())) {
my $name = $obj->{Name};
print "scripting view $name\n" if debug();
if ($obj->{Owner} ne 'INFORMATION_SCHEMA' and substr($name, 0,
3) ne 'sys') {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly | SQLDMOScript_ObjectPermissions,
"$output\\$dbname\\Views\\$name.sql");
}
}
}
$server->disconnect();
$server->DESTROY();
sub printusage {
print << '--Usage--';
Useage:
cmd>perl scriptSQLDMO.pl -S <SQL server or instance>
-d <database name>
-o <output file>
-u <SQL login>
-p <password>
--Usage--
exit;
} #print usage
On 10/31/06, Quentin Hartman <[EMAIL PROTECTED]> wrote:
On 10/31/06, M. Bitner <[EMAIL PROTECTED]> wrote:
> It's a pretty good book. I used it to write a script to write out all
> of the tables, stored procs, triggers, functions, and views from our
> production databases so they could be stored in version control. It
> isn't quite what you want but I'd be happy to send it to you if it
> would help in any way.
I'd love to see it, if for no other reason than to see some successful
techniques that may give me other ideas.
--
-Regards-
-Quentin Hartman-
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
