how to get the automatic backup of the database in the sql
server

Answers were Sorted based on User's Feedback



how to get the automatic backup of the database in the sql server..

Answer / suhail qaiser

This how to will show you to how to create a maintenance
plan that will automatically backup all MS SQL server 2000
databases at a specified time and will also automatically
add any new databases you create.

The original instructions were kindly supplied in pseudo
format by Paul Banks, I've just tidied them up a little

1 - Open Enterprise Manager on your SQL server machine
2 - Expand the console tree by selecting Microsoft SQL
servers > SQL Server Group > Your SQL Server > Management
3 - Right click Database Maintenance Plans and select "New
Maintenance Plan"
4 - When the wizard opens, click Next and select the "All
Databases" option. Click Next
5 - Skip the next two stages of the wizard by clicking Next
unless you want to rebuild indexes (Not recommended for a
large number of databases)
6 - Make sure the options "Back up the database as part of
a maintenance plan" is checked and also "Verify the
integrity of the backup when complete"
7 - Set a schedule up for the plan ti run, daily in the
very early hours is probably best and click Next
8 - Select the location for the backups, either the default
on somewhere on your server
9 - Check the option for "Create a sub-directory for each
database"
10 - Set the length of time that you wish to retain backups
11 - Leave the extension as BAK and click Next
12 - There's no real need to backup the transaction logs
unless you want to at the next stage so click Next
13 - Check the option to Write a report to a text file as
this is handy for diagnosing any problems with the plan and
set the retention time for the logs. Click Next.
14 - You can write the history to a DB table but this is
not necessary, click Next
15 - Check the summary, give the maintenance plan a name
and click Finish

At this stage you may get a warning about the SQL Server
Agent not running. If you do, click OK to clear the
message, right click SQL Server Agent and click Start.

NB: SQL Server Agent is set-up as a manual starting service
so you may want to go to Start > Run > services.msc, select
the SQLSERVERAGENT service and change the start up mode to
Automatic.

Now that you have created your maintenance plan, it's a
good idea to check it actually works so select Jobs below
SQL Server agent.

You should see the plan you have created so just right
click the job and select Start Job. The maintenance plan
should then run and finish with Succeeded under the Last
Run Status column.

Finally browse to the location you chose in step 8 and
check that you have a sub directory for each database and a
BAK file for the database backup.

And that's it! You are now backing up all your databases
and including new ones as they area created. You should now
include this folder as part opf your normal disk backup...

Is This Answer Correct ?    78 Yes 5 No

how to get the automatic backup of the database in the sql server..

Answer / guest

Through maintanence plan

Is This Answer Correct ?    44 Yes 9 No

how to get the automatic backup of the database in the sql server..

Answer / alex

You can try to use the following tool: SQLBackupAndFTP
(http://sqlbackupandftp.com) It's free when you backup 1 or
2 databases

Is This Answer Correct ?    7 Yes 4 No

how to get the automatic backup of the database in the sql server..

Answer / kumar.t

Open the sql server query analysiser

To Type The following query

Backup DataBase <DataBaseName>
To Disk ='Server path'

Is This Answer Correct ?    18 Yes 17 No

Post New Answer

More SQL Server Interview Questions

How you can get the list of largest tables in a database?

0 Answers  


What is the maximun sixes of .mdf in sql server 2005

1 Answers  


You have a table ‘test’ which is a copy of northwind employee table you have written a trigger to update the field ‘hiredate’ with the current date

0 Answers  


What happens when unicode strings concatenate with non-unicode strings?

0 Answers  


Can we store videos inside the sql server table?

0 Answers  






How to verify a user name with sqlcmd tool?

0 Answers  


what is the basic diffrence betn a col declared in char(1) and in varchar(1)

2 Answers  


How to write a query with a full outer join in ms sql server?

0 Answers  


How can I create a plain-text flat file from SQL Server as input to another application?

2 Answers  


What is the difference between locking and multi-versioning?

0 Answers  


How can I create a table from another table without copying any values from the old table?

3 Answers  


Tell me what is the difference between locking and multi-versioning?

0 Answers  


Categories