Use SQL statements to back up, restore, and SQL statements to back up databases

Use SQL statements to back up, restore, and SQL statements to back up databases

Only one method is used here. Back up the database file to the disk and restore it.

/* 2: Use an SQL statement TO back up DATABASE 3: */4: BACKUP DATABASE mydb 5: TO DISK = 'C: \ DBBACK \ mydb. BAK '6: -- specifies the path and file name of the database to be backed up. Note: The Path folder must have been created. the file name can be identified by date 7: 8:/* 9: restore database 10: */11: USE master 12: restore database mydb 13: from disk = 'C: \ DBBACK \ mydb. BAK '14: WITH REPLACE

　　

Note: in many cases, the data cannot be restored directly because it is not exclusively opened. The following process may be used.

1: -- Kill the connection to a database. 2: create proc KillSpid (@ DBName varchar) 3: AS 4: BEGIN 5: DECLARE @ SQL varchar 6: DECLARE @ SPID int 7: SET @ SQL = 'Clare CurrentID CURSOR FOR 8: SELECT spid FROM sysprocesses WHERE dbid = db_id (''' + @ DBName + ''') '9: fetch next from CurrentID INTO @ SPID 10: WHILE @ FETCH_STATUS <>-1 11: BEGIN 12: exec ('Kill '+ @ SPID) 13: fetch next from CurrentID INTO @ SPID 14: END 15: CLOSE CurrentID 16: DEALLOCATE CurrentID 17: END

　　

It is best to use a single user to operate the database after kill the user

SP_DBOPTION @DBName,'single user','true'