C#.net SQL database backup and restore solution

First set up 3 stored procedures and a backup history table in the master database:
1. p_backupdb
2. P_restoredb
3. P_createjob
4. Sys_backuphistory--Backup history table

1. p_backupdb Backup Database stored Procedures

ALTER PROC [dbo]. [p_backupdb] @DBNAME SYSNAME="',       --The name of the database to be backed up, without specifying that the current database is backed up @BKPATH NVARCHAR (260)="', --The storage directory for the backup file, without specifying the default backup directory for SQL @BKFNAME NVARCHAR (260)="',--backup file name, the file name can use \dbname\ to represent the database name, \date\ represents the date, \time\ represents the time @BKTYPE NVARCHAR (Ten)='DB',--Backup Type:'DB'Back up the database,'DF'Differential backup,'LOG'log backup @APPENDFILE BIT=1--Append/overwrite backup files as BEGIN/*p_backupdb ' csframework22.normal ', ' C:\db_backup\ ' p_backupdb ' csframework22.test ', ' C:\db_backup\ ' p_backupdb ' Csframework22.system ', ' C:\db_backup\ '*/DECLARE @SQL VARCHAR (8000) IF ISNULL (@DBNAME,"')="'SET @DBNAME =db_name ()--Current Database IF ISNULL (@BKPATH,"')="'SET @BKPATH =Dbo.f_getdbpath (NULL) IF ISNULL (@BKFNAME,"')="'SET @BKFNAME ='\dbname\_\date\_\time\. BAK'SET @BKFNAME=replace (replace (replace (@BKFNAME,'\dbname\ ', @DBNAME),'\date\ ', CONVERT (Varchar,getdate (), () ),'\time\ ', REPLACE (CONVERT (Varchar,getdate (), 108),':',"')) SET @SQL='BACKUP'+case @BKTYPE when'LOG'Then'LOG'ELSE'DATABASE'END+'['[Email protected]+']'+'To disk=" "[Email protected]+@BKFNAME+" " with'+case @BKTYPE when'DF'Then'differential,'ELSE"'END+case @APPENDFILE when1Then'Noinit'ELSE'INIT'END PRINT @SQL EXEC (@SQL) IF @ @ERROR=0BEGIN PRINT'BACKUP LOG'INSERT into Dbo.sys_backuphistory (dbname,backupfilename,backuppath,backuptime) VALUES (@DBNAME, @BKFNAME, @BKPATH+@BKFNAME, GETDATE ()) EndEnd

2. P_restoredb restoring a database stored procedure

CREATE PROC [dbo]. [P_restoredb] @BKFILE NVARCHAR ( +),   --define the backup file name to restore @DBNAME SYSNAME,--defines the restored database name, which defaults to the file name of the backup @RETYPE NVARCHAR (Ten)='DB',--Recovery type:'DB'Full Recovery database,'Dbnor'For differential recovery, log recovery for full recovery,'DF'Recovery of differential backups,'LOG'Log recovery @FILENUMBER INT=1,        --recovered file number @OVEREXIST BIT=1--whether to overwrite the existing database, only @retype as BEGIN/*--Restore Database P_restoredb ' C:\db_backup\CSFramework22.Normal_20110924_213838.BAK ', ' csframework22.normal '-- View the contents of a backup file restore filelistonly from disk= ' C:\db_backup\CSFramework22.Normal_20110924_213838.BAK '*/DECLARE @SQL VARCHAR (8000)     --gets the restored database name IF ISNULL (@DBNAME,"')="'SELECT @SQL=REVERSE (@BKFILE), @SQL=case when CHARINDEX ('.', @SQL) =0Then @SQL ELSE SUBSTRING (@SQL, CHARINDEX ('.', @SQL) +1, +) END, @SQL=case when CHARINDEX ('\ ', @SQL) =0 then @SQLELSE left (@SQL, CHARINDEX ('\ ', @SQL)-1) END, @DBNAME =REVERSE (@SQL)--generate Database Recovery statement SET @SQL='RESTORE'+case @RETYPE when'LOG'Then'LOG'ELSE'DATABASE'END+'['[Email protected]+']'+'From disk=" "[Email protected]+" '"+'With file='+CAST (@FILENUMBER as VARCHAR)+case when @OVEREXIST =1and @RETYPE in ('DB','Dbnor') Then', REPLACE'ELSE"'END+case @RETYPE when'Dbnor'Then', NORECOVERY'ELSE', RECOVERY'END--set the current database offline status EXEC ('ALTER DATABASE ['[Email protected]+'] SET OFFLINE with ROLLBACK IMMEDIATE')      --Recover Database EXEC (@SQL)--set the current database connection status EXEC ('ALTER DATABASE ['[Email protected]+'] SET ONLINE') END

3. p_createjob creating job stored Procedures

(Because SQLExpress 2005 does not have a proxy service, you can create jobs, but you cannot run them.) SQL2008 version available)
4. sys_backuphistory Backup History table

Common Errors and workarounds:
1. Backup Errors

SQL error:

BACKUP DATABASE csframeworkpermission to disk= ' C:\CSFrameworkPermission_20110923_104732.BAK ' with Noinit
MSG 3201, Level A, State 1, line 1
Cannot open backup device ' C:\CSFrameworkPermission_20110923_104732.BAK '. Operating system error 5 (Access denied. ).
MSG 3013, Level A, State 1, line 1
BACKUP DATABASE is terminating abnormally.

The operating system error (Access denied) is prompted because the user who is currently establishing the connection (csframework) does not have server permissions to establish the file.
Other tips:
Similar to the problem with insufficient NT AUTHORITY\SYSTEM permissions, in service management, log on to SQL Server (MSSQLSERVER) and SQL Server Agent (MSSQLSERVER). Two services (log on) to Domain account with permissions on the 192.168.0.2
Workaround:

Open the SQL Server Configuration Management tool, and on the log on page there are two ways to log on to the server.
1. build-in account, select Local System.
2. This account, which specifies a login user, can use the administrator user.

2. Restore Errors

Under normal circumstances set up the first step has read and write file permissions, generally do not error. If there are still errors, specify the dbcreator role for the current user.

3. Error while writing backup history

The SELECT permission is denied on the object ' sys_backuphistory ', database ' master ', schema

Add:

If you encounter any of the following

In this case, don't worry, check the control Panel, uninstall

This image is already processed, uninstalling 2012 of the client and LocalDB

OK, solve

C#.net SQL database backup and restore solution