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 euglug@euglug.org http://www.euglug.org/mailman/listinfo/euglug
_______________________________________________ EUGLUG mailing list euglug@euglug.org http://www.euglug.org/mailman/listinfo/euglug