/*--backup database
Zou Jian 2003.10 - * /
/ * - Call example
- Back up the current database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _db.bak'
- Difference backup Current Database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _df.bak', @ bktype = 'DF'
- Backup Current Database Log EXEC P_BACKUPDB @ Bkpath = 'C: /', @ bkfname = 'db_ / date / _log.bak', @ bktype = 'log'
- * /
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] Go
CREATE PROC P_BACKUPDB @ DBNAME SYSNAME = ', - To back up the database name, not specified, back up the current database @BKPATH NVARCHAR (260) =' ', the storage directory of the backup file, not specified, use SQL default backup Directory @BKFNAME NVARCHAR (260) = ', - Backup file name, file name can be used in the file name, / DBNAME / representative database name, / Date / representative date, / time / representative time @BkType nvarchar (10) =' db ' , - Backup Type: 'DB' Backup Database, 'DF' Difference Backup, 'Log' Log Backup @Appendfile Bit = 1 - Adding / Overlay Backup File as Declare @SQL VARCHAR (8000) if Isnull (@dbname, ' ') =' 'set @ dbname = db_name () if isnull (@BKPATH,' ') =' 'set @ bkpath = dbo.f_getdbpath (null) if isnull (@BKFNAME,' ') =' 'set @ bkfname = '/Dbname/_/date/_/time/.bak' set @ bkfname = replace (Replace (@BKFNAME, '/ DBNAME /', @ dbname), '/ Date /', Convert (varchar, getdate ), 112), '/ TIME /', REPLACE (Convert (varchar, getdate (), 108), ':', '')) set @ SQL = 'backup' case @Bktype when 'log' Then ' Log 'Else' Database 'end @ dbname ' to disk = '' ' @ bkpath @ bkfname ' '' with ' case @Bktype when' DF 'TEN' DIFFERENTIAL, 'ELSE' END CASE @Appendfile When 1 TEN 'NOINIT' Else 'INIT' End Print @SQL EXEC (@SQL) GO ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - / * - Restore database
Zou Jian 2003.10 - * /
/ * - Call example - Complete recovery database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'DB'
- Difference backup recovery EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'Exec p_backupdb @ bkfile =' c: /db_20031015_df.bak ', @ dbname =' DB ', @ rettype =' df '- log backup recovery EXEC P_RESTOREDB @ bkfile =' c: /db_20031015_db.bak ', @ retype =' dbnor'Exec p_backupdb @ bkfile = 'C: / DB_20031015_log.bak ', @ dbname =' db ', @ rettype =' log '
- * /
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] Go
CREATE PROC P_RESTOREDB @ bkfile nvarchar (1000), - Define Backup file name @dbname sysname = '', - Define the restored database name, default for backup file name @dbpath nvarchar (260) = '' , - Restore data inventory placement, not specified, the default data directory @retype nvarchar (10) @retype nvarchar (10) = 'DB', - Recovery type: 'DB' Financial Recovery Database, 'DBNOR' Recovery, Log Recovery for complete recovery, 'DF' Difference backup recovery, 'log' log recovery @FileNumber int = 1, - Restore file number @overexist bit = 1, - Only override existing database, only @RetyPE is @ @retype Killuser bit = 1 - Do you turn off the user's use process, only @ overexist = 1 time effective asclare @SQL varchar (8000)
- Get recovered database name if isnull (@dbname, ') =' 'select @ SQL = Reverse (@BKFILE), @ SQL = Case When Charindex ('. ', @ SQL) = 0 Then @SQL ELSE Substring (@ SQL, Charindex ('.', @ SQL) 1,1000) End, @ SQL = Case When Charindex ('/', @ SQL) = 0 Then @SQL Else Left (@ SQL, Charindex ('/ ', @ SQL) -1) end, @ dbname = reverse (@SQL)
- Get recovered data inventory to put the directory if isnull (@dbpath, '') = '' set @ dbpath = dbo.f_getdbpath ('') - Generate Database Recovery Statement Set @ SQL = 'Restore' Case @retype by 'Log' TEN 'LOG' Else 'Database' end @ dbname 'from disk =' ' @ bkfile ' '' 'with file =' cast (@filenumber as varchar) case when @ Overexist = 1 AND @RETYPE IN ('DB', 'DBNOR') THEN ', REPLACE' ELSE 'END CASE @Retype when' DBNOR 'TEN', NORECOVERY 'ELSE', Recovery 'endprint @ SQL - Add mobile logical file Processing if @ Retype = 'db' or @ rettype = 'dbnor'begin - Gets logic filename from backup file Declare @LFN NVARCHAR (128), @ TP char (1), @ i int
- Create a temporary table, save the acquired information Create Table #TB (ln nvarchar (128), PN NVARCHAR (260), TP Char (1), FGN NVARCHAR (128), SZ Numeric (20, 0), MSZ NUMERIC 20, 0)) - Get information from backup file Insert Into #tb Exec ('RESTORE FILELISTONLY from Disk =' ' @ bkfile ' '') Declare #f Cursor for Select LN, TP from #tb Open # fetch next from #f Into @ lfn, @ TP set @ i = 0 while @@ fetch_status = 0 begin select @ SQL = @ SQL ', Move' '' '' ' @ dbpath @ DBNAME CAST (@i as varchar) case @tp when 'd' Ten '.mdf' '' Else '' '' 'end, @ i = @ i 1 fetch next from #f INTO @ lfn, @ Tp end close #f deallocate #fnd
- Close User Process Processing if @ Overexist = 1 and @ Killuser = 1begin Declare @Spid Varchar (20) Declare #SPID CURSOR for 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 # spidend-- recover the database exec (@SQL)
GO / * - Create job
Zou Jian 2003.10 - * /
/ * - Call example
- Monthly Job EXEC P_CREATEJOB @ JobName = 'mm', @ SQL = 'SELECT * from syscolumns', @freqtype = 'MONTH'
- Weekly Job EXEC P_CREATEJOB @ JobName = 'WW', @ SQL = 'SELECT * from syscolumns', @freqtype = 'Week'
- Daily execution EXEC P_CREATEJOB @ JobName = 'a', @ SQL = 'SELECT * from Syscolumns'
- Daily execution work, repeated job exec p_createjob @ JobName = 'b', @ SQL = 'select * from syscolumns' every day, @ SQL = 'Select * from syscolumns', @ fsinterval = 4
- * / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_createjob]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_createjob ] Go
Create Proc P_createJob @ JobName VARCHAR (100), - Job Name @SQL VARCHAR (8000), - The command @dbname sysname = '', - default is the current database name @freqType varchar (6) = ' Day ', time period, month, week week, day @fsinterval int = 1, - relative to daily repetition @time int = 170000 - start execution time, for repeated jobs, 0 points to 23:59 ASIF Isnull (@dbname, ') =' 'set @ dbname = db_name ()
- Create Jobs EXEC MSDB..SP_ADD_JOB @ Job_Name = @ JobName
- create a job step exec msdb..sp_add_jobstep @ job_name = @ jobname, @step_name = 'Data Processing', @subsystem = 'TSQL', @ database_name = @ dbname, @command = @sql, @retry_attempts = 5, - Retry Tips @retry_interval = 5 - Retry Interval - Create Scheduling Declare @ftype Int, @ fstype Int, @ ffactor Intselect @ ftype = Case @freqType When 'Day' Ten 4 WHEN 'Week' Then 8 WHEN 'MONTH' THEN 16 end, @ fstype = case @fsinterval when 1 Then 0 else 8 endif @fsinterval <> 1 set @ Time = 0set @ ffactor = case @freqtype when 'day' Then 0 else 1 End
EXEC MSDB..SP_ADD_JOBSCHEDULE @ Job_Name = @ JobName, @name = 'Time Arranger', @freq_type = @ ftype, - Daily, 8 week, 16 month @freq_interval = 1, - Repeat execution time @ freq_subday_type = @ FSTYPE, - Do you repeat @ freq_subday_interval = @ fsinterval, - Repeat cycle @factor, @ Active_Start_time = @ Time - 17:00:00 in the afternoon execution
Go
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- Knowledge point: Usage of backup / recovery statement, job creation
-------------------------------------------------- ----------------------- * // * - Application Case - Backup Solution: Complete Backup (once every Sunday) Difference Backup (Backup every day One) log backup (backup every 2 hours)
Call the above stored procedure to implement - * /
Declare @SQL VARCHAR (8000) - Full Backup (once every Sunday) Set @ SQL = 'exec p_backupdb @ dbname =' '' 'EXEC P_CREATEJOB @ JobName =' Weekly Backup ', @ SQL @ freqtype = 'week'
- Difference backup (backup every day) set @ SQL = 'exec p_backupdb @ dbname =' 'To back up the database name', @ bktype = 'df''Exec p_createjob @ JobName =' Daily backup ', @ SQL, @ freqtype = 'day' - log backup (backup every 2 hours) set @ SQL = 'exec p_backupdb @ dbname =' 'To back up the database name', @ bktype = 'log''Exec p_createjob @ jobname =' Every 2 hours of log backup ', @ SQL, @freqtype =' day ', @ fsinterval = 2 - there is still a function
/ * - Get the file directory @dbname of the database specifies that the database name to obtain, if the specified data does not exist, return the default data directory set when installing SQL If specified NULL, return the default SQL backup directory name - Zou Jian 2003.10 - * /
/ * - Call the sample select database file directory = dbo.f_getdbpath ('Tempdb'), [Default SQL Server Data Directory] = DBO.F_GETDBPATH (''), [Default SQL Server Backup Directory] = 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 ") 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 = r r (Reverse (filename)) from master..sysdatabasees where name = @ dbname
IF @dbname is null set @ RE = Reverse (Substring (@ RE) 5, 260)) 'Backup' else set @ RE = Reverse (@ RE, Charindex ('/' , @ RE), 260) RETURN (@RE) EndGo
- Total a function, three stored procedures
-------------------------------------------------- ------------------------------- / * - Get the file directory of the database: f_getdbpath / * - Call the sample Select Database File Directory = DBO.F_GETDBPATH ('Tempdb'), [Default SQL Server Data Directory] = DBO.F_GETDBPATH (''), [Default SQL Server Backup Directory] = dbo.f_getdbpath (null) - * / ------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----- / * - Backup Database: p_backupdb - / * - Call Example - Backup Current Database EXEC P_BACKUPDB @ Bkpath = 'C: /', @ bkfname = 'db_ / date / _db.bak'
- Difference backup Current Database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _df.bak', @ bktype = 'DF'
- Backup Current Database Log EXEC P_BACKUPDB @ Bkpath = 'C: /', @ bkfname = 'db_ / date / _log.bak', @ bktype = 'log'
- * // * - Parameter Description @dbname sysname = '', - To back up the database name, not specified, back up the current database @BKPATH NVARCHAR (260) = '', - Backup file storage directory, no Specifies to use SQL default backup directory @BKFNAME NVARCHAR (260) = ', - Backup file name, file name can be used in / dbname / represents Database name, / Date / Representative Date, / Time / Representative Time @Bktype Nvarchar (10) = 'db', - Backup Type: 'DB' Backup Database, 'DF' Difference Backup, 'Log' Log Backup @Appendfile Bit = 1 - Additional / Overlay Backup File - * / -------- -------------------------------------------------- -------------------------
-------------------------------------------------- ------------------------------ / * - Restore Database: p_restoredb - / * - Call Example - Complete Recovery Database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'DB'
- Difference backup recovery EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'Exec p_backupdb @ bkfile =' c: /db_20031015_df.bak ', @ dbname =' DB ', @ rettype =' df '- log backup recovery EXEC P_RESTOREDB @ bkfile =' c: /db_20031015_db.bak ', @ retype =' dbnor'Exec p_backupdb @ bkfile = 'C: / DB_20031015_log.bak ', @ dbname =' db ', @ rettype =' log '
- * /
/ * - Parameter Description @BKFile Nvarchar (1000), - Define Backup File Name @dbname Sysname = ', - Define Restore Database Name, Default For Backup File Name @dbpath NVARCHAR (260) = '', - Restored data inventory places, not specified, the default data directory for SQL @retype nvarchar (10) = 'DB', - Recovery type: 'DB' Financial Recovery Database, 'dbnor' is different Recovery, log resumption, complete recovery, 'DF' Difference backup recovery, 'log' log recovery @FileNumber int = 1, - Restore file number @overexist bit = 1, - Whether overwriting the existing database, only @ Retype is @killuser bit = 1 - Do you turn off the user's use process, only @ Overexist = 1 is effective - * / ------------------------ -------------------------------------------------- -----
-------------------------------------------------- ------------------------------- / * - Create job: p_createjob
- / * - Call example - Monthly Job EXEC P_CREATEJOB @ JobName = 'mm', @ SQL = 'SELECT * from syscolumns', @freqtype = 'MONTH'
- Weekly Job EXEC P_CREATEJOB @ JobName = 'WW', @ SQL = 'SELECT * from syscolumns', @freqtype = 'Week'
- Daily execution EXEC P_CREATEJOB @ JobName = 'a', @ SQL = 'SELECT * from Syscolumns'
- Daily execution work, repeated jobs per day exec p_createjob @ JobName = 'b', @ SQL = 'SELECT * from syscolumns', @ fsinterval = 4 - * /
/ * - Parameter Description: @JobName Varchar (100), - Job Name @SQL VARCHAR (8000), - The command @dbname sysname = '', - default is the current database name @freqType varchar 6) = 'day', - Time Period, Month, Week Week, Day Japan @fsinterval INT = 1, - Relative Time for Repeated Dressing Time Work, will range from 0:00 to 23:59 * / - / * - Application Case 2 Production Data Core Library: Produce backup schemes are as follows: 1. Set three jobs, separately back up the Product library, weekly Backup, monthly backup 2. New three new libraries, named: daily backup, a weekly backup, monthly backup 3. Establish three jobs, restore three backup libraries to the above three new libraries. OBJECTIVE: When the user has any data loss in the Produce library, the corresponding TABLE data can be imported from the three backup libraries above.
- * / declare @sql varchar (8000) - 1. Establish a monthly backup and generated moon backup database, per month, 16:40 pm every 1 day: set @ SQL = 'declare @path nvarchar (260 ), @ fname nvarchar (100) set @fname = '' product _ '' convert (varchar (10), getdate (), 112) '' _ m.bak''Set @ Path = dbo.f_getdbpath (null) @ fname - Backup EXEC P_BACKUPDB @dbname = '' product ', @ bkfname = @ fname - Generate a monthly new library EXEC P_RESTOREDB @ bkfile = @ Path, @ dbname =' Product_ Month, @ DBNAME = 'ProductE_ Month For week database recovery, prepare basic database EXEC P_RESTOREDB @ bkfile = @ path, @ dbname = '' product_ week ', @ rettype =' 'dbnor' '- to restore the basic database for Japanese database EXEC P_RESTOREDB @ bkfile = @ Path , @ dbname = '' product_ 日 '', @ retype = '' dbnor '' 'exec p_createjob @ JobName =' Monthly Backup ', @ SQL, @freqtype =' MONTH ', @ Time = 164000 --2. Establish a weekly differential backup and build a weekly backup database, at 17:00 pm on Sunday: set @ SQL = 'declare @path nvarchar (260), @fname nvarchar (100) set @fname =' 'product_' ' Convert (VARCHAR (10), getdate (), 112) ' '_ w.bak'''Set @ path = dbo.f_getdbpath (null) @ fname-- Difference backup EXEC P_BACKUPDB @dbname =' product ' , @ bkfname = @ fname, @ bktype = '' df '' - Difference recovery week database EXEC P_BACKUPDB @ bkfile = @ Path, @ dbname = '' product_ week '', @ retype = '' DF '' EXEC P_createjob @ JobName = 'Difference backup', @ SQL, @freqtype = 'w EEK ', @ Time = 170000--3. Establish a job of daily log backup and generation day backup database, do 17:15 pm on Sunday: set @ SQL =' declare @path nvarchar (260), @ fname nvarchar (100) Set @ fname = ''
Product_ '' Convert (VARCHAR (10), Getdate (), 112) '' _ L.bak'''Set @ Path = dbo.f_getdbpath (null) @ fname - Log Backup EXEC P_BACKUPDB @dbname = 'ProductE ', @ bkfname = @ fname, @ bktype =' 'log' '- log recovery day database EXEC P_BACKUPDB @ bkfile = @ path, @ dbname =' 'product_ 日' ', @ RetyPE =' 'log' ' 'exec p_createjob @ JobName =' weekly differential backup ', @ SQL, @freqtype =' day ', @ Time = 171500 - first execution: declare @dbname sysnameset @ dbname =' dbname '- Modify to the database to be recovered name declare @spid varchar (20) declare #spid cursor for 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 - Perform your recovery restore log dbname from disk = 'filename'With Stopat =' Date_time 'I also turn a post:
/ *************** http://www.9cbs.net/develop/Article/21/21304.SHTM title Using SQLServer's extended stored procedure to implement remote backup and restore happy_david (original ) Keyword expansion stored procedures, remote, backup, recovery
The title is very long, but the content is simple. . This is the first study of the younger brother, it is not good, I hope everyone Haihan.
I recently made data management modules for the company's framework program (with data-oriented applications). The demand for this module is relatively simple: backup, recovery, and cleanup logs. Our company's software is basically architecture with C / S, so two major functions in the data management module 'backup and recovery' may also be stored in the client, backup and recovery 'files in Client. End, thus this data management module must be able to implement remote backup and recovery databases.
The premise of the article is explained, so I should talk about how to implement it. In fact, it is very simple. I want to write a test instance for remote backup to everyone, I can clearly describe it! Example: Environment: Win2k SQLServer 2K Query Analyzer SQLServer Service Instance Name: MainServer Requires Backup Database Name: MSDB Local Machine Name (Client End): David Local User: ZF Password: 123 Local Name: Domain provides backup requirements Folder: E: / TEST first step: Establish a shared folder in program code (or CMD window) NET Share test = E: / test or brief description with NetShareAdd this: NET Share: It is a network command inside Windows . Role: Establish a local shared resource to display the shared resource information of the current computer. Syntax: See NET Share /? Step 2: Establish a Shared Credit Relationship Master "NET Use // David / Test 123 / User: Domain / ZF 'Brief Description: 1: XP_cmdshell: is the extended stored procedure for SQL Server. Role, execute a given command string in the manner of the operating system command line interpreter, and returns any output in a text line mode. Syntax: See SQL Server Online Help 2: Net Use: It is the network command inside Windows. Role, connect your computer with a shared resource or disconnect, or display information about your computer connection. This command also controls a persistent network connection. Syntax: See NET USE /?
Step 3: Backup Database Backup Database MSDB to Disk = '// David / Test / MSDB.BAK' does not need to explain, syntax see SQLServer online help
Step 4: Delete the Shared Folder Call (or CMD Window) NET Share Test / Delete or Use NetSharedl This API Result: Procedure 1376, these pages belong to the database 'msdb' file 'msdbdata' (in the file 1 on 1). Process 1 page, these pages belong to the file 'msdblog' of the database 'msdb' (on the file 1). The Backup Database operation has successfully handled 1377 pages and spent 3.653 seconds (3.086 MB / sec).
In this way, the MSDB on the mainserver server is backed up to the E: /TEST / TSDB.BAK file of the David machine, is it easy to use? The restoration database operation is also the same, as long as the statement of the third step is changed to 'restore database msdb from disk =' // david / test / msdb.bak 'is OK. . Can you try it? ! (The simplest test tool query analyzer CMD window) Note: XP_cmdshell This extension stored procedure can only call the SA level, and it is one of SQL Server's security hazards. Many DBAs like to delete or disable them, so developers Be careful when using it. The example in the article is just a brief explanation of how to use the extended stored procedure to achieve remote backup and recovery, without security and other considerations, I hope the reader is perfected in the code.
/ ************************************************** * SQL off-site backup failed, and the end of the root is permission! ! ! Such as: SQLServer is prepared to File Server, where your SQLServer's start-up users must have enough permissions on File Server! ------------------ ^^^ ---- ---------------------------- 1, the new SQLUSER user permissions are new. 2, FileServer has sufficient permissions to a folder on a folder. 3, the SQLUser password of the two machines is the same (convenient) 4. Change SQL Server to SQLUSER Start (Management Tools -> Services -> MSSQL Item -> Properties -> Specify User and Password) 5, // 192.168. *. * / Folder /sharebak.bak is OK ------------------------------------- ---------------------- Simple is: If you are all Windows2ks your machine also use the same administrator password, and the SQL server starts with Administrator You can write when you write your device: // ip / .......
If the other party is 98, you have to share it! You can write when you write your device: // ip / .......
For the following error: Because the database is being used, the access to the database is not available. It is best to process as follows:
Use master - Please pay attention to build in master database Gocreate Proc Killspid (@dbname varchar (20)) as begin declare @SQL NVARCHAR (500), @ Temp var set @ SQL = 'DECLARE GETSPID CURSOR for select spid from sysprocesses where dbid = db_id ( '' ' @ dbname ' '') 'exec (@sql) open getspid fetch next from getspid into @spid while @@ fetch_status = 0begin set @ temp =' kill ' rtrim (@SPID) EXEC (@temp) Fetch next from getSpid Into @SPID End Close getSpid deallocate getSpid End - Usage Use master - Please note that you run the GoExec Killspid 'Database name under the Master database
