December 31, 2008 at 10:11 am
We are using MS SQL 2005 with Microsoft Server Management Express and I need a way to do a daily unattended backup of our database. I can do a manual database backup, but I would much prefer something that could upfdate the database at night or even real time several times a day, so that I would have a file that could be restored to another server in the event of a disaster or server crash. Is there a script that could be written do this? I have looked for a full version of Management studio but have not found one and the express version does not allow you to schedule backups.
Thanks,
Michael D. Winn
December 31, 2008 at 10:49 am
May I suggest reading this article:
http://www.sqldbatips.com/showarticle.asp?ID=27
In this series of articles, I'll demonstrate a couple of different approaches to writing a maintenance utility that mimics some of the behavior of the sqlmaint utility that is included with SQL Server 2000. SQL Server 2005 Express Edition does not include such a utility, so these articles will show how we can easily create one ourselves using either TSQL or SMO (SQL Management Objects - the successor to SQL-DMO). Rather than dive into the code, these articles will demonstrate how to use these utilities for backing up and maintaining your databases and how to schedule these tasks using the Scheduled Tasks facility in Windows XP and Windows 2003. In this article we will concentrate on the TSQL version of the utility which is in the form of a stored procedure - expressmaint. To download a command line version built using SMO go to Automating Database maintenance in SQL 2005 Express Edition Part II. To vew articles on performing maintenance operations using SMO including sample code see the Related Articles section at the bottom of the page
December 31, 2008 at 11:19 am
OK I will suggest you also this script and some actions to do, hope it will help you to find solution little bit soon!
First select you DB ID
SELECT DB_ID();
The results will show you the ID of your DB! So copy the ID number in the script below replace Your_DB_ID with you select ID and put the original location as you want for the DB Backup, in the script below the default location is in the same place where is your script then save it DBbck.sql in C:\backupFolder
DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
--month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))
--day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))
--hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
--minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
--name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
--=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] = YOUR_DB_ID AND NAME NOT IN ('TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\backupFolder\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'
EXEC (@SQL)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] = YOUR_DB_ID AND database_id>@IDENT AND NAME NOT IN ('TEMPDB')
END
After that also in your C:\backupFolder create one txt file and write this code, just copy from here to your file:
sqlcmd -S InstanceName-E -i"C:\backupFolder\DBbck.sql"
InstanceName is usually you computer name during the installation as default instance!!!
and save it then rename it just the extension from txt to bat!
To test your db bck just double click on batch file with bat extension!
Your bck file will be in C:\backupFolder
After verifying you should go to the Control Panel and double click to Schedule Tasks and add the schedule task with wizard as you want!
:w00t:
April 28, 2009 at 4:07 am
Please read this blog post. It explains how to schedule daily backup of sql server database.
http://cherupally.blogspot.com/2009/04/schedule-daily-backup-for-sql-server_27.html
-Kiran
April 28, 2009 at 5:23 am
Kiran Cherupally (4/28/2009)
Please read this blog post. It explains how to schedule daily backup of sql server database.http://cherupally.blogspot.com/2009/04/schedule-daily-backup-for-sql-server_27.html
-Kiran
Yes, but you should know that we are discussing for SQL Express Edition, no for Developer or Enterprise Edition. In Express edition we don't have SQL Agent !!!
April 29, 2009 at 4:02 am
I've used a combination of these two products with great success to run backups and maintenance to schedule for Express edition:
ExpressMaint Utility: http://www.sqldbatips.com/showarticle.asp?ID=29
SQLScheduler: http://www.lazycoding.com/products.aspx
April 29, 2009 at 5:18 am
Phil C (4/29/2009)
ExpressMaint Utility: http://www.sqldbatips.com/showarticle.asp?ID=29
SQLScheduler: http://www.lazycoding.com/products.aspx%5B/quote%5D
Yes, in the Internet you can find many alternatives how to do that...so we are discussing here in SSC.com and there it is one of the solution ...anyway all other alternatives are welcomed! Importance is that the user have solutions and he\she can decide what is suitable!
:hehe::hehe::hehe:
April 29, 2009 at 8:41 am
I use this method:
http://www.mssqltips.com/tip.asp?tip=1174
It's very easy to use and set up.
lol
Tim White
April 29, 2009 at 8:43 am
and I added another job to remove old backup files......
Another scheduled task to delete old backups:
RUN: forfiles /p "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" /m "*.bak" /d -7 /c "CMD /c del @FILE"
START IN: C:\WINDOWS\system32
Tim White
November 25, 2010 at 12:47 pm
Phil C-257913 (4/29/2009)
ExpressMaint Utility: http://www.sqldbatips.com/showarticle.asp?ID=29
SQLScheduler: http://www.lazycoding.com/products.aspx%5B/quote%5D
After creating the stored procedure as per: http://www.sqldbatips.com/showarticle.asp?ID=29 I got this:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
Stored Procedure created successfully
However, I do not see it in the master db and when I try to run it from the master I get: Could not find stored procedure 'expressmaint'.
After reinstaling it within a user db it is visible and it runs fine
July 16, 2011 at 12:16 pm
I don't see the point of dealing with custom scripts to backup SQL Express when there are many tools available for that.
This tool http://sqlbackupandftp.com lets you create manual or scheduled backups. You can also save your backups in a remote location on your LAN or FTP sites, compression and encryption is also available.
Version 6.0 even lets you schedule differential and transaction log backups.
November 3, 2011 at 12:50 am
we've been using todo backup server for a long time. it offers full, incremental, and differential backup.
we usually set up a daily backup schedule, and save three versions of the images, which means that the software automatically delete the old images.
a specific file can also be restored in Windows explorer from a folder or partition backup image.
we are small business users, and cannot afford some backup software costing 1k dollar.
so we found todo backup suits us very well.
u can also try it.
November 17, 2011 at 11:03 pm
we've been scheduling our SQL backup following this article.
hope it can help a liittle.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply