SQL Express 2008 database full backups & trans logs

[denacho]

i've searched the forums here & found a few question/answers that speak a little to my question, but not completely. i perform basic SQL administration on several 2005 & 2008 SQL servers but i encountered my first vendor-supplied SQL Server Express 2008 server today. i need to set up full nightly backups & daily hourly transaction logs for the application databases on this server. after much reading, i realize that Express is a desktop version of the full-blown SQL (2005 or 2008) that i'm more accustomed to. so i also realize that i do not have access to Maintenance Plans or SQL Server Agent/Jobs that i would normally create my bak's & trn's from. i was able to find a post here that mentioned using sqlcmd<enter>backup database master to disk = 'c:\program files\backup\master.bak' to get a full backup - this did get me through an initial backup yesterday since the system has never had a backup run.

i also found a couple of articles on Microsoft's site (Library Article & Support Article) that cover how to get backups using TSQL & Windows Scheduled Task. The Microsoft Support article is probably more of what i'm looking to do but it's also more than i can understand at this point - i've never really done any scripting. is there anyone willing to take the time to help me figure out how i can create the Stored Procedure & the Batch File that will allow me to get a full nightly backup of Database1 & Database2 as well as hourly transaction logs from 5a-9p daily for those 2 databases? i don't need backups of any other databases such as master, etc. i just cannot understand all the scripting that is going on in the Support article in order to pull out or modify to my specific needs.

i would appreciate any help anyone could give me, but also understand this may be too large a request.

 

[djj55]

A suggestion of one way of many. On a SQL server that has a wizard for backup create the backup and then script it. Create a stored procedure on the express version based on the script and use Task Manager to fire it. As always permissions may be a problem.

I know this is terse but you seem to be headed in the correct direction. I have created a SysAdmin database to hold stored procedures like these.

Good Luck,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[denacho]

thanks dj55. not sure if i'm on the exact same track as you, but i did try going onto another SQL server in this vendor supplied system (there are 3 & the other 2 run SQL 2008 Standard). so i thought, i'll just connect to my Express server via SQL Standard from one of the other 2 servers & create a MaintPlan there just to test if i can get even a remote backup going. for whatever reason, i'm not able to connect from SQL Standard over to the SQL Express server. i did a Connect -> Database Engine & put in all the correct info for Server Name, Authentication, Login & Password but it gives me an error that basically says i need to check if Remote Connection is enabled. so back on the Express server, i check for that & it is definitely enabled, so i'm a bit befuddled. i then thought about taking a backup from the Standard server & scripting it out just to see if i can better understanding the scripting so perhaps i can use that as a guide for what i want to do on the Express server, but again, it's over my head. i did just find another Express server here that a co-worker manages & am going to look at what he did there to see if i can figure it out. thanks for the feedback though & a bit of comfort in that i do seem to be going down some sort of correct path even if i don't quite know what i'm looking for!

 

[denacho]

well anyone that can help me with the last step, i'd greatly appreciate it. i was able to use the Script To in Express to script out a backup titled sql_bu.sql:

BACKUP DATABASE [DBName] TO DISK = N'D:\Backup\DBName.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

that parses out correctly & i was able to manually kick off a backup successfully.

i'm now stuck trying to create a bat file that Windows Scheduled Task can use. here's what i guessed at but it's not working.

sqlcmd -S .EXPRESS -E -Q -i d:\sql_bu.sql

i created my SchedTask telling it to use my bat file. the Task kicks off just fine & looks like it works, but there is no new bak in the destination directory. can someone tell me what i'm missing? you can tell i've never done any programming/scripting before so i'm guessing at all of this & understanding very little of it.

 

[denacho]

well and now after a little more research & comparison, i don't find a sqlcmd.exe anywhere on my server. i even looked for osql & isql just grabbing ideas & find nothing. so i'm not sure exactly what exe would kick off the .sql file.

 

[denacho]

OMG! i think i figured it out! it involved Named Pipes (which i have heard of but don't understand) & credentials. here's what my final statement in my .bat file looks like

sqlcmd -S "np:\\.\pipe\MSSQL$NamedInstance\sql\query" -Uuser -Ppassword -ic:\mybackup.sql

i got this from the other Express server that i was using for comparison. i am not familiar with Named Pipes but had heard of it enough to to recognize np & \pipe. i first went back to what i'm familiar with in SQL 2005/2008 standard & tried running the sqlcmd with -U -P -S & -d & -i parameters. this gave me the error Named Pipes Provider: Could not open a connection to SQL Server....Check if instance name is correct and if SQL Server is configured to allow remote connections etc. so that's when i remembered that the GUI for my Database Engine connection had ServerName\InstanceName & the statement i borrowed from my other server mentioned Named Pipes. it took a little editing to get my command to run, but finally got to the above statement. i edited my .bat file to reflect the statement i'd run from the command prompt & ran my ScheduledTask & it ran just fine! now i just need to create a 2nd Task for the 2nd db that needs backing up or figure out how to back up both in one .sql statement.

hope this helps someone else!