SQL SERVER2000 database backup and restore stored procedures (enhanced version).txt

SQLSERVER2000 database backup and restore stored procedures (enhanced version)

two procedures and a function I wrote myself for SQLSERVER2000 database backup and recovery

Take it out and communicate with you, the detailed description with the course of action and function is in the code

Thank you

/*The method of backing up the database*/

ifexists(select*fromsysobjectswherename='pr_backup_db'andxtype='p')

begindropprocpr_backup_db

finish

go

createprocpr_backup_db

@flagvarchar(20)out,

@backup_db_namevarchar(128),

@filenamevarchar(1000)--path + file name

as

declare@sqlnvarchar(4000), @parnvarchar(1000)

ifnotexists(select*frommaster..sysdatabaseswherename=@backup_db_name)

beginselect@flag='dbnotexist'/*Database will not exist*/return

finish

else

beginifright(@filename,1)'\\'andcharindex('\\',@filename)0beginselect@par='@filenamevarchar(1000)'select@sql='BACKUPDATABASE'+@backup_db_name+'todisk=@filenamewithinit'executesp_executesql@sql, @par,@filenameselect@flag='ok'returnendelsebeginselect@flag='filetypeerror'/*Parameter @filename input format error*/returnend

finish

GO

Note: The pr_backup_db procedure should be to backup your database

/*Create a function to obtain the file path*/

ifexists(select*fromsysobjectswherename='fn_GetFilePath'andxtype='fn')

begindropfunctionfn_GetFilePath

finish

go

createfunctionfn_GetFilePath(@filenamenvarchar(260))

returnsnvarchar(260)

as

begindeclare@file_pathnvarchar(260)declare@filename_reversenvarchar(260)select@filename_reverse=reverse(@filename)select@file_path=substring(@filename,1,len(@filename)+1-charindex('\\',@filename_reverse)) return@file_path

end

GO

/*The procedure of restoring the database*/

ifexists(select*fromsysobjectswherename='pr_restore_db'andxtype='p')

begindropprocpr_restore_db

finish

go

CREATEprocpr_restore_db

@flagvarchar(20)out,/*The status flag of the procedure operating, is definitely the input parameter*/

@restore_db_namenvarchar(128), /*Data name to become restored*/

@filenamenvarchar(260)/*The path exactly where the backup file is stored+the name of your backup file*/

as

declare@proc_resulttinyint/*Return the operating outcome of method stored procedure xp_cmdshell*/

declare@loop_timesmallint/*Number of loops*/

declare@max_idssmallint/*Maximum quantity of ids column in @tem table*/

declare@file_bak_pathnvarchar(260)/*The original database storage path*/

declare@flag_filebit/*File storage flag*/

declare@master_pathnvarchar(260)/*Database master file path*/

declare@sqlnvarchar(4000), @parnvarchar(1000)

declare@sql_subnvarchar(4000)

declare@sql_cmdnvarchar(one hundred)

declare@sql_killnvarchar(one hundred)

/*

Determine the legality of your parameter @filename file format to prevent customers from entering illegal file names like d: or c:\\a\\

The parameter @filename must have '\\' inside and not finish with '\\'

*/

ifright(@filename,1)'\\'andcharindex('\\',@filename)0

