Back up the SQL Server database

1 , creating a batch file

@echo off Set dates=%date:~0,4%%date:~5,2%%date:~8,2% Set rar= "%programfiles%\winrar\winrar.exe" Set rand=%random% Set folder=d:\dbback\%date% REM Creates a folder every day, named after today's date If not exist%folder% MD%folder% @echo ... You are backing up, do not close the program ... rem If backup sql200, change @osql to @isql @osql-usa-p123456-i D:\dbback2005.sql-o D:\sqlback.log After a REM backup, compressed into a zip file with a 4-bit random number after the file name %rar% a-afzip-ibck-df%folder%\ database name%rand% d:\ database name. bak

2 , establish Dbback2005.sql file

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31

DECLARE @dumpfile varchar (50) DECLARE @msg varchar (70) Select @dumpfile = ' d:\ database name. bak ' Select @msg =convert (char), GETDATE (), 9) + '-----cleaning up logs ... ' Print @msg Backup Tran database name with TRUNCATE_ONLY if (@ @ERROR <> 0) Begin Select @msg =convert (char), GETDATE (), 9) + '-----Cleanup log failed or an exception occurred ... ' Print @msg End Else Begin Select @msg =convert (char), GETDATE (), 9) + '-----Cleanup log complete ... ' Print @msg End Select @msg =convert (char), GETDATE (), 9) + '-----Start backing up the Northwind database ... ' Print @msg Backup database name to [email protected] With Init/*init overwrite the existing backup set, do not specify a large amount of data */ if (@ @ERROR <> 0) Begin Select @msg =convert (char), GETDATE (), 9) + '-----backup data failed or unexpected ' Print @msg End Else Begin Select @msg =convert (char), GETDATE (), 9) + '-----Database backup complete ' Print @msg END /* SELECT Mm=datename (yy,getdate ()) +datename (Mm,getdate ()) +datename (Dd,getdate ()) */

Second, backup SQL Server2008

Note: Modify the batch file, call Dbback2008.sql

1 , establish Dbback2008.sql file

1 2 3 4 5 6 7 8 9 10

DECLARE @name varchar (45) DECLARE @datetime Char (17) DECLARE @path varchar (255) DECLARE @bakfile varchar (290) Set @name = ' db_name ' Set @datetime =convert (char (one), GETDATE (), +) + REPLACE (CONVERT (char (8), GETDATE (), 108), ': ', ') Set @path = ' D:\ ' Set @[email protected]+ '/' [email protected]+ ' _ ' [email protected]+ '. BAK ' Backup Database @name to [e-mail protected] with [email protected] Go

Back up the SQL Server database