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
/Mats
How 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:
> 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/expres...xpuserinst.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:
> 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.MD F] 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:
> 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 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 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 probl
em 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\C
MSDB.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:
> 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\C
MSDB.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 comm
and. This is explained
in
sqlexpuserinst.asp" target="_blank">http://msdn.microsoft.com/sql/expre...expuserinst.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:
> 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\C
MSDB.MDF' TO DI
SK = '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 & #91;C:\Inetpub\wwwroot\SlottsCMS\App_Dat
a\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:
> 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\C
MSDB.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?sql

Backing up SQL DB

We're trying to create a .bak file to send it to our site's admin to restore it on the

server:

1. When I try to do that get this error:
http://www.radiorient.net/errors/export_sql.gif

Why is that? and how can I fix it?

2. Should we add both files of the db (.MDF, and .LDF) or only the .MDF file?

Can please someone help me with this, Thanx.

Do not bakcup a datbase to its own data/log file, instead, you can backup to any other file that can be written by SQL Server. The purpose of backup is to gurantee the database can be recovered in case of database corruption (usually the physical files are corrupted), so bakcup a database to its data/log files doesn't make sense, right?

For more information about backup, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

|||

Thanx, we did it, we have some debugging to do now, but at least the DB is online, I hate H-Sphere, I worked with many control panels that is a lot easier.

Now when we upload image using the admin area, it doesn't upload the image on the server, we checked the permission of the image folder and it's 777, so what could be wrong?!

Backing up SP's and Functions...

Hello,

I want to write a batch file which gets the backup of the SP's,Functions,Views and Tables which belongs to me.Is there a utility to do this?

The best option may be using one of the excellent third party products. Or, try these scripts and see if you can convert one of them for your usage.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

I'd recommend one of the third party products, such as SQL Script from www.ApexSQL.com.

sql

Backing up online log after loss of primary data file

My question is this:

Is there anyway to backup the current online log after complete loss of the current correponding datafile?

Example:

(2) Logical Disk Volumes

Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf

Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.

Thanks,
PI can't beleive there is no way to get at the information in the current online log depite the fact that the database file is gone.

So, if I can't get at the current online log I must rely on a backup of the log which I would take every 5 or 15 minutes but that 0-5 minute period is not retrievable even if its just the data file that fails.

Any thoughts?

Backing up log files

