DB Backup Question

Hello,
I know the transaction log will grow while backing up a
database, but will the database file grow as well if the
database is not in use?
Any help would be greatly appreciated!
Thanks in advance.
If the Db is not being actively updated i would not expect either the
databse, or the log to grow t all during a backup.
I am not sure what you mean when you say you "know the log will grow". Is
there an underlying problem you are trying to resolve?
Mike John
"Gene S." <anonymous@.discussions.microsoft.com> wrote in message
news:21b801c4ac8b$c816de30$a601280a@.phx.gbl...
> Hello,
> I know the transaction log will grow while backing up a
> database, but will the database file grow as well if the
> database is not in use?
> Any help would be greatly appreciated!
> Thanks in advance.
sql

DB Backup Question

Hello,
I know the transaction log will grow while backing up a
database, but will the database file grow as well if the
database is not in use?
Any help would be greatly appreciated!
Thanks in advance.If the Db is not being actively updated i would not expect either the
databse, or the log to grow t all during a backup.
I am not sure what you mean when you say you "know the log will grow". Is
there an underlying problem you are trying to resolve?
Mike John
"Gene S." <anonymous@.discussions.microsoft.com> wrote in message
news:21b801c4ac8b$c816de30$a601280a@.phx.gbl...
> Hello,
> I know the transaction log will grow while backing up a
> database, but will the database file grow as well if the
> database is not in use?
> Any help would be greatly appreciated!
> Thanks in advance.

DB Backup from 1 PC & Restore to diff. PC

Friends,

I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:\...." directory.

Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:\...." directory only, as it has no partitions.

This restore process gives me error :

Restore failed for Server 'HSVM\MICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ?

Please try to solve the same for me.... W8ing...

Whe u move database from one machine to another , u can either do it by Detach/attach or backup /resotre method. U mentioned that u have backup of MDF and LDF. How have u taken this backup. Did u detached the database first and then copied or u stoped the services and then copied. Please let us know the process u carried out to take backup of MDF and LDF

If u have taken backup of database using Backup Database statement, then u must restore the database from this backup using With MOVE Option . Read about Restore Database with MOVE Option in BOL

From BOL

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Madhu

|||
I have taken Backup from PC(1) without stopping any services, from SQL Management Studio 2005 directly, using UI.
Database files were on "E:\...." directory.

Copied that "xyz.bak" file into other PC(2), which is having only "C:\..." Drive.|||

Now u follow these steps

(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set

(b) RESTORE DATABASE YourNewDatabaseName
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'

Above statement will restore the database to new location.

Madhu

|||
Practically I just tried with your suggession.... and ofcourse it worked. Thank you for that.

Are you aware of VB.NET ? and if yes, then MicroSoft.SqlServer.Management.Smo Object Model ?

Bcoz I have taken backup file manually, but restoring from .NET implementation.

If you aware of same, then plz guide me to use Restore Class of SMO Object Model.|||

check this

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

Madhu

|||
still i am not satisfied.

while restoring DB it is giving me the error :

Exception >> Restore failed for Server 'HSVM\MICROFIT'.
Inner Exception >> Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

where HSVM is computer name & MICROFIT is instance on SQL server.

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log fi
les to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I
get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's d
rive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, &
file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, &
file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to
another?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log
files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option,
I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's
drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path,
& file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path,
& file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach t
o another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data file
s to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not runni
ng under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs usin
g the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its ow
n Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.co
m...
> Conan Kelly wrote:
> Check the service account that SQL Server is running under, make sure that
account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data fi
les to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not run
ning under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs us
ing the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its
own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
Thanks for any help anyone can provide,
Conan Kelly
Conan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.
|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there.
>> It attaches as Read Only.
>> When I go into the DB's properties and try to change the Read-only option, I get an error message:
>> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
>> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> Could not restart database '(DB's name)'. Reverting back to old status.
>> ALTER DATABASE statement failed.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> sp_dboption command failed.
>> What am I doing wrong? How do I successfully detach from one & reattach to another?
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

DB and Log size Checking

Hi,
I am wondering what the best way is to go about getting data about
database size (free & used space). I have multiple servers that I
must keep an eye on so I am developing a little application that will
report on the DB and log size but I do not want to go to each server
with enterprise manager or query analyzer to check on them. As far as
log size information I have been using a stored procedure that retuns
results from DBCC SQLPERF(Logspace) but I have not been able to find a
similar function for the database stats. Any ideas?
Paul,
For whatever the current database is the system stored procedures:
sp_spaceused -- shows utilization
sp_helpfile -- shows the file sizes, e.g. the mdf and the ldf, etc.
I use these both to understand what is happening.
Russell Fields
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408231149.1aa9225a@.posting.google.c om...
> Hi,
> I am wondering what the best way is to go about getting data about
> database size (free & used space). I have multiple servers that I
> must keep an eye on so I am developing a little application that will
> report on the DB and log size but I do not want to go to each server
> with enterprise manager or query analyzer to check on them. As far as
> log size information I have been using a stored procedure that retuns
> results from DBCC SQLPERF(Logspace) but I have not been able to find a
> similar function for the database stats. Any ideas?
|||Thank you Russel!

DB and Log backup

Hello all,

