Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/MatsHow about just executing the TSQL BACKUP DATABASE command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The problem is that due to
> connection pooling the file is constantly locked so I can't copy the file or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?|||Mats Lycken wrote:
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen|||Use instances are special. You can get the name of the pipe from the parent instance and then use
that name to connect from your app where you want to execute the backup command. This is explained
in
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE [C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skc
Lookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steen

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skcLookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steensql

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skcLookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steen