Basic problem

I downloaded SQLExpress and Visual Studio Express to my home computer.

I built a simple database, adding data through theSQLexpress admin tool.

I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder.

I can still see and work in the database through SQL server Express.

The web page and the MSStudio attempt to connect to the mdf file both fail with this message:

Cannot open user default database. Login failed.
Login failed for user 'KAAAK/Administrator'.

So it seems to be trying to connect as the Windows user.

When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user.

from web.config:

<

connectionStrings>

<

addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345"providerName="System.Data.SqlClient"/>

</

connectionStrings>

That was changed from the original string created automatically by MS Studio

<

connectionStrings>

<

addname="stocksConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\stocks.mdf;Integrated Security=True;User Instance=True;"providerName="System.Data.SqlClient"/>

</

connectionStrings>I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.?

Thanks, Michael

Hi!,

This may happen because the SQL server is configure for windows authentication only. Change to mixed mode.

Hope this will help.

Regards

Bad un-install of SQL Server 2005 Tools Express Edition CTP

I have used the tool at this URL

http://msdn.microsoft.com/vstudio/express/support/uninstall/#Tool

and *cannot* get SQL Server 2005 Tools Express Edition CTP to uninstall. I get the following dialog box:

"The setup has encountered an unexpected error in datastore. The action is Write_CommitFlag. The error is:Unable to write property into cache flagCommit Unable to write property into cache: "flagCommit" XmlRW Error: Failure loading xmlrw.dll CheckAllProcedures() returned 2."

Any ideas as to how I can work around this ?

Just dug this out of forums, and I believe it fixed my issues. Reposting for everyone's benefit.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130776&SiteID=1

|||

1.Run 'SQL Server 2005 Tools Express Edition' setup with -x from command prompt this will extract all setup files into a folder.

2. search a help file in that folder and go to ‘command line ‘ help topic, you can run that command(s) from command prompt.

sql

Backups from SQLcmd ?

I am working with SQL Express and found myself surprised when I tried to automate a backup... No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem. I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT, NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

What editor did you use to save the SQL file? Sounds like you generated a unicode file without BOM (byte order mark) so it is treated as ANSI file. Check the save options of your editor. Notepad for example has a combo box in its save dialog where you should choose ANSI. Unicode will save 2 or more byte per character. For standard letters it is often the ANSI code followed by a 0 byte, which is a terminator for ASCII c strings - so I guess sqlcmd stops after hitting the first 0 byte which follows your first letter.

--
SvenC

|||

Yep - That did it. Somehow my other test using the select statement must have been saved in ANSI - my backup file was saved in unicode

Thanks a million !

backups and restore DB in SQL EXPRESS 2005

can anyone explain me how can i make a DB backup in sql express 2005 ?
The sql express instance and my web aplication are running on the same server.
I need a script for directly execute it (and if possible also to restore the DB ) from my web aplication.

other question:
any idea how can i tell to sql express to do the backup automatically every day?

To backup SQL Server you either use a stored proc to do it or you use one of the best part of SQL Server called the backup and restore wizard to backup the database. To backup daily you need to schedule the backup with SQL Server Agent Job. To do all of the above you need the new Enterprise Manager called Management Studio but Express did not come with it so donwload the eval version from the link below it is good for 180 days install it as a named instance. Then right click at the top of the Management Studio and register the Express so you can use the Management Studio to do all of the above. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=6931FA7F-C094-49A2-A050-2D07993566EC&displaylang=en

Backups

Hi,

I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?

Any suggestions?

Thanks,

Dale

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

Thanks for that. In answer to your question, the easiest way I know to backup the site (not just the database) is to copy the entire site using FTP, included in the site is the database file database.mdf under App_Data which is auto-mounted.

Given that ASP.NET 2.0 comes with this database xcopy feature and the App_Data directory it seemed such a nice solution that I didn't consider the backup implications until now. Now I am wondering if anyone has used the App_Data directory for a live site and if so how they manage backups.

Thanks,

Caddre:

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Thanks for the comprehensive reply - I do agree with you although in this case its nothing as important as transaction data. BTW - using the App_Data directory you still get a mdf & ldf file, Its a normal SQL Server 2005 Express database, its just that its physically located in the website and its automatically mounted when access is required. In all other respects its the same.

