Operate and manage SQL Server (Introduction SQL-DMO) in VFP post SQL Server2000 Database backup and recovery Stored Procedure

After so many years of mixing in the forum, we have seen an obvious trend: more and more netizens are using VFP and SQL server for the system. Many people are concerned about how to operate or manage SQL Server from VFP. You can use views, SPT, or ADO to perform data operations on SQL Server. However, to manage the server itself, it seems that only SPT can be used to send some commands. In fact, Microsoft has developed a complete set of tools for Operating SQL server on the client, which is SQL-DMO (Distributed Management objects ). this is a set of COM components that can be used in various languages. VFP can also be used.
I am going to take some time to write some articles about this and create an example. It is a gift I will give to you after I pass the mcba. The following introduction and examples are for SQL Server 2000 and vfp7.

I. necessity of using SQL-DMO

Most vfper programs are small-and medium-sized systems, because this is the most suitable range for VFP. In many cases, such a system is not maintained by professional IT personnel on the client, let alone a dedicated DBA. The SQL Server system has some problems. SQL Server is a set of large professional database software, which often requires maintenance and cleaning. However, we system developers cannot always go to the users for maintenance, this is especially costly for commercial software. If you can put the maintenance work on SQL server in your own program, or even seamlessly integrate the installation of SQL Server into the installation package of your own program, I believe everyone will be happy. DMO functions can meet our needs. It allows you to use commands in a program to operate SQL SERVER: Stop/start services, create databases and tables, add users and permissions, and back up/restore databases, data Import/Export/release... DMO is designed to implement all the functions that can be completed in Enterprise Manager using code.

2. Microsoft Desktop Database Engine)

We all know that SQL Server 2000 has four versions: Personal Edition, Developer Edition, Standard Edition, and Enterprise Edition. In fact, there is also a version called the desktop Data Engine (DDE ). DDE has almost
All functions of SQL Server Standard Edition and Enterprise Edition do not support only a few uncommon features. The difference between DDE and other versions is that DDE does not have any graphical management interface, Enterprise Manager, query analyzer, and other tools. The management of it is mainly carried out through DMO or a special API. Another advantage of it is that it provides the installation program and can be directly added to the 3rd-party software for installation. Each version of SQL Server CD contains DDE, which can be installed by running the setup program under the MSDE directory. As for how to add it to your own program for installation, you can find the Help File of SQL Server.
The limitation of DDE is: it is suitable for small and medium-sized systems (for example, if there are not more than five users simultaneously connecting to and running large SQL commands). If there are many users simultaneously accessing, DDE will be slower than the normal SQL Server.

In this way, DDE + DMO can implement the functions required above. This practice can even prevent users from knowing that you are using SQL Server as a database. Of course, DMO is not only able to operate DDE, it can operate all versions of SQL Server.

Iii. DMO Installation

In fact, DMO is mainly a DLL file: sqldmo. dll. The specific location is in the C:/program files/Microsoft SQL Server/80/tools/binn directory, and there is a help file sqldmo80.hlp in the same directory. if you cannot use DMO in the program, find the DLL file and register it. In addition, there are sqldmo examples in the C:/program files/Microsoft SQL Server/80/tools/devtools/samples/sqldmo directory, but they are both VB and VC ++. The VB example is very similar to that of VFP.

4. Initial contact with DMO

Write a few simple sentences here to see the power of DMO. These commands can be directly tested in a line in the Command window, but they are used to display the database information. To add complex functions such as database, data table, backup, and recovery, you must do so in the instance.

Oserver = Createobject ("sqldmo. sqlserver") & create a Server Object
Oserver. Connect ("Snoopy", "sa", "778899") & connect to your server
? Oserver. Databases. Count & display the total number of databases on the server
? Oserver. Databases. Item (1). Name & displays the name of the first database

ODB = oserver. Databases ("pubs") & create a database object
? ODB. Tables. Count & display the number of tables in the database
Otable = ODB. Tables ("titles") & create a table object
? Otable. Rows & display the number of records in the table
? Otable. Columns. Count & displays the number of columns in the table

** Display column attributes
? Otable. Columns (1). Name
? Otable. Columns (1). Type
? Otable. Columns (1). datatype
? Otable. Columns (1). Identity
? Otable. Columns (1). inprimarykey

Example:

Backup and Restore Databases

Oserver = Createobject ("sqldmo. sqlserver") & create a Server Object
Oserver. Connect ("Snoopy", "sa", "778899") & connect to your server