Hi,
I'd like to know what happens to a log file when you back it up. I received
an error that my log file had grown too much & that I should back it up,
which I did & I no longer received the error.
But what happens when you simply back up a log file? Does it also truncate
unused space or what? It obviously doesn't simply make a backup of it.
Many thanks for helping me understand what is happening here
AntBACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
reason don't want to do transaction log backup then set the recovery model for the database to
"simple".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
> Hi,
> I'd like to know what happens to a log file when you back it up. I received
> an error that my log file had grown too much & that I should back it up,
> which I did & I no longer received the error.
> But what happens when you simply back up a log file? Does it also truncate
> unused space or what? It obviously doesn't simply make a backup of it.
> Many thanks for helping me understand what is happening here
> Ant|||Tibor
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file).
I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
virtual logs file that LOG file is built by and delete only inactive
(written to disk) transaction
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file). If you for some reason don't want to do transaction log backup then
> set the recovery model for the database to "simple".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>|||Hi Uri,
It all depends on what abstraction level we want to be. :-)
At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate", whichever
in English gets the message through). I prefer to say "empty", since I believe it makes it clearer
that the file isn't made smaller.
There are of course much more details what happens when you BACKUP LOG. I stay away from those
details when I see a basic question and I suspect the OP has limited experience with transaction log
internals - like virtual log files. The reasoning is that more details will only cloud the message.
:-)
As for the details, this is how I believe it works. Anyone is of course free to add and correct,
below if off the top of my head:
A VLF can be in four states:
A. Never ever used
B. Used, and all log records are prior than the last checkpoint and have also been written to disk.
C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
D. The active log (where the current log record, "head of the log", is).
BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever the
head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C and D
have status 2.
Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
tend to stick with the higher abstraction level for basic questions. :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
> LOG file is built by and delete only inactive (written to disk) transaction
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>|||Tibor
The ability to think on abstaction level given from God, I mean you canot
learn this. :-)
And you I think have it.
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file
Yep, that it was my understanding as well
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Tibor
The two middle states have nothing to do with Checkpoint. The have to do
with whether those transactions have been backed up, so the log space can be
reused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Kalen,
Thanks. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> The two middle states have nothing to do with Checkpoint. The have to do with whether those
> transactions have been backed up, so the log space can be reused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate",
>> whichever in English gets the message through). I prefer to say "empty", since I believe it makes
>> it clearer that the file isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG. I stay away from those
>> details when I see a basic question and I suspect the OP has limited experience with transaction
>> log internals - like virtual log files. The reasoning is that more details will only cloud the
>> message. :-)
>> As for the details, this is how I believe it works. Anyone is of course free to add and correct,
>> below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have also been written to
>> disk.
>> C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
>> virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever
>> the head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C
>> and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
>> tend to stick with the higher abstraction level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
>> LOG file is built by and delete only inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>|||You're welcome.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWx%23pC1YIHA.3880@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
> Thanks. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> The two middle states have nothing to do with Checkpoint. The have to do
>> with whether those transactions have been backed up, so the log space can
>> be reused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the
>> log" (or "truncate", whichever in English gets the message through). I
>> prefer to say "empty", since I believe it makes it clearer that the file
>> isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG.
>> I stay away from those details when I see a basic question and I suspect
>> the OP has limited experience with transaction log internals - like
>> virtual log files. The reasoning is that more details will only cloud
>> the message. :-)
>> As for the details, this is how I believe it works. Anyone is of course
>> free to add and correct, below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have
>> also been written to disk.
>> C. Used, but some log records are more recent than last checkpoint
>> and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just
>> allow for re-use of the virtual log file. Basically turning C above into
>> B. A and B can be re-used (overwritten whenever the head of the log
>> moves to this virtual log file). These have status 0 in DBCC LOGINFO,
>> while C and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or
>> whiteboard). Which is why I tend to stick with the higher abstraction
>> level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to
>> re-use virtual logs file that LOG file is built by and delete only
>> inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file). If you for some reason don't want to do transaction log
>> backup then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of
>> it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>>
>

Backing up log file with a script setup as a job

Is there any way to include in this script below something
to tell the job to backup will the log file when it gets
to a certain percentage say 85%.
Thanks,
Aboki.
/* Script for backing up the transaction log when it is
getting full and clear the space for the log file. */
/* This can also apply to any other system but you need to
change drive and part location */
/* to match where backup location */
Backup LOG IntegraProd TO
DISK = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\IntegraProdTran.bak'
GOYou could write a job to run every so often, which uses DBCC PERFLOG
to get information about log space and log space used. If log space for your
database is > 85% then issue your t-sql command.
The log is cyclic, it should wrap around and re-use empty space
where possible. It needs to keep information on the oldest active
transaction
for rollback. If your log is constantly at a certain size, this will most
likely be
right for the database anyway.
HTH
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GO|||You can set an alert to start the job. There should be an example alert you
can modify already installed with SQL.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GOsql

Backing up Encryption Master Key

Hello,
I have problems backing up the Master key. The message is cannot write into
file 'file path' verify that you have the write permissions. I don't
understand why because I am using user dbo and defaul schema dbo on the
database.
If someone can help me to clarify the permissions part.
Thanks for your help.Make sure the SQL Server service account has write access to the path where
you want to save the file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
> Hello,
> I have problems backing up the Master key. The message is cannot write
> into
> file 'file path' verify that you have the write permissions. I don't
> understand why because I am using user dbo and defaul schema dbo on the
> database.
> If someone can help me to clarify the permissions part.
> Thanks for your help.|||Well I am not login as sa. I am using windows authentication and the login
user I have is member of the Administrators group on the local computer wher
e
I am trying to save the file containing the backup of the master key.
Thanks for your help anyway.
"Laurentiu Cristofor [MSFT]" wrote:

