If I start with table1 and take a copy of it, call it table2.
How can I later determine whether there have been any changes have been made to table2 since the copy (so I can determine whether I need to copy back the changes to table1).
I'd like to use a 'last-updated-date' on the table, but I can't seem to find anything like that in the system tables.
I'm considering using:
exec sp_table_validation
to get checksums of table1 and table2 (I have no image, text or ntext fields) so this should work fine, though might be slow (since I'd have to select the full count(*) option).
I'd prefer to use timestamps if they're available.
And no, I don't want to use triggers.
Thanks,
- Andy AbelOther than using to trigger to *log* changes, you would have to do a full table *scan* to find the differences. This sure would take a long time to step through a large table.
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Thursday, March 22, 2012
Wednesday, March 21, 2012
Detect new files and populate date
Hi, I need to create a SSIS package for following usage:
I have a folder called c:\test. Every month, I copy a file with following format testMMYY.txt. (Month Year) from a client (A) for vendor B. I have a sql table which contains filemonth(, clientname and vendorname. How can I create a SSIS package to detect any new files, if any, and populate the month and year into file month, and populate clientname and vendorname.
Thanks,
You can use the FileWatcher task (available on SQLIS.com) to monitor for a file. If it is only a once a month thing, you could just run the package and use the File System task to check for existence of the file. You'll need to use a expression to set the ConnectionString property of your FlatFile connection manager to the correct value by concatenating the proper month and year into the file name.|||Thanks for the reply.
Maybe my description is a little bit misleading, my issue is to identify latest file and transform the file name, e.g. I0407.txt into April 2007 and insert it into the filemonth field in the vendorfile table.
Any thoughts?
Thanks,
|||You can use a script task and the DateAndTime class's MonthName function to translate the file name. This post (http://forums.microsoft.com/msdn/showpost.aspx?postid=1467831&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1) has the script for sorting the list of files in a directory and outputting in a DataTable object. Just take the last row of the data table for the latest file.Sunday, March 11, 2012
Detaching & Attaching Database
Hello Group.
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>
detach-copy-attach vs Copy Database Wizard
Instead of running the Copy Database Wizard, if I
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>
Friday, March 9, 2012
Detach/Attach to SQL Server 2005
Hello,
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
Thanks in advance,
sck10
SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>
|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
Thanks in advance,
sck10
SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>
|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
Detach/Attach to SQL Server 2005
Hello,
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
Thanks in advance,
sck10SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
Thanks in advance,
sck10SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
Detach/Attach to SQL Server 2005
Hello,
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
--
Thanks in advance,
sck10SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
--
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We have two developer machines. One is running SS2K and the other is
>> running SS2005. What we want to do is detach a SS2K database, copy it
>> and
>> then put it on the other machine and attached it to SS2005. When I
>> attach
>> the database, will it automatically be upgraded to a 2005 type database,
>> or
>> is there something else I need to do?
>> --
>> Thanks in advance,
>> sck10
>>
>
We have two developer machines. One is running SS2K and the other is
running SS2005. What we want to do is detach a SS2K database, copy it and
then put it on the other machine and attached it to SS2005. When I attach
the database, will it automatically be upgraded to a 2005 type database, or
is there something else I need to do?
--
Thanks in advance,
sck10SQL Server will perform database conversion when attaching database on SQL
2005. You should have seen a messag like :'Converting database 'dbanme'
from version 539 to the current version 607.'
--
HTH. Ryan
"sck10" <sck10@.online.nospam> wrote in message
news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have two developer machines. One is running SS2K and the other is
> running SS2005. What we want to do is detach a SS2K database, copy it and
> then put it on the other machine and attached it to SS2005. When I attach
> the database, will it automatically be upgraded to a 2005 type database,
> or
> is there something else I need to do?
> --
> Thanks in advance,
> sck10
>|||>> is there something else I need to do?
Ryan is correct that the database files are converted during the attach
operation. Once you've attached the database to a 2005 instance, you cannot
then detach it and move it back to the 2000 instance.
Also, be aware that the attached database retains it's SQL Server 2000 (80)
compatibility level. This means that the database will behave as if it is on
an instance of SQL Server 2000 for certain features. For example, queries
that contain the old *= and =* operators for outer joins will not work in
SQL Server 2005 when the database is in 90 compatibility level, but they
will work when the compatibility level is set to 80. So, keeping the
compatbility level at 80 may be useful if you need to retain some 2000
behaviors. On the other hand, to use some SQL Server 2005 features, you
will need to explicitly change the compatibility level to 90. For example,
to create database diagrams in SQL Server 2005, the database must be set to
90 compatibility level.
You change the database compatiblity level by using the sp_dbcmptlevel
stored procedure. See the Books Online topic on sp_dbcmptlevel for a list
of behavior changes between 80 and 90 levels.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OskG4AALGHA.3832@.TK2MSFTNGP09.phx.gbl...
> SQL Server will perform database conversion when attaching database on SQL
> 2005. You should have seen a messag like :'Converting database 'dbanme'
> from version 539 to the current version 607.'
> --
> HTH. Ryan
> "sck10" <sck10@.online.nospam> wrote in message
> news:%23ubCb9$KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We have two developer machines. One is running SS2K and the other is
>> running SS2005. What we want to do is detach a SS2K database, copy it
>> and
>> then put it on the other machine and attached it to SS2005. When I
>> attach
>> the database, will it automatically be upgraded to a 2005 type database,
>> or
>> is there something else I need to do?
>> --
>> Thanks in advance,
>> sck10
>>
>
Detach and attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
Detach and attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>> Hi Ellie,
>> The change in permissions on the mdf file after detaching and attaching
>> is by design in SQL Server 2005. This Books Online topic explains what
>> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Ellie" <nospam@.nospam.net> wrote in message
>> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>>
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>> Hi Ellie,
>> The change in permissions on the mdf file after detaching and attaching
>> is by design in SQL Server 2005. This Books Online topic explains what
>> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Ellie" <nospam@.nospam.net> wrote in message
>> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>>
>
Detach and attaching changing file permissions
Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>
Detach and Attach for Backup and Deployment?
Hello,
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
Saturday, February 25, 2012
Designing Aggregations
Does anyone know of a way to copy aggregations in the BI Studio?
I know you can copy aggregations in management studio after the cube is deployed...
Trying to do it at design time.
Thanks
Mark
You can switch to the "Code View" in BI dev studio and copy following property
<AggregationDesignID>YourAggregationDesignHere</AggregationDesignID>
From one partition to another.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)