SQL Server Backup Script

Enterprise Manager
--Management
--sql Server Agent
--Right-click Job
--New job
--Enter the job name in the "General" entry
--"Step" item
--New
--Enter the step name in step name
--SELECT "Transact-SQL script (TSQL)" in "type"
--"Database" Select the database that executes the command
--Enter the statement to execute in the command:

-Determine
--"Dispatch" item
--New schedule
--Enter the schedule name in "name"
--Select your job execution schedule in the schedule type
--If "recurring" is selected
--point "change" to set your schedule to one day at a time

The SQL Agent service is then started and set to start automatically, otherwise your job will not be executed

Setup method:
My Computer--control Panel--management tool--service--right-sqlserveragent--Property--Startup Type--select "Auto Start"--OK.

/******************* Full Backup Job *******************/
--full backup, once a week
Use Master
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\FullBak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. Bak '
BACKUP DATABASE [Demo] to [email protected]
With Retaindays=15,noformat,noinit,
Name=n ' Demo full backup ', Skip,norewind,
nounload,stats=10
GO

/******************* differential Backup Job *******************/
--Truncate Log
Use Master
GO
BACKUP LOG Demo with no_log
GO
--Shrink log file
Use Demo
GO
DBCC shrinkfile (N ' Demo_log ', 0,truncateonly)
GO
--differential backup, once per day
Use Master
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\DiffBak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. diff '
BACKUP DATABASE [Demo] to [email protected]
With Differential,retaindays=8,noformat,noinit,
Name=n ' demo differential backup ', Skip,norewind,
nounload,stats=10
GO

/****************** log Backup Job *******************/
--log backups, once per hour
Use Demo
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\logbak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ' , ') + '. Trn '
BACKUP LOG [Demo] to [email protected]
With Retaindays=3,noformat,noinit,
Name=n ' demo log backup ', Skip,norewind,
nounload,stats=10
GO

--Delete outdated backup files two times a day
DECLARE @str varchar (+), @dir varchar (+), @fileName varchar (30)
Set @dir = ' del D:\DBtext\jgj\DBABak\ '
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -15,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Fullbak ' [email protected]+ ' *.bak '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -8,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Diffbak ' [email protected]+ ' *.diff '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -8,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Logbak ' [email protected]+ ' *.trn '
EXEC xp_cmdshell @str

======================================================================
SQL restore
======================================================================
1. Verifying backups
------------------------------------------------------------
Restore HEADERONLY from Bak3
Restore FILELISTONLY from Bak3 with file=1
Restore LABELONLY from Bak3
Restore VERIFYONLY from Bak3
----------------------------------------------------------------------
2. Restore from backup
-------------------------------------------------------------------------
Restore HEADERONLY from BAK1
Restore Database D1 from BAK1 with file=2--recovering from a full backup
----------------------------------------------------------------------
RESTORE HEADERONLY from BAK2--recovering from a differential backup
Restore Database D2 from BAK2 with File=1,norecovery
Restore Database D2 from BAK2 with File=5,recovery
----------------------------------------------------------------------
RESTORE HEADERONLY from Bak3--recovering from a log backup
Restore database D3 from Bak3 with File=1,norecovery
Restore log d3 from Bak3 with File=2,norecovery
Restore log d3 from Bak3 with File=3,norecovery
Restore log d3 from Bak3 with File=4,norecovery
Restore log d3 from Bak3 with File=5,recovery
----------------------------------------------------------------------
Restore database D3 from Bak3 with File=1,norecovery--restore to a specified time
Restore log d3 from Bak3 with File=2,norecovery
Restore log d3 from Bak3 with File=3,norecovery
Restore log d3 from Bak3 with file=4,recovery,stopat= ' 2003-08-15 11:29:00.000 '
----------------------------------------------------------------------
Restore database d5 filegroup= ' FG2 ' from BAK5 with File=4,norecovery--Restoring a filegroup backup
Restore log d5 from Bak5 with File=5,norecovery
Restore log d5 from Bak5 with File=7,recovery
----------------------------------------------------------------------
RESTORE HEADERONLY from Bak6--Restoring a file backup
Restore database d5 file= ' d5_data3 ' from Bak6 with File=6,norecovery
Restore log d5 from Bak6 with File=7,norecovery
Restore log d5 from Bak6 with File=9,recovery
----------------------------------------------------------------------
Restore database D5 from BAK6 with replace--delete an existing database and rebuild it from a backup
----------------------------------------------------------------------
Create database D6--move to move the db file to a new location
On primary
(Name=d6_data,
Filename= ' E:\Program Files\Microsoft SQL Server\mssql\data\d6_data.mdf ',
SIZE=2MB)
Log on
(Name=d6_log,
Filename= ' E:\Program Files\Microsoft SQL Server\mssql\data\d6_log.ldf ',
SIZE=2MB)
Go
BackupDatabase d6 to bak6 with Init
Drop Database d6
Restore Database d6 from Bak6
With move ' d6_data ' to ' e:\data\d6\d6_data.mdf ',
Move ' d6_log ' to ' e:\data\d6\d6_log.ldf '
sp_helpdb d6
----------------------------------------------------------------------
3. Separating and re-connecting the database
--------------------------------------
sp_detach_db ' d6 '
sp_attach_db ' d6 ', ' e:\data\d6\d6_data.mdf ', ' e:\data\d6\d6_log.ldf '
--------------------------------------
sp_detach_db d6
Go
Create Database D6
On primary
(filename= ' e:\data\d6\d6_data.mdf ')
For attach
Go
----------------------------------------------------------------------
4. Recover the damaged system database
----------------------------------------------------------------------
1) first back up master, MSDB
2) Stop the SQL service and delete or rename the master database file. This way, the SQL service will not start.
3) Restore of the system database
-----------------------------------------------
(1) If the SQL service can also start, restore the system database from the backup.
(2) If the SQL service does not start, the system database needs to be rebuilt.
Rebuild the master database using Rebuildm.exe in the SQL folder Tools\Binn directory.
(3) Create a backup device that points to the previous backup device.
(4) Start SQL in single-user mode
CD programe Files\Microsoft SQL Server\mssql\binn
Sqlservr.exe-c-M
(5) Enter the Query Analyzer to recover the master database from the backup.
Restore Database Master from Masterbak
Restore database msdb from disk= ' E:\bak\msdb.bak '
After Master is restored, the information for the user database in SQL is restored.
(6) If Master does not have a backup, you need to attach the user database to the new master database with the sp_attach_db command.

SQL Server Backup Script