Backup a SQL database. What's it's name?

[ghall426]

My app in VB 2005 Express needs to backup the SQL database daily. Since I understand VB a lot more than I do SQL, I initially went about just copying the .mdf and .ldf files. Even though the connection is closed, I still get a "file in use" error.

So I guess I have to do it the proper way and use the "BACKUP DATABASE" command, however what is the name of my database? I've tried using "CallLog" and "CallLog.MDF" which are the filenames. I do not see any way of finding or changing the name of the database I created in SQL Express 2005.

And assuming I can get the name of the database, will it still backup correctly even though it's constantly "in use" as stated above?

Greg

[ghall426]

OK, I can't be the first person who's ever wanted to back up a database. Either my question:

1) Is too hard
2) Doesn't make sense

Basically I need to backup by SQL database (I think I have to use the BACKUP DATABASE command instead of copying the files) but it requires the name of the database which I cannot figure out, cannot find, nor have I ever named.

Greg

[techgnome]

When you connect to your database in your app.... what's the name you use in the connectionstring? It would be the same thing.

-tg

[szlamany]

I could have swore I posted here yesterday - must have closed the window without actually posting - oh well...

At any rate - what I said yesterday was:

Download the Management Studio Express tool from MS - it's free and at this link:

http://msdn.microsoft.com/vstudio/express/sql/download/

It will allow you to open the "server" and see the database name (although as TG said - it should be in your connection string - maybe called initial catalog?)...

At any rate - once you have the name of the DB you do a BACKUP command.

BACKUP DATABASE Funds 
   TO DISK = 'c:\BACKUP\Funds_copy.bak'

By the way - this is the only proper way to backup a database. It can be run hot - with users in the DB - it actually "marks" the log file so that any open transactions are held until the backup completes. By doing this if a restore is needed later any "uncommitted" transactions are automatically rolled back in the restored DB - which is a good thing.

You never want to copy the MDF/LDF. Using BACKUP marks the MDF/LDF so that RESTORES are possible. Using COPY is a file level operation that SQL knows nothing about.

[ghall426]

When you connect to your database in your app.... what's the name you use in the connectionstring? It would be the same thing.

My connection string is below and I use the AttachDbFilename command. This is the connection string that VB created for me.

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Greg\My Documents\Visual Studio 2005\Projects\CallLog\CallLog\CallLog.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

At any rate - once you have the name of the DB you do a BACKUP command.

BACKUP DATABASE Funds 
   TO DISK = 'c:\BACKUP\Funds_copy.bak'

Perfect! It works now that I know my database name is the entire path and filename!

BACKUP DATABASE [C:\DOCUMENTS AND SETTINGS\GREG\MY DOCUMENTS\VISUAL STUDIO 2005\PROJECTS\CALLLOG\CALLLOG\BIN\DEBUG\CALLLOG.MDF]
TO DISK = 'C:\BACKUP\BACKUP.MDF'

All of the examples I found online showed a simple database name (like 'Funds' in the example given to me above). Is there a way I can rename the database to a more meaningful name (like CallLog) rather than the entire path? Every place that shows the database 'Name' is grayed out as a read-only property.

I guess maybe I'm just being a perfectionist, but since all the examples I found show a name like this, it must be me still doing something wrong.

Thanks again.

Greg

[szlamany]

You are using SQL in a new way - just available with MS SQL 2005 (and maybe only on EXPRESS).

You are using it with ATTACHDBFILENAME - which means that when your application is not running the DB is not attached to the server instance.

We create our DB's on the server using the management tools. You can probably permanently attach the DB to the server instance through MANAGEMENT STUDIO. When this is done you basically have a "simple" logical name.

I'm not sure what you lose when you change from an ATTACH at runtime to a permanently attached DB - as I only use the latter.

[ghall426]

By the way - this is the only proper way to backup a database. It can be run hot - with users in the DB - it actually "marks" the log file so that any open transactions are held until the backup completes. By doing this if a restore is needed later any "uncommitted" transactions are automatically rolled back in the restored DB - which is a good thing.

You never want to copy the MDF/LDF. Using BACKUP marks the MDF/LDF so that RESTORES are possible. Using COPY is a file level operation that SQL knows nothing about.

Since a database can be backed up while it's open and used, from what I read (and have attempted) this is not the case when it is restored, correct? If so, my problem now is this: If I want to add 'Restore Database' as an option in my application, how can I do this because I'd need to be connected to the database to execute the RESTORE command from visual basic.

Even if I made a separate application to restore the database in the event of a failure or corruption, I'd run into the same problem. I don't want the users to have to use the management studio or anything like that. Most of the users have little computer knowledge anyway, and I want it to be easy in case I'm not around, no longer with the organization, or even dead some day!

I've read about possibly connecting to the master database but from what I read I shouldn't do it. I don't even really know what this is, so if this is what I need to do, please provide more information.

Thanks again.

Greg

[techgnome]

SQL Management Objects (SMO) - which replace the SQLDMO of the past - and Backing Up and Restoring Databases and Transaction Logs

-tg

[szlamany]

@tg - does that all work with SQL SERVER EXPRESS as well?

[techgnome]

szlamany - yup.... it's all the same engine..... just like SQLDMO could be used against SQL Server and MSDE, the SMO can be used against SQL Server 2005, SQL Express, and SQL Everywhere. SQL Compact.... not sure....

-tg