Geeks With Blogs
Jayd Page

Steps Involved:

1) Create a Database Backup Script.

2) Create a Scheduled Task To Run the Backup Script.

1 Create a Database Backup Script.

a) Download and install SQL Server Management Studio. This is a free tool available on the Microsoft website.

b) Once Management Studio is installed launch it and connect to the SQL server instance that contains the database that you want to back up.

c) Right click on the database and then in the menu choose Tasks -> Back up...

d) This will open up a window where you can choose your backup options, once you are happy with the options click on the "Script" button near the top and select the "Script Action to File" option.

e) Save the File.

2 Create a Schedule Task to Run the Backup Script

a) Open up Windows Task Scheduler.

b) Create a new Task using the wizard, when asked to select a program browse to C:\Program Files\Microsoft SQL Server\100\Tools\binn\SQLCMD.exe

c) There are 2 arguments that need to be set: -S \SERVER_INSTANCE_NAME  -i "PATH_OF_SQLBACKUP_SCRIPT"
where SERVER_INSTANCE_NAME  is the name of the instance of SQL server that contains your database e.g. (local) and PATH_OF_SQLBACKUP_SCRIPT is the path of your backup script e.g. "C:\Program Files\Microsoft SQL Server\DatastoreBackup.sql"

d) Adjust the task to run at the desired times and you are done.

Posted on Saturday, February 19, 2011 8:30 AM | Back to top


Comments on this post: Automating the Backup of a SQL Server 2008 Express Database

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Dear Fellow,
i did not get where we have to set the 2 arguments u mentioned. kindly can u elaborate bit more. im doing this on sql2008 standard. i dnt wana use maintenance plan.

i tried making a batch file as

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S
\(local) -i "C:\Documents and Settings\myuser\Desktop\northwind.sql"

when i run this batch file from the task scheduler, only a cmd promt appears for a second and thts it, nothing happens.. no backup created..

please help me out what needs to be done here.. i will wait for ur reply on urownzee@yahoo.com
Left by Haider Mir on Dec 13, 2011 12:21 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Hi, Please note that it is not necessary to make a batch file. In the task that you create you can create an action to start a program.

Set the program to the location of SQLCMD.EXE and there is a section where you can add in arguments to the action.

Left by Jayd on Dec 13, 2011 5:59 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
how is step 1 automated?

from what I understand, SSMS express 2008 does not have the SQL Server Agent which is what is needed to schedule automatic backups in SSMS (SQL Server Mgmt Studio) ... only the non-express version has the Server Agent ...

if you know of a way to auto backup the SQL Server express DB, please let us know ... ;-x
Left by jk on Aug 21, 2012 8:56 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Step 1 creates the backup script which when run will back up the database.
Step 2 creates a schedules task using windows task scheduler to run the backup script created in Step 1. This will work SQL Express.
Left by Jayd on Aug 21, 2012 9:36 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
When you put the two arguments do you put a space between them? I only have one place to put arguments.
Left by Susan E. Russel on Mar 05, 2013 12:54 PM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Yes, you should have a space between the arguments for example. -S argument1 -i argument2
Left by Jayd on Mar 05, 2013 8:18 PM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
First of all, thank you so much for your help.

I am using the arguments listed below:

-S \SQLEXPRESSAO12 -i "C:\Program Files\Microsoft SQL Server\AO12.sql"

But the task scheduler gives me the following error:

The directory name is invalid 0x8007010B
Left by Susan E. Russel on Mar 06, 2013 7:38 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
So I got it to run, but it justs says running in the task scheduler and it never finishes. Sorry to bother you so much but this is very important.

Thanks
Left by Susan E. Russel on Mar 06, 2013 7:55 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
This is what is in the AO12.sql file:

BACKUP DATABASE [GHA-AO] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESSAO12\MSSQL\Backup\GHA-AO.bak' WITH NOFORMAT, NOINIT, NAME = N'GHA-AO-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Left by Susan E. Russel on Mar 06, 2013 8:07 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Hi Susan,

Please try and change the "NOINIT" in your AO12.sql file to be "INIT" instead. NOINIT means that it will append the backup to an existing file, for this to happen the file needs to be created with INIT first.

Regards
Left by Jayd on Mar 06, 2013 7:03 PM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Thanks again for all of your help. My last problem is that the task scheduler keeps running after the task is done. Do you have any ideas?
Left by Susan E. Russel on Mar 08, 2013 8:22 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Susan...press F5 on the list of tasks, it's probably done but the window doesn't update when the tasks complete
Left by anonymous on Mar 20, 2013 2:46 PM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
This worked perfectly for me, thanks so much for you help and good simple instructions Jayd
Left by Chris Webb on May 15, 2013 8:55 PM

# Just what I was looking for
Requesting Gravatar...
Thanks so much for this post and answering its comments.

Maybe this will help others, just for über-clarity:
A) Scheduled Tasks is located in the Control Panel
B) To input the additional parameters, you need to check the box at the last step of the "Add Scheduled Task" wizard. Or you can just edit the saved task by double-clicking and editing its properties.
C) To get the Server Instance Name, execute this SQL statement: select @@SERVERNAME or just look at the "Server name" when connecting to server upon initial login to MS SQL Server Management Studio. For example, "MYSERVER\SQLEXPRESS" would use SQLEXPRESS in the script.

Questions:
1) The generated script uses NOINIT. Is there anything wrong with using NOINIT instead of manually changing it to INIT?
2) Is there any way to generate a unique filename, possibly using SELECT CURRENT_TIMESTAMP or something like that? If yes, how can I only keep the 10 most recent instead of having an infinite number of backups accumulating?
3) What are your thoughts on compressing the backups?

Thanks SO much!
Left by Clifford on May 31, 2013 3:25 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Thanks Clifford,

1)NOINIT will mean that it appends to the file each time. INIT will create a new file each time (overwriting the existing one)
2)Im not sure about this
3)If you wanted the backup compressed you could create a bat file to do this and have it run after the backup task.
Left by Jayd on Jun 04, 2013 4:01 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
hi could you please tell me how to set password in sql server database backup
Left by Laxman on Aug 28, 2013 1:02 AM

# re: Automating the Backup of a SQL Server 2008 Express Database
Requesting Gravatar...
Wow, thanks, this worked like a treat for me!
Left by dessyboy on Jun 13, 2014 5:39 AM

Your comment:
 (will show your gravatar)
 


Copyright © JaydPage | Powered by: GeeksWithBlogs.net | Join free