Author: chao_ping Date: 01-10-14 18:13
OFF-topic: SQL * Server Backup Policy
Please ask me to help, I don't understand SQL Server. How to make a backup policy that is more complete SQL Server7. Will be a disk image. But SQL Server itself is definitely still needed. Whether the brother has a ripe backup strategy: script / document Share's younger brother! Thank you. Chao_ping@163.com
Author: yanwang
Time: 01-10-15 02:10
If you just want to backup once, follow the precedure below.1. Open SQL Server Enterprise Manager.2. Click Microsoft SQL servers3. Click on the left side of SQL Server group4. Click on the left side of the SQL server that you are interesting5 Click on the left side of Databases folder6 Find the database that you want backup, right mouse click on the database.7 Highlight All Tasks -...> Backup databases ... 8. Mark radio button Database-complete for full backup9. Under destination, click add, then select the directory that the backup file will go, or select the tape drive by click Backup Device radio button. Then click OK. Then OK again. The backup will start.If you want to schedule backup, you need to create a Database Maintenance Plan. The steps 1-4 are the same. 5. Click on the left side of Management folder.6. Right mouse click on Database Maintenance Plans, then click New Maintenance plan .. .7. Click Next.8. Select Databases That You Want To Backup, I Always Select All Database ES.9. Click Next.10. Select Update Statistics Used by Query Optimizer, or Nothing. I DON '
t select the other two selection, click next.11. select Check database integrity. click next.12. Mark Backup the database as part of the maintenance plan. Select tape if you want to backup to tape, or disk. Under the schedule, click change, then you could select the time you want, then click OK. click next13. Mark Create a subdirectory for each database. Select the time you want to Remove files older than ?? weeks. Click next.14. Mark Backup the transanction log as part of the maintenance plan. Then select tape or disk. same as before. you can change the time. For production, backup the transanction log every two hours is a normal setting. For heavily used system, the time could be 30 min . Click next.15. Same as before, mark Create a subdirectory for each database. Select the time you want to Remove files older than ?? weeks. Click next.16. Select write report to file or send an e-mail to operators I DID NOT USE Mail. Click Next The Record, You May Use The Default Setting , Click next.18. Then review the setting, if everything is OK, click finish, you are done. Then check the plan is executed or not next day.For cold backup, one need to shutdown SQL Server, then use OS copy command .. to copy datafiles Another way is to use detach and attach.Please check books online You can find almost all the answers there Good luck author:.! snowhite2000
Time: 01-10-15 04:05
Like yanwang say, and do a one-time backup or create job or maintenance plan including backup plan for scheduling backup I do so:. 1.create two maintenance plans, one for system database (master, model and msdb) full disk backup at 11pm from Mon to one week's backup copies Sat. keep on the hard disk. SQL SERVER job engin will delete old more than 7 days backup automatically.another maintenance for user database. Full disk backup at 11:30 pm Mon. to Sat. too . Log files, once an hour from 8am to 8pm from Mon. to Friday.2. OS admin will use some other tool backup whole hard disk to tape. so I will have two backup copies. I have all database datafiles and logfiles, I can use store procedure sp_attach_db or sp_attach_single_file_db, I also can use resotre database commande to restore database from backup database file. only thing I know is SQL SERVER you do not need to do COLD backup. this is a first person has done Oracle database Misunderstanding. As long as there is no user online, there is no Transaction, DBA does not require Shutdown SQL Server. The so-called Shutdown is just STOP MSSQLServer's Services. Author: yanwang
Time: 01-10-15 05:11
Thanks for Sharing Your Strategy! Your Backup Method Is Perfect.
Author: kezizi
Time: 01-10-15 07:17
ON NT, TO SHUTDOWN ORACLE IS Also to Stop Oracle Service.
Author: guo
Time: 01-10-15 09:11
Fortunately, I have been three years of SQLDBA, there are some scripting structures on the hand: the DBBKLIST meter is used to store data backup information such as the backup directory of each database, full backup and incremental backup time interval structure: DBNAME SYSNAME, - Database Name PaSTD Tinyint, - Since the last backup, the number of days fulld tinyint, - fully backed up time interval Diffd Tinyint, - Incremental backup time interval fdate varchar (10), - most recently Backup Time BPath Varchar (255) - Backup File Storage Directory below is the backup process: create proc backupdbasDeclare @errorno int @fdate varchar (10), @ bpath varchar (255), @ Today varchar (10) Declare DBList Cursor for select * from dbbklist - Top-by - one database for processing set @ Today = Convert (VARCHAR (10), getDate (), 20 ) Open dblistfetch next from dblist @ DBNAME, @ pasted, @fulld, @ Diffd, @fdate, @ bpathwhile @@ fetch_status = 0 beginif (@fdate = ') begin - need to initialize backup Task: Make backup, write the current date to fdate EXEC ('Backup Database' @ dbname 'to disk =' '' @ bpath @ dbname @ Today 'F.bak' '' with init, nounload, Name = '' ' @ DBNAME ' Full Backup ' ' '', Skip, Restart, Stats = 10, Noformat ') Update DBBKLIST SET FDATE = @ Today Where Current Of DBListPrint' Database ' @ DBNAME 'Completes the first database backup, this complete backup date is:' @ Todayend Else Begin - If it is not empty, it has been backed up. Compare @PASTD with @fulld, @ Diffd, @fulld begin - When @pastdate is not less than @ffulldate, complete backup EXEC (' Backup Database ' @ dbname ' to disk = '' @ bpath @ dbname @ Today 'F.bak' '' 'with init, nounload, name =' ' @ dbname ' in ' @ Today ' Full backup '
'' ', Skip, Restart, Stats = 10, NOFORMAT') - After the backup is complete, write the current date to FDATE, set @pastdate 1, start a new round of loop Update dbbklist set fdate = @ Today, pastd = 1 WHERE CURRENT OF DBLISTPRINT 'Database' @ dbname 'Start a new round of complete and difference backup. The current date is:' @ TodayEnd else begin - When @Pastdate is less than @ffulldate when IF (@pastd> = @ Diffd) and (@ pasted- @ Diffd * (@ pasted / @ DIFFD) = 0) Begin - When @PastDate is not less than @diffd, the difference backup EXEC ('Backup Database' @ dbname to disk = '' ' @ BPATH @ dbname @ fdate 'D.bak' '' 'with init, nounload, degreential, name =' '' @ dbname 'incremental backup of' @ Today '' increment. Last complete backup time: ' @ FDATE '', SKIP, RESTART, STATS = 10, NOFORMAT ') END - After the backup is complete, add @pastdate plus 1, continue loop Update dbbklist set pasted = @ pasted 1 where current of dblistprint' a day 'Endend - Handling the next record fetch next from dblist @ dbname, @ pASTD, @ fulld, @ Diffd, @ fdate, @ bPathendclose dblistdeallocate DBListGO specific Usage process is: Use Job to call stored procedure backupdb every day, check if needed by this process Backing up and how backup, DBList records backup configuration information for individual databases, you can use standard INSERT, UPDATE, DELETE to edit this process when you do SQLDBA, and then go to Oracle, no need, some places need to be improved. For example, clearance of old backup file is not made out of:. chao_ping
Time: 01-10-15 10:02
Thank you. I will try it with your way and have questions to continue. Thank you
