Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Express 2008 database full backups & trans logs

Status
Not open for further replies.

denacho

Technical User
Sep 6, 2002
116
US
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.
 
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!
 
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!
 
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.
 
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.
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top