1 We usually need to log on to the database server to back up and recover the database. This is inconvenient. In fact, the SQL server command may make it easy for us to remotely use ASP..
2 Backup Database ' Name of the database to be backed up ' To Disk = ' Backup File Path ' ;
3 Alter Database ' Name of the recovered Database ' Set offline with roolback immediate;
4 Restore database ' Name of the recovered Database ' From Disk = ' Backup File Path ' ;
5 Alter Database ' Name of the recovered Database ' Set Online with roolback immediate;
6
7 A few simple commands can be used to back up and restore the database, in ASP. net, you can call these four statements just like calling SQL statements to easily back up and restore the SQL Server database.
8
9 Example (taking northwind as an example ):
10 1 Back up the northwind database to the C root directory ' Northwind. Bak ' Name:
11 Backup Database ' Northwind ' To Disk = ' C: \ northwind. Bak ' ;
12
13 2 , Restore the northwind database, under the C root directory ' Northwind. Bak ' Backup file:
14 A. Place the database offline
15 Alter Database ' Northwind ' Set offline with roolback immediate;
16 B. Restore the northwind database
17 Restore database ' Northwind ' From Disk = ' C: \ northwind. Bak ' ;
18 C. Place the database online
19 Alter Database ' Northwind ' Set Online with roolback immediate;
20
21 Use stored procedures:
22 Create proc backupdb
23 @ Dbname sysname = '' , -- Name of the database to be backed up. If this parameter is not specified, the current database is backed up.
24 @ Bkpath nvarchar ( 260 ) = '' , -- Backup file storage directory. If this parameter is not specified, the default SQL Backup Directory is used.
25 @ Bkfname nvarchar ( 260 ) = '' , -- Backup file name. \ dbname \ can be used in the file name to represent the database name, \ date \ represents the date, \ time \ represents the time
26 @ Bktype nvarchar ( 10 ) = ' DB ' , -- Backup Type: ' DB ' Back up the database, ' DF ' Differential backup, ' Log ' Log backup
27 @ Appendfile bit = 1 -- Append / Overwrite backup file
28 As
29 Declare @ SQL varchar ( 8000 )
30 If Isnull (@ dbname, '' ) = '' Set @ Dbname = Db_name ()
31 If Isnull (@ bkpath, '' ) = '' Set @ Bkpath = DBO. f_getdbpath ( Null )
32 If Isnull (@ bkfname, '' ) = '' Set @ Bkfname = ' \ Dbname \ _ \ date \ _ \ time \. Bak '
33 Set @ Bkfname = Replace (replace (@ bkfname, ' \ Dbname \ ' , @ Dbname)
34 , ' \ Date \ ' , Convert (varchar, getdate (), 112 ))
35 , ' \ Time \ ' , Replace (convert (varchar, getdate (), 108 ), ' : ' , '' ))
36 Set @ SQL = ' Backup ' + Case @ Bktype when ' Log ' Then ' Log ' Else ' Database ' End + @ Dbname
37 + ' To disk = ''' + @ Bkpath + @ Bkfname
38 + ''' With ' + Case @ Bktype when ' DF ' Then ' Differential, ' Else '' End
39 + Case @ Appendfile when 1 Then ' Noinit ' Else ' Init ' End
40 Print @ SQL
41 Exec (@ SQL)
42
43
44 Go
45
46