> Make sure the SQL Server service account has write access to the path wher
e
> you want to save the file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:AF3C125B-D6D3-4304-87F2-C332DD562626@.microsoft.com...
>
>|||How you connect and the permissions you have don't matter in this case -
when you go outside the server, you go out with the service account
credentials, not with the credentials you connected to the server.
So, find out what account SQL Server is running as, and make sure that
account has access to the location where you want to save the backup file.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Car" <Car@.discussions.microsoft.com> wrote in message
news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...[vbcol=seagreen]
> Well I am not login as sa. I am using windows authentication and the
> login
> user I have is member of the Administrators group on the local computer
> where
> I am trying to save the file containing the backup of the master key.
>
> Thanks for your help anyway.
> "Laurentiu Cristofor [MSFT]" wrote:
>|||I got it. I find out that the SQL Server Account I was running does not hav
e
permissions on the Local computer. Now I am running the backup statements a
s
a job under the right account and I have to create another job to restore th
e
backup of the master key into the suscriber database.
Thanks again for your assistance.
"Laurentiu Cristofor [MSFT]" wrote:

> How you connect and the permissions you have don't matter in this case -
> when you go outside the server, you go out with the service account
> credentials, not with the credentials you connected to the server.
> So, find out what account SQL Server is running as, and make sure that
> account has access to the location where you want to save the backup file.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:DFF1B05E-DE27-4C72-BCE5-16C640CDFCB3@.microsoft.com...
>
>sql

Backing up database file groups with SMO

Hi,

I am trying to backup file groups with Backup class:

Server svr = new Server();
Backup bkp = new Backup();
bkp.Database = "AdventureWorks";
bkp.Action = BackupActionType.Files;
bkp.DatabaseFileGroups.Add("WorkOrderGroup");
bkp.DeviceType = DeviceType.File;
bkp.Devices.Add(@."c:\SMOTest_FG.bak");
bkp.SqlBackup(svr);

Unfortunately, BackupRestoreBase.DatabaseFileGroups property does not have set method (i.e. it is read-only). How can I perform a database file group backup?

Thanks in advance,

Mark

Hi Mark,

BackupRestoreBase.DatabaseFileGroups does not have a set method, but you can add DatabaseFileGroups using the Add method and that seems to work. Here is a sample application you can use (I am creating a new database, you can use an existing one) -

Server svr = new Server(".");
Database db = new Database(svr, "sampleDB");
db.Create();
FileGroup fg = new FileGroup(db, "fg1");
fg.Create();
Backup bkp = new Backup();
bkp.Database = db.Name;
bkp.Action = BackupActionType.Files;
bkp.DatabaseFileGroups.Add(db.FileGroups[0].Name);
bkp.DatabaseFileGroups.Add(db.FileGroups[1].Name);
bkp.Devices.Add(new BackupDeviceItem(@."c:\testbackup\sampleDb.bak", DeviceType.File));
bkp.SqlBackup(svr);

Thanks,
Kuntal

|||

Thanks! It worked.

In C++, I had to do the following:

Backup ^ bkpH = gcnew Backup();

bkpH->Database = gcnew String(dbName);

bkpH->Action = BackupActionType::Files;

StringCollection^ strCollH = bkpH->DatabaseFileGroups;

strCollH->Add(gcnew String(groupName));

...

Backing up database file groups with SMO

Hi,

I am trying to backup file groups with Backup class:

Server svr = new Server();
Backup bkp = new Backup();
bkp.Database = "AdventureWorks";
bkp.Action = BackupActionType.Files;
bkp.DatabaseFileGroups.Add("WorkOrderGroup");
bkp.DeviceType = DeviceType.File;
bkp.Devices.Add(@."c:\SMOTest_FG.bak");
bkp.SqlBackup(svr);

Unfortunately, BackupRestoreBase.DatabaseFileGroups property does not have set method (i.e. it is read-only). How can I perform a database file group backup?

Thanks in advance,

Mark

Hi Mark,

BackupRestoreBase.DatabaseFileGroups does not have a set method, but you can add DatabaseFileGroups using the Add method and that seems to work. Here is a sample application you can use (I am creating a new database, you can use an existing one) -

Server svr = new Server(".");
Database db = new Database(svr, "sampleDB");
db.Create();
FileGroup fg = new FileGroup(db, "fg1");
fg.Create();
Backup bkp = new Backup();
bkp.Database = db.Name;
bkp.Action = BackupActionType.Files;
bkp.DatabaseFileGroups.Add(db.FileGroups[0].Name);
bkp.DatabaseFileGroups.Add(db.FileGroups[1].Name);
bkp.Devices.Add(new BackupDeviceItem(@."c:\testbackup\sampleDb.bak", DeviceType.File));
bkp.SqlBackup(svr);