We currently have disk space problem on our main SQL server (say server "A")
and I'l like to save DBs and logs backups on another server (say "B"). Is
that possible? I tried to set the backup path to point to B but it generate
an error. Serurity/Account issue? SS2K on A was running under the System
account. I tried to change it to an account that was created on both server.
Still an error.

Any help/suggestion would be appreciated.

Both server are running SS2000 SP3. Server A is W2K and B is NT4.

Thanks for your time.

Yannick"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:U4cFd.34222$TN6.1120153@.news20.bellglobal.com ...
> Hello all,
> We currently have disk space problem on our main SQL server (say server
> "A") and I'l like to save DBs and logs backups on another server (say
> "B"). Is that possible? I tried to set the backup path to point to B but
> it generate an error. Serurity/Account issue? SS2K on A was running under
> the System account. I tried to change it to an account that was created on
> both server. Still an error.
> Any help/suggestion would be appreciated.
> Both server are running SS2000 SP3. Server A is W2K and B is NT4.
> Thanks for your time.
> Yannick

The MSSQL service account on server A needs to be a domain account with
access to the share on server B where you want to place the backups. Then a
command like this should work fine:

backup MyDB to disk = '\\serverB\share\folder\MyDB.bak

If you have a local account on each server with the same name and password,
then it will probably work too (I've never tried it myself), but using a
domain account would be easier anyway. There's more information about using
a domain account in Books Online under "Setting up Windows Services
Accounts" and "Changing Passwords and User Accounts".

If this doesn't help, it would be useful to know exactly what backup command
you're executing and what the error messages are.

Simon|||Hello Simon,

Your command worked. But I tried to make a backup from a Maintenance Plan
and it was this SQL command that was not working. After some comparaison
between both command I finally realised it was not working because I wrote
in the destination \\serverB\share instead of \\serverB\share\ (forgot the
trailing backslash).

Thanks for your help.

Yannick

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41e55501$1_1@.news.bluewin.ch...
> "Yannick Turgeon" <nobody@.nowhere.com> wrote in message
> news:U4cFd.34222$TN6.1120153@.news20.bellglobal.com ...
>> Hello all,
>>
>> We currently have disk space problem on our main SQL server (say server
>> "A") and I'l like to save DBs and logs backups on another server (say
>> "B"). Is that possible? I tried to set the backup path to point to B but
>> it generate an error. Serurity/Account issue? SS2K on A was running under
>> the System account. I tried to change it to an account that was created
>> on both server. Still an error.
>>
>> Any help/suggestion would be appreciated.
>>
>> Both server are running SS2000 SP3. Server A is W2K and B is NT4.
>>
>> Thanks for your time.
>>
>> Yannick
>>
> The MSSQL service account on server A needs to be a domain account with
> access to the share on server B where you want to place the backups. Then
> a command like this should work fine:
> backup MyDB to disk = '\\serverB\share\folder\MyDB.bak
> If you have a local account on each server with the same name and
> password, then it will probably work too (I've never tried it myself), but
> using a domain account would be easier anyway. There's more information
> about using a domain account in Books Online under "Setting up Windows
> Services Accounts" and "Changing Passwords and User Accounts".
> If this doesn't help, it would be useful to know exactly what backup
> command you're executing and what the error messages are.
> Simon

DAVID/ASPNET Sql connection problem!

I am having a problem with .net ms sql connections...

My situation: I log onto my computer (Windows XP Pro) using the administrator account.
I have MSDE installed, with NT authorization.
I created a quick connection in ASP.net Web Matrix, and ran the code using the ASP.NET web matrix server - It ran perfectly, so i know the connection string is good, and so is all the other code i used for the connection... I know that it used the administrator account to access the sql server.

Then i copied and pasted the exact working code i had in web matrix to Visual Studio.net, and tried to run it using IIS, but it gave the following error:

Server Error in '/' Application.

Login failed for user 'DAVID\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DAVID\ASPNET'.

Source Error:

Line 11: Dim myCommand As New SqlCommand(CommandText, myConnection)
Line 12:
Line 13: myConnection.Open()
Line 14:
Line 15: ' Create Instance of Connection and Command Object

I only have MSDE installed on my PC, so i ran the following in DOS:

cd "c:\Program Files\Microsoft SQL Server\80\Tools\Binn"
osql -E -S DAVID -Q "sp_grantlogin 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_grantdbaccess 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_addrolemember 'db_owner', 'DAVID\ASPNET'"
pause

So now ASPNET should have full access to the sql server. (Stored Procedures ran fine, and the roles and accounts were created successfully.)

So i ran the ASP.net code in IIS again, but still the same error!

I have even tried re-installing asp.net!
I havent tried re-installing MSDE because i dont want to loose my data, but if i have to, then i guess i have to... :(

What do i do??I'm sure you understood, that your SQL Server did not accept DAVID\ASPNET as a login name?! Either there is indeed not such a user, or maybe you took the back-slash instead the slash. Could you post your connection string?|||I will get the connection string to you asap - its at home, not at work, so only tomorrow...

When installing the .NET framework, the user ASPNET is automatically added. ASP.NET uses this user for all the interaction it makes with the server (in most cases). So anyway - the ASPNET user is there, and my computer name is DAVID, and so is my SQL server instance called DAVID.

Ill try with the forward slash instead of using a back slash, but im pretty sure that i did it the right way... I copied it exactly as the asp error page had it...