beginselect@sql_cmd='dir'+@filenameEXEC@proc_result=master..xp_cmdshell@sql_cmd,no_outputIF(@proc_result0)/*System stored process xp_cmdshell return code worth: 0 (achievement) or 1 (failure)*/beginselect@flag= 'notexist'/*Backup file doesn't exist*/return/*Exit process*/end/*Create a short-term table and save the outcome set consisting from the database and log file list contained inside the backup set*/createtable#tem(LogicalNamenvarchar(128 ), /*logical name with the file*/PhysicalNamenvarchar(260), /*physical name on the file or operating program name*/Typechar(1), /*data file (D) or log file (L)*/FileGroupNamenvarchar(128 ),/*The name on the file group containing the file*/[Size]numeric(20,0),/*Current size (in bytes)*/[MaxSize]numeric(20,0)/*Maximum size allowed (In bytes) */)/* Build a table variable, the table structure is basically exactly the same as the short-term table is the fact that there are actually two additional columns, column ids (increment quantity column), column file_path, the path to store the file */declare@temtable (idssmallintidentity,/*Auto increment column*/LogicalNamenvarchar(128),PhysicalNamenvarchar(260),File_pathnvarchar(260),Typechar(1),FileGroupNamenvarchar(128))insertinto#temexecute('restorefilelistonlyfromdisk=''+@filename+' ''')/*Import the temporary table in to the table variable and calculate the corresponding path*/insertinto@tem(LogicalName,PhysicalName,File_path,Variety,FileGroupName)selectLogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Form,FileGroupNamefrom #temif@@rowcount0begindroptable#temendselect@loop_ti me=1select@max_ids=max(ids)/*Maximum variety of ids column in @tem table*/from@temwhile@loop_time=@max_idsbeginselect@file_bak_path=file_pathfrom@temwhereids=@loop_timeselect@sql_cmd='dir'+@file_bak_pathEXEC@proc_result =master..xp_cmdshell@sql_cmd,no_output/*System stored process xp_cmdshell return code value: 0 (success) or 1 (failure)*/IF(@proc_result0)select@loop_time=@loop_time+1elseBREAK/*No pre-backup data discovered The original storage path with the file, exit the loop */endselect@master_path=''if@loop_time@max_idsselect@flag_file=1/*The original storage path on the data file before backup exists*/elsebeginselect@flag_file=0/*The original data file prior to backup There's a storage path will not exist*/select@master_path=dbo.fn_GetFilePath(filename)frommaster..sysdatabaseswherename='master'endselect@sql_sub=''/*type='d' is a data file, type='l' can be a log file *//*@flag_file=1 The new database file is still stored in the original path, otherwise the storage path would be the exact same as the master database path*/select@sql_sub=@sql_sub+'move'''+LogicalName+'''to'''+ casetypewhen'd'thencase@flag_filewhen1thenFile_pathelse@master_pathendwhen'l'thencase@flag_filewhen1thenFile_pathelse@master_pathendend+casetypewhen'd'then@restore_db_name+'_DATA'+convert(sysname,ids)/*Give the file number*/+'+'+' ,three)/*Add a suffix name to the file, mdforndf*/+''','when'l'then@rest ore_db_name+'_LOG'+convert(sysname,ids)/*Number the file*/+'.' parted magic recover partition table (PhysicalName,3)/*Add a suffix name for the file, mdforndf*/+''','endfrom@temselect@ sql='RESTOREDATABASE@db_nameFROMDISK=@filenamewith'select@sql=@sql+@sql_sub+'replace'select@par='@db_namenvarchar(128),@filenamenvarchar(260)'/*Close associated processes and import the corresponding course of action status temporarily */Selectidentity(int,1,1)ids, spidinto#tempfrommaster..sysprocesseswheredbid=db_id(@restore_db_name)if@@rowcount0--find the corresponding course of action beginselect@max_ids=max(ids)from#tempselect@loop_time=1while @loop_time=@max_idsbeginselect@sql_kill='kill'+convert(nvarchar(20),spid)from#tempwhereids=@loop_timeexecutesp_executesql@sql_killselect@loop_time=@loop_time+1endenddroptable#tempexecutesp_executesql@sql,@par,@db_name=@restore_db_name, @filename=@filenameselect@flag='ok'/*Operation succeeded*/

finish

else

beginSELECT@flag='filetypeerror'/*Parameter @filename input format error*/

finish

GO

--run

--Backup database test_database

declare@flvarchar(ten)

executepr_backup_db@flout,'test_database','c:\\test_database.bak'

select@fl

- Restore the database, the input parameters are wrong

declare@flvarchar(20)

execpr_restore_db@flout,'sa','c:\\'

select@fl

--Restore the database, that is certainly, create a copy on the database test_database test_db

declare@flvarchar(20)

execpr_restore_db@flout,'test_db','c:\\test_database.bak'

select@fl

The above process and function run successfully in MSSQL2000. Since hard drive recover partition table does not assistance user-defined functions and table variables, the function fn_GetFilePath might be rewritten to be utilized under MSSQL7.

alter table recover partitions impala , rewrite the table variables within the method pr_restore_db as a short-term table to run, interested buddies can try!