** Backup
Obackup = Createobject ("sqldmo. Backup") & create a backup object
Obackup. Database = "pubs" & specifies the backup database
Obackup. Password = "8899" & add a password to the backup file
Obackup. Files = "D:/temp/pubback. dat" & specify the target file
Obackup. sqlbackup (oserver) & run the BACKUP command, which is fast

** Backup Recovery
Orestore = Createobject ("sqldmo. Restore ")
Orestore. Database = "pubs"
Orestore. Files = "D:/temp/pubback. dat"
Orestore. sqlrestore
Orestore. sqlrestore (oserver) & password-free trial, backup failed
Orestore. Password = "8899"
Orestore. sqlrestore (oserver)

SQL Server provides several database backup methods,
Full backup, differential backup, log backup, and file backup

Full backup backs up the entire database. Differential backup only creates new changes in the database after the full backup. When the database is very large, full backup is very free of space and time. You can perform it on a regular basis, for example, once a week or every month. You can perform differential backup at other times, such as once a day or once every half a day. You can also perform log backup in combination. File backup is a direct backup of database data and log files. It is the same as backing up data in the operating system.

During restoration, you only need to restore the full backup and then restore the last differential backup. If there is a log backup, you also need to restore the log backup after the differential backup.

The backup type is determined by the Action attribute of the backup object.
Obackup. Action = 0 & parameter: 0-full backup, 1-differential backup, 2-file backup, 3-log backup

 

Server Object Attributes and Methods

Attribute
Autoreconnect: whether to automatically reconnect when the connection is disconnected
Connectionid: ID of the Connection
Hostname: name of the Local Machine
Isdbcreator, isdiskadmin, isprocessadmin, issecurityadmin, isserveradmin, issetupadmin, issysadmin: returns whether the current connected user belongs to a certain type of server role, such as SA

Logintimeout: determines the connection timeout. The default value is 60 seconds.
Salogin: whether the current connection is sa
Status: the current status of the server.
Versionmajor, versionminor: SQL Server version number

Method:

Attachdb, detachdb: Add or detach a database
Connect, disconnect, close: connection, disconnect, or close the current Server Object (oserver)
Verifyconnection: test whether the current connection is disconnected
Reconnect: reconnect
Start, stop, shutdown: Used to start, stop, or shut down the server
Begintransaction, rollbacktransaction, committransaction: Operation transaction
Enumserverattributes: returns most of the server's setting parameters.
Enumlocks: returns all lock processes on the server.
Killprocess: force a process to be interrupted

 

Operate and manage SQL Server in VFP (2) (Introduction to SQL-DMO)
Use DMO to create a task and run it regularly. The execution environment must be NT or Windows 2000, because you need to run the SQL Agent

* ** Create a job)
Ojob = Createobject ("sqldmo. Job") & create a task object
Ojob. Name = "pubs_daily_backup" & Task Name
Oserver. jobserver. Jobs. Add (ojob) & add to the SQL server task
Ojob. beginalter & start to define a task
Ojobstep = Createobject ("sqldmo. jobstep") & create a task step object
Ojobstep. Name = "step_1" & step name

Ojobstep. stepid = 1 & step number
Ojobstep. databasename = "pubs" & Database Name
Ojobstep. subsystem = "tsql" & task type, which can be tsql, cmdexec, or activescripting

Cfname = "pubback" + DTOC (date (), 1) + ". dat" & backup target file name
Ojobstep. Command = "backup database pubs to disk = 'd:/temp/" + cfname + "'with Password = '000000'" & execute the backup tsql command
Ojobstep. onfailaction = 2 & exit if the task fails. You can also set it to send an email or use netsend in NT to send a message.
Ojobstep. onsuccessaction = 1 & if the task is successful, exit.
Ojob. jobsteps. Add (ojobstep) & add task steps
Ojob. startstepid = 1 & starts from step 1 and takes effect for multi-step tasks.
Ojob. doalter & Save changes

*** Add the task to Scheduler

Ojobschedule = Createobject ("sqldmo. jobschedule") & create a schedule object
Ojob = oserver. jobserver. Jobs ("pubs_daily_backup") & create a task object
Ojobschedule. Name = "daily_execution" & name
Ojobschedule. Schedule. frequencytype = 4 & running frequency, 4 is daily
Ojobschedule. Schedule. frequencyinterval = 1 & run interval, 1 is daily
Ojobschedule. Schedule. activestartdate = DTOC (date (), 1) & start date (today), must be in yyyymmdd format
Ojobschedule. Schedule. activestarttimeofday = "233000" & start time (PM), which must be in hhmmss format
** In the following two statements, the task never expires.
Ojobschedule. Schedule. activeenddate = 99991231 & no end date
Ojobschedule. Schedule. activeendtimeofday = 235959 & No End Time

