T-SQL statement realizes database backup and restore function

I use T-SQL statements to complete the restore of the database backup function.

It should be noted that the following stored procedures were searched online. Thank you for the code provider. Reproduced here

Using T-SQL statement to realize the function of database backup and restore

Embodies four of the knowledge points in SQL Server:

1. Get the default directory on the SQL Server server

2. Use of Backup SQL statements

3. Restore the use of SQL statements while considering the process of shutting down other user processes while forcing recovery

4. Use of job creation SQL statements

1. Access to the file directory of the database

@dbname Specify the name of the database to get the directory

Returns the default data directory set when installing SQL if the specified data does not exist

If NULL is specified, the default SQL backup directory name is returned

Call Example

select 数据库文件目录=dbo.f_getdbpath('tempdb')
,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')
,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdbpath]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdbpath]
GO
create function f_getdbpath(@dbname sysname)
returns nvarchar(260)
as
begin
declare @re nvarchar(260)
if @dbname is null or db_id(@dbname) is null
select @re=rtrim(reverse(filename)) from master..sysdatabases where name='master'
else
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname
if @dbname is null
set @re=reverse(substring(@re,charindex('\',@re)+5,260))+'BACKUP'
else
set @re=reverse(substring(@re,charindex('\',@re),260))
return(@re)
end
go