Creation, backup, and recovery of SQL Server 2 k databases in Pb

1. Shared Functions

1. gf_check_sqlserver

//************************************** ****************************//
// Function: gf_check_sqlserver
// Description: determines whether the SQL installation has been started. If the SQL installation is not started, perform corresponding processing.
//--------------------------------------------------------------------
// Return: integer lo_sqlserver.status:
// 0 Unknown Status
// 1 started
// 2 SQL Server is paused
// 3 SQL Server is in the stopped status
// 4 sqlserver is starting (close --> starting)
// 5 is shutting down
// 6 sqlserver is starting (pause --> start)
// 7 SQL Server is paused (start --> paused)
//--------------------------------------------------------------------
// Arguments: (none)
//--------------------------------------------------------------------
// Author: Liu XJ Date: 2005.12.11
//************************************** ****************************//

Oleobject lo_sqlserver
String ls_servername
String ls_username = 'sa'
String ls_password =''
Integer li_return

Ls_servername = profilestring ("user. ini", "Profile", "servername", "192.168.1.2 ")
Lo_sqlserver = create oleobject
Li_return = lo_sqlserver.connecttonewobject ("sqldmo. sqlserver ")

If li_return = 0 then // connection successful
Lo_sqlserver.name = ls_servername
Lo_sqlserver.logintimeout = 10
// Lo_sqlserver.loginsecure = true // connect in NT mode
Lo_sqlserver.loginsecure = false // Connect using SQL Server
 
Try
Li_return = lo_sqlserver.status
Catch (runtimeerror RTE)
MessageBox ('hprompt ', "sqlserver is not installed in the system", stopsign !)
End try
 
