But it will walk you through all the steps, so it's more suitable for beginners. Tip: The procedure of creating a backup task with Maintenance Plan Wizard is largely the same. You can also right-click it and choose Start Job at Step to test it immediately. It will be executed automatically according to your schedule. After saving the plan, you can find it under SQL Server Agent > Jobs. Double-click the task to choose the backup type, and the databases to backup. Hit Toolbox and drag the Back Up Database Task into the blank area on the right side.ĥ. Keep the schedule type as the default Recurring, and configure the frequency as you like. Click Subplan Schedule (calendar icon) to set up the backup schedule. Right-click it and choose New Maintenance Plan. Navigate to Management > Maintenance Plans. Make sure the SQL Server Agent is enabled, otherwise please Start it first.Ģ. Here I will mainly introduce the first one.ġ. This feature gives you options to create a plan manually, or complete the operation following a wizard. You can use it flexibly as you need.īACKUP DATABASE TO DISK = 'filepath\fileaname.bak' WITH COMPRESSION, INIT, STATS = 5 īACKUP DATABASE TO DISK = 'filepath\filename.bak' WITH DIFFERENTIALīACKUP LOG TO DISK ='filepath\n ' WITH NORECOVERY
The basic T-SQL backup statements is BACKUP DATABASE TO DISK.
Tip: After backup, you can also restore SQL database from bak file via SSMS. Then you can find the backup file in the target folder. It will prompt you a message once the operation is complete. Then, e nter the file name as you like and remember to add the. It's recommended specify a easily accessible path so that the backup file can be easily found when you need to perform recovery. Choose the backup type (full/differential/transaction log), and Add a path as you need. Right-click the database you want to back up in Object Explorer, choose Tasks > Back Up.Ģ. Launch SSMS and connect to your instance. In Microsoft SQL Server Management Studio, the easiest way to backup database is using the backup GUI.ġ.
You can pick the way you like, or use another SQL backup software that can schedule backup multiple databases simply and intuitively. Also, it is not applicable to SQL Express editions either. ◉ SQL Server Agent : It can also automate backups with high flexibility, but you need to fill in the backup script yourself as the core step. However, it is only available for non-Express editions and is relatively cumbersome to operate. ◉ Maintenance Plans : It allows you to backup multiple databases at once, and add a schedule to automate the task. But with effective scripts, you can perform some more advanced operations easily. ◉ T-SQL script: You need to know basic T-SQL commands beforehand, and any small mistake may lead to operation failure. ◉ Backup GUI: This feature is easy to operate without using scripts, but only one database can be backed up at a time, and the operation cannot be automated. So before you start, I’d like to briefly introduce and compare them for your reference: Backup database in SQL Server Management StudioĪs you probably know, there are multiple backup methods in Microsoft SSMS, each with its own advantages and disadvantages.
0 kommentar(er)