Add a task to schedule
Ojob. beginalter
Ojob. jobschedules. Add (ojobschedule)
Ojob. doalter

The above commands are successfully tested in vfp7, and the newly added commands and running settings are also displayed in SQL Server, but because I use Windows ME, you cannot test whether the task is actually running. Which one has an environment? Can you try it for me?

The stored procedure in the following post is troublesome, but it is relatively simple to call.

------------------------------------------------

SQL Server2000 Database backup and recovery stored procedures

 

I wrote two processes and one function for SQL Server2000 Database backup and recovery.
I will share with you the detailed descriptions of the Process and function in the code.
Thank you.

 

/* Database backup process */
If exists (
Select * From sysobjects
Where name = 'pr _ backup_db 'and xtype = 'P'
)
Begin
Drop proc pr_backup_db
End
Go

 

Create proc pr_backup_db
@ Flag varchar (20) Out,
@ Backup_db_name varchar (128 ),
@ Filename varchar (1000) -- path + file name
As
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
If not exists (
Select * from Master .. sysdatabases
Where name = @ backup_db_name
)
Begin
Select @ flag = 'db not exist'/* The database does not exist */
Return
End
Else
Begin
If right (@ filename, 1) <> '/' and charindex ('/', @ filename) <> 0
Begin
Select @ par = '@ filename varchar (1000 )'
Select @ SQL = 'backup database' + @ backup_db_name + 'to disk = @ filename with init'
Execute sp_executesql @ SQL, @ par, @ filename
Select @ flag = 'OK'
Return
End
Else
Begin
Select @ flag = 'file type error'/* parameter @ filename input format Error */
Return
End
End

 

Go

 

Note: The pr_backup_db process backs up your database.

 

 

/* Create a function and obtain the file path */
If exists (
Select * From sysobjects
Where name = 'fn _ getfilepath' and xtype = 'fn'
)
Begin
Drop function fn_getfilepath
End
Go

 

Create Function fn_getfilepath (@ filename nvarchar (260 ))
Returns nvarchar (260)
As
Begin
Declare @ file_path nvarchar (260)
Declare @ filename_reverse nvarchar (260)
Select @ filename_reverse = reverse (@ filename)
Select @ file_path = substring (@ filename, 1, Len (@ filename) + 1-charindex ('/', @ filename_reverse ))
Return @ file_path
End

 

Go

 

/* Database restoration process */
If exists (
Select * From sysobjects
Where name = 'pr _ restore_db 'and xtype = 'P'
)
Begin
Drop proc pr_restore_db
End
Go

 

