sql server database backup time to time automatic

Question

0.00/5 (No votes)

See more: (untagged)

hi to all,
this is Hem Raj Thakur, and I want to create a database backup automatically time to time.
How can I solve this problems. please help me. It's Urgent.

Thanks in advance.
Hem Raj Thakur

Posted 5-Dec-13 22:55pm

Hem Raj Thakur

Add a Solution

Comments

phil.o 6-Dec-13 5:06am

Which version of SQL Server? Express?
Please tag your question accordingly.

Hem Raj Thakur 6-Dec-13 5:24am

sqlserver 2005

3 solutions

Solution 3

If your SQL Server version is not an express one, as it seems, then you can use the Maintenance Plan feature of SQL Server Management Studio, that will allow you to configure and schedule your databases backups with a graphical UI.
Setting up a Maintenance Plan to Backup Databases[^]

If you have an express version, then you will not have access to SQL Server agent tasks scheduling, and will have to use the script provided in solution 2 ; this can be scheduled with the Windows Scheduler and osql Utility[^].

Posted 5-Dec-13 23:31pm

phil.o

Solution 1

you can create daily backup job, please refer below URL:

Create a database backup job using SQL Server Management Studio

Posted 5-Dec-13 23:03pm

Member 10434230

Updated 5-Dec-13 23:09pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ganesh Raja · Accepted Answer · 2013-12-05T23:10:00

1.Create the below scrip as procedure to take the backup of all the db which is available in mssql ,except system db's

SQL

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

2. Schedule this procedure in sql agent with required time.
or Schedule it in Task Scheduler if you are using Express Edition
For more see this
Automating the Backup of a SQL Server 2008 Express Database