Restoring MSSQL Databases in TSQL

Since the tool doesn't allow us to restore databases with multiple filegroups, can we use TSQL to restore our databases?

 

You have full access to/from data pumps on your local end to your hosted SQL.
You can also drop code into the on-line tool.
https://sqlwebadmin.discountasp.net/

If you need detailed help re-post. ;-)

 

Not Quite What I Had In Mind

I was gunning toward writing my own backup script in Management Studio. I have several scripts in my toolbox. Imagine my disappointment/disgust followed by some empathy when I was greeted with the following message:
Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database [mydatabase].
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Is there any chance us advanced users can write our own scripts for backup restore? I mean I am a DBA too so I understand the benefit of the tool for non-DBA types but I was able to figure where the backups were, the data and log files were so I know the pattern of how you guys place things. And don't forget we have powershell in our toolbox now too so we shouldn't be locked in to just using the web tool

 

There are some advanced options for you.
Search the forums for posts by CrystalCMS (Joe)
http://community.discountasp.net/showthread.php?t=7935
He has done some DASP API code for SQL Server.

btw, as a DBA you should be aware of the need for some lockdown on Shared Hosting with the Web version of SQL Server. You do right? ;-)

 

Aware yes - But Still A Little Peeved.

Yeah, I'm aware of the lockdown necessity - hence the empathy but if we can backup via a tool, why can't we backup via T-SQL?
Any issue we run into via T-SQL, we can run into via the tool as well.
The only potential issue might be the path used when backing up but since the SQL server users backup in the context of the server service account, we could only go where the service can go. This could be solved by giving us fixed devices or with a server trigger in 2008 by the way. And don't forget powershell either. We can do some really nifty admin stuff with it so having a tool written in powershell that accomodates multifile/multifilegroup databases could overcome the limitations of the current tool. I volunteer for that - for a fee of course......

As a "precaution" I tried restoring via T-SQL and got:
Msg 3110, Level 14, State 1, Line 1
User does not have permission to RESTORE database 'SQL2008_596572_conejondata'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This in only an issue for those of us who don't use the Primary filegroup as our default filegroup and actually use multiple filegroups (partitioning being one of the biggest driving forces for this). The tool doesn't allow you to restore from a multifile/multifilegroup database backup. Cramming objects in the Primary filegroup is discouraged so most of the DBAs with some years under them actually set an alternate filegroup as the default and leave primary alone.

Sooooooooo how about some permissions to backup and restore via T-SQL? Pretty Please?

 

...I used to write Triggers for SIEMENS AG, miss doing that sort of thing with them. ;-)

 

I know. The point here isn't to gripe but to point out something that is lacking and offer to help create a solution. We've got 3 options:
1.) Grant Backup Permissions (Least likely because of perceived security risk of backing up another database. Thing is you have to have permissions on the database to back it up so no risk there).
2.) Make a powershell based tool to handle backups and restores with multiple filegroups. This way you can hard code the tool to place the backups where you want and have it connect as a user that public facing users are not aware of - preserving security. Open this task up to enterprising developers like myself. We'd do that almost for free.
3.) Upgrade the current tool (2nd least likely and probably makes the good folks at DiscountASPNet reeeeeeeeaaaallll nervous).

 

...While you wait for a response from the DASP crew I just want to mention that I'm a Martial Arts vet with a huge interest (fan) in Parkour. ;-)

 

Cool. If I wasn't so busy coding, I'd hit the Martial Arts/Parkour combo. The little i do keeps me young but I just hate the time limitations. Like coding, a lot of it is a little bit over time to get good. Just gotta pay dues....