Create proc pr_restore_db
@ Flag varchar (20) Out,/* indicates the running status of the process, which is the input parameter */
@ Restore_db_name nvarchar (128),/* Name of the data to be restored */
@ Filename nvarchar (260)/* path for storing the backup file + name of the backup file */
As
Declare @ proc_result tinyint/* return the system stored procedure xp_mongoshell running result */
Declare @ loop_time smallint/* number of cycles */
Declare @ max_ids smallint/* @ maximum number of IDS columns in the TEM table */
Declare @ file_bak_path nvarchar (260)/* original database storage path */
Declare @ flag_file bit/* file storage mark */
Declare @ master_path nvarchar (260)/* database master file path */
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
Declare @ SQL _sub nvarchar (4000)
Declare @ SQL _cmd nvarchar (100)
Declare @ SQL _kill nvarchar (100)
/*
Determine the validity of the @ filename file format to prevent invalid file names such as D: or C://.
The parameter @ Filename must contain '/' and does not end '/'.
*/
If right (@ filename, 1) <> '/' and charindex ('/', @ filename) <> 0
Begin
Select @ SQL _cmd = 'dir' + @ filename
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
If (@ proc_result <> 0)/* Code returned by the system stored procedure xp_cmdshell: 0 (successful) or 1 (failed )*/
Begin
Select @ flag = 'not exist'/* the backup file does not exist */
Return/* exit process */
End
/* Create a temporary table and save the result set consisting of a list of databases and log files contained in the backup set */
Create Table # TEM (
Logicalname nvarchar (128),/* logical name of the file */
Physicalname nvarchar (260),/* Physical name of the file or operating system name */
Type char (1),/* data file (d) or log file (l )*/
Filegroupname nvarchar (128),/* Name of the file group containing the file */
[Size] numeric (20, 0),/* current size (in bytes )*/
[Maxsize] numeric (20, 0)/* maximum allowed size (in bytes )*/
)
/*
Create a table variable. The table structure is basically the same as that of a temporary table.
There are two more columns,
Column IDS (auto-increment Number Column ),
Column file_path: path for storing files
*/
Declare @ TEM table (
IDS smallint identity,/* auto-increment Number Column */
Logicalname nvarchar (128 ),
Physicalname nvarchar (260 ),
File_path nvarchar (260 ),
Type char (1 ),
Filegroupname nvarchar (128)
)
Insert into # TEM
Execute ('Restore filelistonly from disk = ''' + @ filename + '''')
/* Import the temporary table to the table variable and calculate the corresponding path */
Insert into @ TEM (logicalname, physicalname, file_path, type, filegroupname)
Select logicalname, physicalname, DBO. fn_getfilepath (physicalname), type, filegroupname
From # TEM
If @ rowcount> 0
Begin
Drop table # TEM
End
Select @ loop_time = 1
Select @ max_ids = max (IDS)/* @ maximum number of IDS columns in the TEM table */
From @ TEM
While @ loop_time <= @ max_ids
Begin
Select @ file_bak_path = file_path
From @ TEM where IDs = @ loop_time
Select @ SQL _cmd = 'dir' + @ file_bak_path
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
/* System stored procedure xp_mongoshell return code value: 0 (successful) or 1 (failed )*/
If (@ proc_result <> 0)
Select @ loop_time = @ loop_time + 1
Else
Break/* The original storage path of the data file before backup is not found. Exit the loop */
End
Select @ master_path =''
If @ loop_time> @ max_ids
Select @ flag_file = 1/* The original storage path of the data file before backup exists */
Else
Begin
Select @ flag_file = 0/* The original storage path of the data file before backup does not exist */
Select @ master_path = DBO. fn_getfilepath (filename)
From master.. sysdatabases
Where name = 'master'
End
Select @ SQL _sub =''
/* Type = 'D' indicates the data file, and type = 'l' indicates the log file */
/* @ Flag_file = 1 when the new database file is stored in the original path, otherwise the storage path is the same as the master database path */
Select @ SQL _sub = @ SQL _sub + 'move ''' + logicalname + ''' '''
+ Case type
When 'd 'then case @ flag_file
When 1 then file_path
Else @ master_path
End
When 'l' then case @ flag_file
When 1 then file_path
Else @ master_path
End
End
+ Case type
When 'd 'then @ restore_db_name
+ '_ Data'
+ Convert (sysname, IDS)/* number the file */
+ '.'
+ Right (physicalname, 3)/* Add a suffix to the file, MDF or NDF */
+ ''','
When 'l' then @ restore_db_name
+ '_ Log'
+ Convert (sysname, IDS)/* number the file */
+ '.'
+ Right (physicalname, 3)/* Add a suffix to the file, MDF or NDF */
+ ''','
End
From @ TEM
Select @ SQL = 'Restore Database @ db_name from disk = @ filename'
Select @ SQL = @ SQL + @ SQL _sub + 'replace'
Select @ par = '@ db_name nvarchar (128), @ filename nvarchar (260 )'
/* Close the related process and import the Process status to the temporary table */
Select Identity (INT, 1, 1) IDs, spid
Into # temp
From master .. sysprocesses
Where dbid = db_id (@ restore_db_name)
If @ rowcount> 0 -- find the corresponding process
Begin
Select @ max_ids = max (IDS)
From # temp
Select @ loop_time = 1
While @ loop_time <= @ max_ids
Begin
Select @ SQL _kill = 'Kill '+ convert (nvarchar (20), spid)
From # temp
Where IDs = @ loop_time
Execute sp_executesql @ SQL _kill
Select @ loop_time = @ loop_time + 1
End
End
Drop table # temp
Execute sp_executesql @ SQL, @ par, @ db_name = @ restore_db_name, @ filename = @ filename
Select @ flag = 'OK'/* operation successful */
End
Else
Begin
Select @ flag = 'file type error'/* parameter @ filename input format Error */
End

 

Go

 

 

 

 

-- Run

 

-- Backup database test_database
Declare @ FL varchar (10)
Execute pr_backup_db @ FL out, 'test _ database', 'c:/test_database.bak'
Select @ fl

 

-- Restore the database. The input parameter is incorrect.
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'sa ', 'c :/'
Select @ fl

 

-- Restore the database, that is, create a copy of the database test_database test_db
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'test _ db', 'c:/test_database.bak'
Select @ fl

 

The above process and function run successfully in MS SQL2000. Because Ms sql7 does not support user-defined functions and table variables, you can rewrite the function fn_getfilepath to a previous one when using MS sql7.

 

And rewrite the table variable in the pr_restore_db process to a temporary table. If you are interested, try it!

1. Data Backup:
Close all
Clear
Mypath_old = sys (5) + sys (2003)
Mypath_new = alltrim (mypath_old) + '/mydbcb ')
If! Directory ("& mypath_new .")
MD & mypath_new.
Endif
Set default to & mypath_new.
Mydbf_old = GetFile ('bak', '','', 1, 'enter or select the Backup Filename ')
If Len (mydbf_old) = 0
= MessageBox ('You didn't input any file name, and the backup was not successful !! ', 32, 'prompt information ')
Set default to & mypath_old.
Else
Mydbf_path = left (mydbf_old, at ('/', mydbf_old, occurs ('/', mydbf_old)-1)
If! Directory ("& mydbf_path .")
MD & mydbf_path.
Endif
Erase & mydbf_old.
Handle = sqlexec (con, "backup database mydbc to disk =? Mydbf_old ")
Set default to & mypath_old.
If handle> 0
= MessageBox ('(^_^) data is successfully backed up to the' + mydbf_path + 'folder! ', 64, 'database backup ')
Else
= MessageBox (message (), 16, 'database backup ')
Endif
Endif
Ii. Restoration:
Close all
Clear
Mypath_old = sys (5) + sys (2003)
Mypath_new = alltrim (mypath_old) + '/mydbcb ')
Set default to & mypath_new.
Mydbf = GetFile ('bak', '','', 1, 'select the file name to restore ')
Restore from mypath_old + '/myurl/url' Additive
SQL _server = alltrim (my_server)
SQL _path = alltrim (right (SQL _server, Len (SQL _server)-ATC ('/', SQL _server )))
If Len (SQL _path) = 0
SQL _restore_mdf = 'C:/program files/Microsoft SQL Server/MSSQL/data/mydbc. MDF'
SQL _restore_ldf = 'C:/program files/Microsoft SQL Server/MSSQL/data/mydbc_log.ldf'
Else
SQL _restore_mdf = 'C:/program files/Microsoft SQL Server/MSSQL $ '+ SQL _path +'/data/mydbc. MDF'
SQL _restore_ldf = 'C:/program files/Microsoft SQL Server/MSSQL $ '+ SQL _path +'/data/mydbc_log.ldf'
Endif
* Now_timer = 1
Try
= Sqldisconnect (CON)
Catch
Finally
Endtry
= Sqlsetprop (0, "displogin", 3) & never show a message indicating a trusted connection
Myurl = "driver = SQL Server; server =" + alltrim (my_server) +;
"; Uid = sa; Pwd =" + alltrim (my_pwd) +;
"; Database = Master"
Con_master = sqlstringconnect (myurl)
If con_master <0
= MessageBox ('<@ _ @> the local machine has lost connection with the server. You will exit the system! ', 16, 'System information ')
Else
Handle = sqlexec (con_master, "Restore database mydbc from disk =? Mydbf with replace;
, Move 'mydbc _ data'? SQL _restore_mdf ';
, Move 'mydbc _ log'? SQL _restore_ldf '")
If handle> 0
= MessageBox ('success _^ data recovery successful! ', 32, 'Data restore ')
Try
= Sqldisconnect (con_master)
Catch
Finally
Endtry
= Sqlsetprop (0, "displogin", 3) & never show a message indicating a trusted connection
Myurl = "driver = SQL Server; server =" + alltrim (my_server) +;
"; Uid = sa; Pwd =" + alltrim (my_pwd) +;
"; Database = mydbc"
Con = sqlstringconnect (myurl)
If con <0
= MessageBox ('<@ _ @> the local machine has lost connection with the server. You will exit the system! ', 16, 'System information ')
Else
Now_timer = 0
Endif
Else
= MessageBox (message (), 16, 'Data restore ')
Endif
Endif
Set default to & mypath_old.

Data backup does not need to be explained in detail. for data recovery:
1. Note that the current path may be changed when the recovery file is selected. Restore the current path after the data is restored.
2. Test the connection to the data source. Because the SQL2000 database master is built-in, you only need to test whether one of them can be connected.
The user-defined database you want to restore may not be found during restoration, so you need to test the SQL2000 data table.
3. The installation of SQL2000 may involve user-defined instances. For example, if your service instance name is server, the folder generated by SQL2000 is MSSQL $ server rather than MSSQL.
4. disconnect from the current database when restoring data (as described above, use SQL2000's own database as the current connection); otherwise, an exception will occur.
5. After the data recovery is successful, you need to restore the connection to the custom database.