Choose case li_return
Case 1 // started
// MessageBox ('hprompt ', ls_servername + 'sqlserver server started ')
// Lo_sqlserver.stop () // stop sqlserver
Case 2 // SQL Server is paused
Lo_sqlserver.continue ()
MessageBox ('hprompt ', 'server is suspended, and the SQL server on' + ls_servername + 'is being started ...~ R ~ N ~ R ~ N. Try again later. ')
Case 3 // SQL Server is stopped
Lo_sqlserver.start (false, ls_servername, ls_username, ls_password)
MessageBox ('hprompt ', 'server stopped, starting SQL Server' + ls_servername + ...~ R ~ N ~ R ~ N. Try again later. ')
Case 4 // sqlserver is starting (close --> start)
MessageBox ('hprompt ', the SQL server on ls_servername +' is starting (close --> starting )')
Case 5 // sqlserver is shutting down
MessageBox ('hprompt ', the SQL server on ls_servername +' is shutting down ')
Case 6 // sqlserver is starting (pause --> start)
MessageBox ('hprompt ', SQL Server on ls_servername +' is starting (pause --> Start )')
Case 7 // SQL Server is paused (start --> pause)
MessageBox ('hprompt ', SQL Server on ls_servername +' is paused ')
Case 0 // unknown
MessageBox ('hprompt ', 'unknown' + ls_servername + 'SQL Server status ')
End choose
Else
Destroy (lo_sqlserver)
MessageBox ('System prompt ', "SQL sserver is not installed in the system! ")
Return 0
End if
Destroy (lo_sqlserver)
Return li_return

2. Create a data database

Integer li_count
String ls_ SQL
String ls_dbpath
Uno_des luno_des
If gf_check_sqlserver () <> 1 then return

If not wf_check_input () then return
Setpointer (hourglass !)
Timer (1)
// Profile master
Itrans_master = create transaction
Itrans_master.dbms = "MSS Microsoft SQL Server"
Itrans_master.database = 'master'
Itrans_master.logpass = is_logpass
Itrans_master.servername = is_servername
Itrans_master.logid = is_logid
Itrans_master.autocommit = true
Sqlca. dbparm = ""

Connect using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if

// ************* Obtain the server NIC address
Select top 1 net_address into: is_servermacadd from sysprocesses where dbid = db_id ('master') order by login_time DESC using itrans_master;
If isnull (is_servermacadd) or (LEN (is_servermacadd) <> 12) then
MessageBox ("system prompt", 'make sure the network connection is normal and try again. ')
Goto Error
Else
Is_servermacadd = mid (is_servermacadd, 3, 2) + right (is_servermacadd, 6)
Is_servermacadd = luno_des.uf_jiami (is_servermacadd)
// MessageBox ('is _ servermacadd', is_servermacadd)
End if

Hpb_1.position = 10
//// // Create an empty library
Select count (1) into: li_count from sysdatabases where name =: is_database using itrans_master;

If li_count> 0 then
MessageBox ('System prompt ', 'database already exists! ')
// Goto Error
Ls_ SQL = "DROP DATABASE" + is_database
Execute immediate: ls_ SQL using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if
End if

Hpb_1.position = 30
Ls_dbpath = is_currentdirectory + '/data /'
// Ls_ SQL = "CREATE DATABASE" + is_database + "On (name = '" + is_database + "_ data", filename =' "+ ls_dbpath + is_database +" _ data. MDF ', size = 10, filegrowth = 10%) log on (name =' "+ is_database +" _ log', filename = '"+ ls_dbpath + is_database +" _ log. ldf', size = 5, filegrowth = 10% )"
Ls_ SQL = "CREATE DATABASE" + is_database
Execute immediate: ls_ SQL using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if

Hpb_1.position = 60
// ************** Check the connection status ************//
/* Select spid into: li_count from sysprocesses where dbid = db_id (: is_database) using itrans_master;
If li_count> 0 then
// MessageBox ("system prompt", 'the database has other user connections and cannot be recovered .~ R ~ N ~ R ~ N please try again when the database is idle. ')
// Goto Error
End if
*/
//// // Restore the database
Ls_ SQL = "Restore database" + is_database + "from disk = '" + is_dbbakfile + "' with recovery"
Execute immediate: ls_ SQL using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if
Hpb_1.position = 90

Ls_ SQL = "Update" + is_database + "... gy_constant set Zhi = '" + is_servermacadd + "'where MC = 'servermacadd '"
Execute immediate: ls_ SQL using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if

Disconnect using itrans_master;
Destroy itrans_master
Hpb_1.location = 100
Timer (0)
Setpointer (arrow !)
MessageBox ("system prompt", "the database has been created. ")

Return

Error:
Disconnect using itrans_master;
Destroy itrans_master
Hpb_1.position = 1
Setpointer (arrow !)
Timer (0)
Return

3. Backup Database

String ls_ SQL
// String ls_dbpath
If gf_check_sqlserver () <> 1 then return

If not wf_check_input () then return
Setpointer (hourglass !)
Timer (1)

// Profile master
Itrans_master = create transaction
Itrans_master.dbms = "MSS Microsoft SQL Server"
Itrans_master.database = 'master'
Itrans_master.logpass = is_logpass
Itrans_master.servername = 'liuxj'
Itrans_master.logid = is_logid
Itrans_master.autocommit = true
Sqlca. dbparm = ""

Connect using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if

Hpb_1.position = 10
// ************** Back up the database *************//
Ls_ SQL = "backup database" + is_database + "to disk = '" + is_dbbakfile + "'"
Execute immediate: ls_ SQL using itrans_master;

If itrans_master.sqlcode <> 0 then
MessageBox ("backup database error:", itrans_master.sqlerrtext)
Goto Error
End if

Hpb_1.position = 90
Disconnect using itrans_master;
Destroy itrans_master

Is_dbbackpath = left (is_dbbakfile, lastpos (is_dbbakfile ,'/'))

Hpb_1.location = 100
Setpointer (arrow !)
Timer (0)
MessageBox ("system prompt", space (20) + "the database has been backed up .~ R ~ N ~ R ~ N back up to the following file :~ R ~ N "+ is_dbbakfile)
Return

Error:
Disconnect using itrans_master;
Destroy itrans_master
Hpb_1.position = 1
Timer (0)
Setpointer (arrow !)
Return

4. Restore the database

Integer li_count
String ls_ SQL
String ls_dbpath
If gf_check_sqlserver () <> 1 then return

If not wf_check_input () then return
Setpointer (hourglass !)
Timer (1)

// Profile master
Itrans_master = create transaction
Itrans_master.dbms = "MSS Microsoft SQL Server"
Itrans_master.database = 'master'
Itrans_master.logpass = is_logpass
Itrans_master.servername = is_servername
Itrans_master.logid = is_logid
Itrans_master.autocommit = true
Sqlca. dbparm = ""

Connect using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("system prompt", itrans_master.sqlerrtext)
Goto Error
End if

Hpb_1.position = 10

// ************** Check the connection status ************//
Select spid into: li_count from sysprocesses where dbid = db_id (: is_database) using itrans_master;
If li_count> 0 then
MessageBox ("system prompt", 'the database has other user connections and cannot be recovered .~ R ~ N ~ R ~ N please try again when the database is idle. ')
Goto Error
End if

*************//
Ls_ SQL = "Restore database" + is_database + "from disk = '" + is_dbbakfile + "' with recovery"
Execute immediate: ls_ SQL using itrans_master;
If itrans_master.sqlcode <> 0 then
MessageBox ("database restoration error:", itrans_master.sqlerrtext)
Goto Error
End if
Hpb_1.position = 90

Disconnect using itrans_master;
Destroy itrans_master
Hpb_1.location = 100
Setpointer (arrow !)
Timer (0)
MessageBox ("system prompt", "the database has been recovered. ")
Return

Error:
Disconnect using itrans_master;
Destroy itrans_master
Hpb_1.position = 1
Setpointer (arrow !)
Timer (0)
Return