Back up/restore the system database

1. Back up/restore the system database in the SQL Server database. The system information is stored in the system database. The main system databases include:
Master-controls user database and SQL server operations as a whole. After creating any User-Defined Object, you must back up it.
Model-provides templates and prototypes for new databases
MSDB-backup methods for jobs, alarms, operators, and other information are the same as normal databases. After backup, copy the backup file to another SQL Server and restore. to enter the single-user mode:
1. In command line mode, enter sqlservr-C-F-M or sqlservr-M.
Among them,-C can shorten the startup time, and SQL server is not started as a Windows NT Service.
-F use the minimum configuration to start SQL Server
-M single-user mode to start SQL Server 2. You can enter-C-F-M or-m in the startup parameters of control panel-service-MSSQLServer, and click Start

3. Restore the master database
A. go directly to the query analyzer and have a prompt to ignore it.
Enter the recovery statement to restore the database:
Restore database master from disk = 'C:/specific backup file name 'B. Alternatively, enter the file name at the command prompt. Note that the file name is case sensitive.
For "Windows Authentication", enter iSQL/E
For "SQL Server and Windows Authentication", enter the iSQL/u User Name "/P" password"
Then input the following prompt (Note 1>, 2> yes ):
1> Restore database master from disk = 'C:/specific backup file'
2> go 2. -- 1. create a directory to save the backup database, create the stored procedure in the master database, and call it to back up the user database. /* -- back up all databases
 
The backup file name is the database name +. Bak -- dig 2003.10 -- * // * -- call example -- backup all user Databases
Exec p_backupdb @ bkpath = 'C:/', @ dbname = ''-- backup the specified database
Exec p_backupdb @ bkpath = 'C:/', @ dbname = 'customer profile, xzkh_new'
-- */If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_backupdb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_backupdb]
Gocreate proc p_backupdb
@ Bkpath nvarchar (260) = '', -- specifies the directory where backup files are stored. If this parameter is not specified, the default SQL Backup Directory is used.
@ Dbname nvarchar (4000) = ''-- List of database names to be backed up. If this parameter is not specified, all user databases are backed up.
As
Declare @ SQL varchar (8000) -- check parameters
If isnull (@ bkpath, '') =''
Begin
Select @ bkpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ bkpath = substring (@ bkpath, charindex ('/', @ bkpath) + 1,4000)
, @ Bkpath = reverse (substring (@ bkpath, charindex ('/', @ bkpath), 4000) + 'backup /'
End
Else if right (@ bkpath, 1) <> '/' set @ bkpath = @ bkpath + '/' -- get the list of databases to be backed up
If isnull (@ dbname, '') =''
Declare TB cursor local
Select name from Master .. sysdatabases where name not in ('master', 'tempdb', 'model', 'mdb ')
Else
Declare TB cursor local
Select name from Master .. sysdatabases
Where name not in ('master', 'tempdb', 'model', 'msdb ')
And (@ dbname like '%,' + name + ', %' or @ dbname like name + ', %' or @ dbname like '%,' + name) -- backup Processing
Open TB
Fetch next from TB into @ dbname
While @ fetch_status = 0
Begin
Set @ SQL = 'backup database' + @ dbname
+ 'To disk = ''' + @ bkpath + @ dbname
+ '. Bak' with init'
Exec (@ SQL)
Fetch next from TB into @ dbname
End
Close TB
Deallocate TB
Go -- copy all the user databases backed up above to another server. create the following stored procedure in the master database and use it to restore all user databases. /* -- restore all databases in the specified directory. The recovered database name is the backup file name (excluding the extension)
The extension of the backup file is fixed as. Bak -- dig build 2003.10 -- * // * -- call example
-- Restore all databases in the specified directory
Exec p_restoredb @ bkpath = 'C:/'-- restore the specified database in the specified directory
Exec p_restoredb @ bkpath = 'C:/', @ bkfile = 'customer profile, xzkh_new'
-- */If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_restoredb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_restoredb]
Gocreate proc p_restoredb
@ Bkpath nvarchar (1000) = '', -- defines the directory where backup files are stored. The default value is the SQL Backup Directory.
@ Bkfile nvarchar (4000) = '', -- defines the backup file name to be restored, excluding the extension
@ Dbpath nvarchar (260) = '', -- the restored database storage directory. If this parameter is not specified, it is the default SQL data directory.
@ Overexist bit = 1, -- whether to overwrite existing databases. It is valid only when @ retype is 'db'/'dbnor '.
@ Killuser bit = 1 -- whether to disable the user process. This parameter is only valid when @ overexist = 1.
As
Declare @ SQL varchar (8000), @ dbname sysnameif isnull (@ bkpath, '') =''
Begin
Select @ bkpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ bkpath = substring (@ bkpath, charindex ('/', @ bkpath) + 1,4000)
, @ Bkpath = reverse (substring (@ bkpath, charindex ('/', @ bkpath), 4000) + 'backup /'
End
Else if right (@ bkpath, 1) <> '/' set @ bkpath = @ bkpath + '/' -- the recovered database storage directory
If isnull (@ dbpath, '') =''
Begin
Select @ dbpath = rtrim (reverse (filename) from Master .. sysfiles where name = 'master'
Select @ dbpath = reverse (substring (@ dbpath, charindex ('/', @ dbpath), 4000 ))
End
Else if right (@ dbpath, 1) <> '/'set @ dbpath = @ dbpath +'/'-- get all the backup files in the specified directory
Create Table # T (fname varchar (260), depth int, ISF bit)
Insert into # T exec master .. xp_dirtree @ bkpath, 1, 1if isnull (@ bkfile, '') =''
Declare TB cursor local for select fn = left (fname, patindex ('%. Bak', fname)-1) from # T
Where ISF = 1 and fname like '%. Bak' -- Obtain the. Bak File
Else
Begin
Set @ bkfile = ',' + Replace (@ bkfile, '. Bak,') + '. Bak ,'
Declare TB cursor local for select fn = left (fname, patindex ('%. Bak', fname)-1) from # T
Where ISF = 1 and fname like '%. Bak' and @ bkfile like '%,' + fname + ', %'
End -- Restore database processing
Open TB
Fetch next from TB into @ dbname
While @ fetch_status = 0
Begin
-- Generate database recovery statements
Set @ SQL = 'Restore database' + @ dbname
+ 'From disk = ''' + @ bkpath + @ dbname + '. bak '''
+ 'With recovery'
+ Case when @ overexist = 1 then', replace 'else' end -- add the processing of Mobile logical files
-- Obtain the logical file name from the backup file
Declare @ LFN nvarchar (128), @ TP char (1), @ I int -- create a temporary table and save the obtained information
Create Table # Tb (LN nvarchar (128), PN nvarchar (260), TP char (1), FGN nvarchar (128), SZ numeric (20, 0 ), msz numeric (20, 0 ))
-- Obtain information from the backup file
Insert into # TB exec ('Restore filelistonly from disk = ''' + @ bkpath + @ dbname + '. Bak ''')
Declare # F cursor local for select ln, TP from # TB order by TP
Open # F
Fetch next from # F into @ LFN, @ TP
Set @ I = 0
While @ fetch_status = 0
Begin
Select @ SQL = @ SQL + ', move ''' + @ LFN + ''' to ''' + @ dbpath + @ dbname + Cast (@ I as varchar)
+ Case @ TP when 'd 'then'. MDF ''' else'. ldf''' end
, @ I = @ I + 1
Fetch next from # F into @ LFN, @ TP
End
Close # F
Deallocate # F
Drop table # TB -- Disable User process Processing
If @ overexist = 1 and @ killuser = 1
Begin
Declare @ spid varchar (20)
Declare # spid cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ dbname)
Open # spid
Fetch next from # spid into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # spid into @ spid
End
Close # spid
Deallocate # spid
End -- Restore database
Exec (@ SQL)
Fetch next from TB into @ dbname
End
Close TB
Deallocate TB
Go