Thanks,
Kuntal

|||

Thanks! It worked.

In C++, I had to do the following:

Backup ^ bkpH = gcnew Backup();

bkpH->Database = gcnew String(dbName);

bkpH->Action = BackupActionType::Files;

StringCollection^ strCollH = bkpH->DatabaseFileGroups;

strCollH->Add(gcnew String(groupName));

...

Backing Up A MDF

I plan on using a database file for my site, how would I back it up if I were to make changes to my site?

From the sql server management studio, you can choose a backup option from the menus. Right-click on the database entry, choose Tasks, then Backup.

You can restore it using the Restore command.

Alternatively, you can Detach the database file, make a copy using windows, then reattach.

|||

Can this be done on my site, if I use ftp, can I download the file?

|||

Are you using a hosting provider? If you are, you will probably have to use their backup utility.

Once you use it, it will build a file that you should be able to ftp.

|||

Using SQL DMO for automated Database Backup and Restore.

Or,

This code may help you:

public static void BackupDatabase() {string sConnect = Properties.Settings.Default.BackupConnectionString;string dbName;using (SqlConnection cnn =new SqlConnection(sConnect)) { cnn.Open(); dbName = cnn.Database.ToString(); ServerConnection sc =new ServerConnection(cnn); Server sv =new Server(sc);// Check that I'm connected to the user instance Console.WriteLine(sv.InstanceName.ToString());// Create backup device item for the backup BackupDeviceItem bdi =new BackupDeviceItem(@."C:\AppDataBackup\SampleBackup.bak", DeviceType.File);// Create the backup informaton Backup bk =new Backup(); bk.Devices.Add(bdi); bk.Action = BackupActionType.Database; bk.BackupSetDescription ="SQL Express is a great product!"; bk.BackupSetName ="SampleBackupSet"; bk.Database = dbName; bk.ExpirationDate =new DateTime(2007, 5, 1); bk.LogTruncation = BackupTruncateLogType.Truncate;// Run the backup bk.SqlBackup(sv); Console.WriteLine("Your backup is complete."); } }

|||

Sql server comes with the capability to do a batch job, so you can just tell it to run the backup program without having to write a program...

Backing up a file or table?

Is a file the same thing as a table?
Backup Database xxxx
file = 'whatever'
to somedevice
error:
file 'whatever' is not a database file for database 'xxxx'
'whatever' is a table in the database, but is it a file?
My goal is to backup only a few tables in a DB...not all of them.
Thanks,
Don
sql 7.0 or 2000
You can not backup at the table level, only DB, File or Filegroup. You
might want to BCP out the data from the table(s) in question if you don't
care about all the other data. You can however do periodic File or
Filegroup backups. That means you can create a new filegroup and place the
table(s) in that filegroup. Then you can backup that filegroup on a
different schedule than the rest of the db. But that does not mean you can
stop backing up the rest of the db or the logs. They are all required to do
a proper Filegroup backup or restore. See more details in BooksOnLine.
Andrew J. Kelly SQL MVP
"DonSQL2222" <DonSQL2222@.discussions.microsoft.com> wrote in message
news:FD970E6A-41E8-4C53-82E6-A7BAE70AFB47@.microsoft.com...
> Is a file the same thing as a table?
> Backup Database xxxx
> file = 'whatever'
> to somedevice
> error:
> file 'whatever' is not a database file for database 'xxxx'
> 'whatever' is a table in the database, but is it a file?
> My goal is to backup only a few tables in a DB...not all of them.
> Thanks,
> Don
> sql 7.0 or 2000
sql

backing up a DB to a CD

HI folks
I have done a full DB backup to a 2nd HDD (both HDD's are NTFS). If I use
Microsoft Backup or just copy the backup file to a CD, can that CD be used t
o
do a restore if necessary?
Thanks in advance
Kind regards
Ross PetersenYes. The media doesn't matter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:5BC55CED-ECB7-4638-B960-B62254C89C6C@.microsoft.com...
HI folks
I have done a full DB backup to a 2nd HDD (both HDD's are NTFS). If I use
Microsoft Backup or just copy the backup file to a CD, can that CD be used
to
do a restore if necessary?
Thanks in advance
Kind regards
Ross Petersen

backing up 2005 to something I can restore to 2000

I have an exiting database which I'd rather not manually recreate that I'd
like get a backup file from that I can restore to a 2000 database.
Is this in principle possible?
PaulYou cannot restore a SQL Server 2005 backup to SQL Server 2000. You have to
transfer objects instead using SSIS.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"PJ6" <noone@.nowhere.net> wrote in message
news:eGgw0I%23iGHA.4284@.TK2MSFTNGP05.phx.gbl...
> I have an exiting database which I'd rather not manually recreate that I'd
> like get a backup file from that I can restore to a 2000 database.
> Is this in principle possible?
> Paul
>

Backing up 2 databases..

Hi,
Is it ok to backup 2 databases in one file. Our vb.net app uses 2 databases (created on MSDE) and we want to prompt user to do backup during uninstallation but do not want to ask them twice (for each database). Then later on we want to be able to restor
e from the backup files too.
Any suggestions.
Thanks
hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:9A3740A3-E7C2-44B6-B717-29682B262BD5@.microsoft.com...
> Hi,
> Is it ok to backup 2 databases in one file. Our vb.net app uses 2
databases (created on MSDE) and we want to prompt user to do backup during
uninstallation but do not want to ask them twice (for each database). Then
later on we want to be able to restore from the backup files too.
> Any suggestions.
> Thanks
each backup file can contain more than 1 single database backup set, even
from multiple databases...
you have to specify the syntax as
BACKUP DATABASE databasename
TO DISK = N'c:\..\phisical path.bck'
WITH INIT
for the very first backup, in order to insure it overwrites any existing
one, while you have to omit the WITH INIT clause for additional database
backup set you want to include in the same backup device..
in order to restore from it, you have to use the syntax as
RESTORE DATABASE databasename
FROM DISK = N'c:\..\phisical path.bck'
WITH FILE = n
where n is the backup set file position within the backup device...
you can see the whole content of your backup device executing
RESTORE HEADERONLY
FROM DISK = N'c:\..\phisical path.bck'
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
But is it regarded as good programming practise or not..
dev
|||There is nothing inherently "bad" with having several backups in one backup device.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"dev" <anonymous@.discussions.microsoft.com> wrote in message
news:D80398A4-4B44-4365-8C66-2F257114C237@.microsoft.com...
> Thanks Andrea,
> But is it regarded as good programming practise or not..
> dev

back upwith out any machine specific data

How can i take back up of a data base
with out having any machine/system specific
data in the .bak file?

What exactly do you mean by machine specific data?

When you take a backup your database is backed up, nothing more, nothing less.

If you are talking about the file location this can be changed in the RESTORE statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Data or information? What issues are you trying to prevent with this? If you do a restore headeronly, you will see the server name that backed up the file. Is that the kind of thing you are concerned about?

Otherwise, as WesleyB said, you can backup on one server and restore to another.

-Sue

|||i cant restore a db ,in another system

i am getting error like

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Microsoft SQL Server 2005\mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)

i am trying to restore from a .bak file

i have sql servrer in D:
but on another system it in c:
|||

That's where the MOVE statement is for.

From BOL:

RESTORE DATABASE TestDB
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\testdb.ldf';

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

back up too slow

We are using ARCServe to back up all the file, Nomarlly, it took two hours to finish, but now it took two days haven't finished, anybody can figure out for me, or let me know how to figure out?Thanks.In order to significantly improve both the backup speed and reliability, I would suggest you install a different backup package. Personally, I like Backup Exec, but I'm not very finicky (other than not liking ArcServe).

-PatP

Back up the transaction log for the database to free up some log s

I have a server with 80 GB free space. Logfile for the database is set to
autogrow (10%), unrestricted. The log file itself is right now about 25 MB.
This message can't be accurate, so I'm wondering what could cause this.
There was no activity that would cause the logfile to grow, certainly not 80
GB.
I've seen this intermittently, no standard, easily reproducible cause. In
this particular case, I was doing a defrag of the file system, and trying to
parse a query in an SSIS package.
I've googled this (of course) and found a few people who've seen similar
error messages with large amounts of free space, but nothing conclusive.
TIA
jdnIt is probably because your log file is so small and can't hold all the
transactions you are trying to put into it. That will force an autogrow. IF
the file can not grow fast enough you can get a timeout and this message.
Always keep plenty of free space in both the log and data files. And most
importantly do not turn on AutoShrink or run any jobs to shrink the files.
So grow your log file to a size that is large enough to hold all the
transactions you will do in the time period between the log backups and
leave it there. Also change the growth for a % to a fixed size of say40 or
50MB (your mileage may vary).
--
Andrew J. Kelly SQL MVP
"jdn" <jdn@.discussions.microsoft.com> wrote in message
news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
>I have a server with 80 GB free space. Logfile for the database is set to
> autogrow (10%), unrestricted. The log file itself is right now about 25
> MB.
> This message can't be accurate, so I'm wondering what could cause this.
> There was no activity that would cause the logfile to grow, certainly not
> 80
> GB.
> I've seen this intermittently, no standard, easily reproducible cause. In
> this particular case, I was doing a defrag of the file system, and trying
> to
> parse a query in an SSIS package.
> I've googled this (of course) and found a few people who've seen similar
> error messages with large amounts of free space, but nothing conclusive.
> TIA
> jdn|||Well, that makes as much sense as anything else I've read. We'll set that up
and see if the error recurs.
Thanks.
jdn
"Andrew J. Kelly" wrote:
> It is probably because your log file is so small and can't hold all the
> transactions you are trying to put into it. That will force an autogrow. IF
> the file can not grow fast enough you can get a timeout and this message.
> Always keep plenty of free space in both the log and data files. And most
> importantly do not turn on AutoShrink or run any jobs to shrink the files.
> So grow your log file to a size that is large enough to hold all the
> transactions you will do in the time period between the log backups and
> leave it there. Also change the growth for a % to a fixed size of say40 or
> 50MB (your mileage may vary).
> --
> Andrew J. Kelly SQL MVP
>
> "jdn" <jdn@.discussions.microsoft.com> wrote in message
> news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
> >I have a server with 80 GB free space. Logfile for the database is set to
> > autogrow (10%), unrestricted. The log file itself is right now about 25
> > MB.
> >
> > This message can't be accurate, so I'm wondering what could cause this.
> > There was no activity that would cause the logfile to grow, certainly not
> > 80
> > GB.
> >
> > I've seen this intermittently, no standard, easily reproducible cause. In
> > this particular case, I was doing a defrag of the file system, and trying
> > to
> > parse a query in an SSIS package.
> >
> > I've googled this (of course) and found a few people who've seen similar
> > error messages with large amounts of free space, but nothing conclusive.
> >
> > TIA
> > jdn
>
>

Back up the transaction log for the database to free up some log s

I have a server with 80 GB free space. Logfile for the database is set to
autogrow (10%), unrestricted. The log file itself is right now about 25 MB.
This message can't be accurate, so I'm wondering what could cause this.
There was no activity that would cause the logfile to grow, certainly not 80
GB.
I've seen this intermittently, no standard, easily reproducible cause. In
this particular case, I was doing a defrag of the file system, and trying to
parse a query in an SSIS package.
I've googled this (of course) and found a few people who've seen similar
error messages with large amounts of free space, but nothing conclusive.
TIA
jdn
It is probably because your log file is so small and can't hold all the
transactions you are trying to put into it. That will force an autogrow. IF
the file can not grow fast enough you can get a timeout and this message.
Always keep plenty of free space in both the log and data files. And most
importantly do not turn on AutoShrink or run any jobs to shrink the files.
So grow your log file to a size that is large enough to hold all the
transactions you will do in the time period between the log backups and
leave it there. Also change the growth for a % to a fixed size of say40 or
50MB (your mileage may vary).
Andrew J. Kelly SQL MVP
"jdn" <jdn@.discussions.microsoft.com> wrote in message
news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
>I have a server with 80 GB free space. Logfile for the database is set to
> autogrow (10%), unrestricted. The log file itself is right now about 25
> MB.
> This message can't be accurate, so I'm wondering what could cause this.
> There was no activity that would cause the logfile to grow, certainly not
> 80
> GB.
> I've seen this intermittently, no standard, easily reproducible cause. In
> this particular case, I was doing a defrag of the file system, and trying
> to
> parse a query in an SSIS package.
> I've googled this (of course) and found a few people who've seen similar
> error messages with large amounts of free space, but nothing conclusive.
> TIA
> jdn