topic Re: A layer is added to the mdf file while it (mdf file) emains with same size, in Data Management Questions

A layer is added to the mdf file while it (mdf file) emains with same size,

JamalNUMAN — Thu, 02 May 2013 18:29:10 GMT

Layer is added while the mdf file remains with same size,

I�??m wondering how the mdf file remains with same size despite the fact that a layer is copied and past to it (mdf)

[ATTACH=CONFIG]23969[/ATTACH], [ATTACH=CONFIG]23970[/ATTACH], [ATTACH=CONFIG]23971[/ATTACH]

What might be the issue here?

Thank you

Best

Jamal

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

Anonymous User — Thu, 02 May 2013 19:30:23 GMT

Original User: vangelo

This is just on of those "That's the way the database works" issues. DBMSes which
use file-based data stores (Ora, MS, INF, DB2, but not PG) manage the blocks
themselves. The blocks must be preallocated so initial inserts to a new table
*won't* change the file size, unless there's so many inserts that auto-grow
allocates more storage.

- V

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

JamalNUMAN — Thu, 02 May 2013 19:48:47 GMT

This is just on of those "That's the way the database works" issues. DBMSes which
use file-based data stores (Ora, MS, INF, DB2, but not PG) manage the blocks
themselves. The blocks must be preallocated so initial inserts to a new table
*won't* change the file size, unless there's so many inserts that auto-grow
allocates more storage.

- V

Many thanks Vince,

This is very risky! Particularly when taking backup by synchronizing the data!

My synchronization software didn�??t detect any change between the source and the destination and thus the new inserted layer is not backed up. This is really a problem!

[ATTACH=CONFIG]23980[/ATTACH]

What sort of solutions for such very challenging issue?

At the end of the day, I need to have a safe copy for my mdf files

Best

Jamal

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

Anonymous User — Thu, 02 May 2013 20:36:58 GMT

Original User: vangelo

Every connect to a database changes the files, but you can't identify which blocks
have been changed unless you let the database tell you. Again, this is a "that's
the way they work" issue. If your sync software doesn't recognize this, then it's
not a problem with the database, it's with the sync software (neither of which
is particularly on-topic for this forum).

- V

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

MarcoBoeringa — Thu, 02 May 2013 21:25:57 GMT

What sort of solutions for such very challenging issue?

At the end of the day, I need to have a safe copy for my mdf files

Best

Jamal

Jamal,

The solution is not to rely on synch software, but to use the build-in options of the DBMS to do the job in an automated scheduled way.

Here are some links that may be of use for SQL Server, but you can find a ton others by Googling:

http://msdn.microsoft.com/en-us/library/ms186289.aspx
http://technet.microsoft.com/en-us/library/ms187510.aspx
http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx
http://outcoldman.com/en/blog/show/214

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

Anonymous User — Fri, 03 May 2013 14:00:18 GMT

Original User: Jamal432@gmail.com

Every connect to a database changes the files, but you can't identify which blocks
have been changed unless you let the database tell you. Again, this is a "that's
the way they work" issue. If your sync software doesn't recognize this, then it's
not a problem with the database, it's with the sync software (neither of which
is particularly on-topic for this forum).

- V

Thanks Vince,

But we are not living in another planet! We use different software, operating systems, hardware, etc. and as end users we need to ensure maximum size of compatibility.

This is why I got confused as part of the data wasn�??t synchronized.

Integrity, consistency and workflow what might interest the end user.

Best

Jamal

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

JamalNUMAN — Fri, 03 May 2013 14:06:49 GMT

Jamal,

The solution is not to rely on synch software, but to use the build-in options of the DBMS to do the job in an automated scheduled way.

Here are some links that may be of use for SQL Server, but you can find a ton others by Googling:

http://msdn.microsoft.com/en-us/library/ms186289.aspx
http://technet.microsoft.com/en-us/library/ms187510.aspx
http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx
http://outcoldman.com/en/blog/show/214

Thank you very much Marco,

Yes. We do like to hear from experts some kind of solutions! And that�??s what you did Marco!

Well, I would prefer to have my mdf files synchronized with my software to ensure that all types of other data are backed up IN ONE GO!

However, this is a good solution

[ATTACH=CONFIG]23998[/ATTACH]

Appreciated

Jamal

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

Anonymous User — Sat, 04 May 2013 08:56:50 GMT

Original User: mboeringa2010

Thank you very much Marco,

Yes. We do like to hear from experts some kind of solutions! And that�??s what you did Marco!

Well, I would prefer to have my mdf files synchronized with my software to ensure that all types of other data are backed up IN ONE GO!

However, this is a good solution

[ATTACH=CONFIG]23998[/ATTACH]

Appreciated

Jamal

I think the recommended approach is to let the RDBMS handle the backup of your databases, as this takes care of recent edits in transaction logs as well if you choose to backup those as well, as shown in the links I posted.

That said, you may wish to do a system image backup of your server as well once in a while, if you are concerned with the entire state of the PC and all its software. On a regular Windows 7 / 8 "Home edition" type test machine, you can use the System Image backup option to do that (Control Panel / Backup). This will allow you to restore the entire system to a new hard disk in one go in case of a fatal hard disk failure. You will probably need to restore your database afterwards as well to get to the most recent state of the database.

There are probably more issues and things to take care of in a true Windows Server 2008/2012 server machine backup, consult the Microsoft pages for that. E.g. http://technet.microsoft.com/en-us/library/jj713536.aspx

Re: A layer is added to the mdf file while it (mdf file) emains with same size,

JamalNUMAN — Sat, 04 May 2013 19:03:53 GMT

I think the recommended approach is to let the RDBMS handle the backup of your databases, as this takes care of recent edits in transaction logs as well if you choose to backup those as well, as shown in the links I posted.

That said, you may wish to do a system image backup of your server as well once in a while, if you are concerned with the entire state of the PC and all its software. On a regular Windows 7 / 8 "Home edition" type test machine, you can use the System Image backup option to do that (Control Panel / Backup). This will allow you to restore the entire system to a new hard disk in one go in case of a fatal hard disk failure. You will probably need to restore your database afterwards as well to get to the most recent state of the database.

There are probably more issues and things to take care of in a true Windows Server 2008/2012 server machine backup, consult the Microsoft pages for that. E.g. http://technet.microsoft.com/en-us/library/jj713536.aspx

Many thanks Marco,

I got the point. The issue is that I have been using the sync software since long time ago (before being engaged to sql server data) and never had a problem.

If the mdf files do change with no changes in size and date then I�??m forced to try the options you have already recommended

All the best

Jamal