I was assuming (probably a bad move) that because Microsoft has developed this architecture it was therefore a *good* way to do things. So far it has proved to be *good* but now maybe with the exception of taking a backup. Therefore I was also assuming (another bad move) that there was a way to backup such an architecture.

Caddre:

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.|||

Thanks again, so just to sumamrise you're recommending that I convert my 'user instance' database into a 'real' database? Can you clarify, are you also suggesting that I move the physical location of the database from the website to the SQL data directory?

You've been very helpful, I appreciate it.

Caddre:

Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.

|||

No I am not recommending you change the location just receate it through restore or reattach it. User instance is a database that is independent of the SQL Server engine with known issues that is covered by the SQL Server team in the blog entry below. Your database located in the App_Data directory is a User instance. What I am suggesting is Backup the User instance and Restore at the same folder location but this time it is not a user instance because when you choose the Restore location SQL Server engine maps it as the physical location of the database. Microsoft have also provided a guide to use Attach to change the location you are use the method that suits you purpose.

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||Thank you! Much appreciated!|||

CoolKiwiBloke:

Thank you! Much appreciated!

I am glad I could help.

Backup without tabledata

Hello.

I am using SQL express and we have developed our app. til now.

Now we have to move the database to the another server and go live. It is fine just to backup the database but how to do it without tabledata? We have many test posts there and when going live we would like to start from beginning.

Is there any way to do it or do we have to delete all data before backing up?

Hi

This is possible. Under MSSQL 2005 Management Studio select the Database > All Tasks > Generate Script and Walk thru the all options.

Doing this will generate the script which you can use on empty database to get the tables etc.

Thanks

Rubal Jain

|||Hi!

Isn't there any Export function in SQL Express or MSSQL 2005 Management Studio?

Backup up sql server express database

Hi, I want to back up my database. I found in the books online where it said
backups could be done by simply copying the .mdf and .ldf files. Its that
enough? I don't have to run sqlmaint to produce the .bak files? and then
copy those?
Is there going t be a new newsgroup for SQL Server 2005 Express?
Thanks,
Brian
hi Brian,
SuperBK wrote:
> Hi, I want to back up my database. I found in the books online where
> it said backups could be done by simply copying the .mdf and .ldf
> files. Its that enough? I don't have to run sqlmaint to produce the
> .bak files? and then copy those?
you can just simply copy your database and log files, but the database must
not be in use in order to allow that... so consider a "standard" stategy
where you perform Transact-SQL BACKUP DATABASE statements, that's to say the
"natural" backup option for SQL Server databases..
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
you can perform that via the native sqlcmd.exe or oSql.exe, or even your own
home made tool and/or application..

> Is there going t be a new newsgroup for SQL Server 2005 Express?
think there will be...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup to network drive using Workgroup/Standard.

I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive.
My question is:

Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.

My other question is :
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience.
Regards
Berly Sam

You cannot backup to a network share with any edition of SQL Server.

SQL Server Backup will only used locally attached drives, or NAS/SAN LUNs. You can either backup locally, and then move the file to a network share (both tasks can be automated with SQL Agent -not included with SQL Express), or Check out the several third party backup tools. (Idera, Red-Gate, to name a couple.)

Workgroup-Standard-Enterprise, which is 'best'. It depends upon the functionality that you require. With a database of that size, you are definitely between the Standard and Enterprise Editions (in my opinion). You may wish to check out the functionality differences at this site.

|||Thats not exactly true, you cannot see the backup media in the SQL Server dialog as it only displays local fixed drives, but you can specify a UNC share within the dialog textbox or within the script. Depending on the situation you should rather consider using a local /SAN / NAS backup first and then copy it to a network share if needed.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I consider it a gentle ‘challenge’ for me to give more consideration to why I responded as I did. And I appreciate that.

As I contemplate why I thought so, all I can remember is ever since 6.5, I have always taught to backup locally and xcopy to a net share. I can only imagine that was because of the speed issues of what used to be slower networks, as well as the persistence and permissions issues related to mapped drives.

And I now realize that I have not reconsidered or changed that practice in the intervening years.

I’m glad that you challenged me, now I have tested and affirmed that a backup to a UNC works just fine. But I doubt that I will change my practice of Backing up locally and using xcopy to move the backup file to a network resource -the BACKUP won't fail because of any network issues.

Regards,

- Arnie Rowland