Database Attach Scripts

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?
Something like this:
declare @.dbid int,
@.sql varchar(4000),
@.name sysname,
@.old_name sysname,
@.filename nchar(260);
declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;
open db_cr;
set @.sql = '';
set @.old_name = '';
fetch next from db_cr into @.dbid, @.filename;
while @.@.fetch_status = 0
begin
If @.old_name <> db_name(@.dbid)
select @.sql = @.sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
N''' + rtrim(@.filename) + '''';
else
select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
select @.old_name = db_name(@.dbid);
fetch next from db_cr into @.dbid, @.filename;
end
close db_cr;
deallocate db_cr;
print @.sql;
Linchi
"Charlie" wrote:

> I have a few existing 2000 instances that we are migrating to 2005 SP2. The
> instances have a lot of databases on them. Is there an easy way to generate
> the attach scripts for migrating?
|||Charlie
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:220D9152-7C48-41BF-8CD8-8C3DD32C2C35@.microsoft.com...
>I have a few existing 2000 instances that we are migrating to 2005 SP2.
>The
> instances have a lot of databases on them. Is there an easy way to
> generate
> the attach scripts for migrating?
|||What does this script do exactly?
"Linchi Shea" wrote:
[vbcol=seagreen]
> Something like this:
> declare @.dbid int,
> @.sql varchar(4000),
> @.name sysname,
> @.old_name sysname,
> @.filename nchar(260);
> declare db_cr cursor
> for select dbid, filename
> from master..sysaltfiles
> where dbid > 4
> order by dbid;
> open db_cr;
> set @.sql = '';
> set @.old_name = '';
> fetch next from db_cr into @.dbid, @.filename;
> while @.@.fetch_status = 0
> begin
> If @.old_name <> db_name(@.dbid)
> select @.sql = @.sql + char(13) + char(10) +
> 'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
> N''' + rtrim(@.filename) + '''';
> else
> select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
> select @.old_name = db_name(@.dbid);
> fetch next from db_cr into @.dbid, @.filename;
> end
> close db_cr;
> deallocate db_cr;
> print @.sql;
> Linchi
> "Charlie" wrote:
|||> What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> What does this script do exactly?
> "Linchi Shea" wrote:
|||Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name...
Make sense?
Maybe I am not understanding what you are telling me...
"Linchi Shea" wrote:
[vbcol=seagreen]
> Generate a T-SQL script to sp_attach_db every user database.
> Linchi
> "Charlie" wrote:
|||The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.
Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> Where does it get a list? We are moving the user db's to a new environment.
> I need to attach the raw files, sepcifying the .mdf and .ldf file locations
> and db name...
> Make sense?
> Maybe I am not understanding what you are telling me...
> "Linchi Shea" wrote:
|||That is really what I need!
This script does not print anything. A assume you want me to run it in
query analyzer?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:
|||Nevermind.
Sorry!
Thanks for the help!
"Charlie" wrote:
[vbcol=seagreen]
> That is really what I need!
> This script does not print anything. A assume you want me to run it in
> query analyzer?
> "Linchi Shea" wrote:
|||It truncates after 32 db's. Is there any way to avoid this?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:

Database and Report Server in Different Instances?

I have a database in the SQL 2005 default instance and a report server in a named instance. The database has a symmetric key for decrypting one of the columns. When the report server queries the database I get this error: "Cannot find the symmetric key 'key', because it does not exist or you do not have permission."

I know the symmtric key is there because it works when queried from the same instance. Should the database and report server be in the same instance? Or is there a way to change the permissions so that the report server can access the symmetric key in the other instance?

I might note that the report server is able to access unencrypted data in the other instance. The error only occurs when I try to decrypt data using the symmetric key.

I might follow up with this information. I believe that I need to grant permission on the symmetric key. However, I don't know the syntax for granting permission for the report server to access the key. It should be something like this:

grant references on symmetric key :: PasswordKey touser

Who is the user if the report server is in an instance named sqlexpress?

|||

The problem was with the user who was logging into the symmetric key. I used a SQLExpress profiler found athttp://sqlprofiler.googlepages.com/ to find out who was logging in when the report ran. Then I granted permissions to the symmetric key like this: grant references on symmetric key::PasswordKey touser

Database Aliases

Is there currently a way to created "database aliases"? We have many instances of SQL Server running in our development environment, and have a few metabases that are supposed to be in sync across all instances. It would be extremely handy to have a database "X" on Server A actually point to database "X" on Server B.

As far as any application is concerned, Server A has a database called "X", but in reality the database only physically exists on Server B.

It would be incredibly useful to centrally manage a database that is meant to be shared among many server installs. It would also help for app hosting situations.

If this isn't possible, I'll enter it as a feature request for a later version of SQL Server.

Thanks,
Bryan Somerville
Software Engineer
Retail Anywhere, Inc.

Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

**unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

hope it helps.

|||

Ahsukal wrote:

Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

**unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

hope it helps.

The concept is something of a beginning on what he's looking for, but he's after much, much more. Essentially, he's looking for a database name to be a pointer and nothing else. To use a linked server, at least in its simple form, the calling app would still have to be aware of the linked server (just as a linked server instead of as a different connection). One way you could somewhat force this would be to create a different linked server for each database, and train the application to that. After that, whether the linked server is just another name for the local server, or serves as a passthrough to a different server is transparent to the calling application. You should start off with all databases on the same server, then move several off to another server without the applicaiton knowing (simply by changing the link associated with the linked server paired to that database). Note, however, that you are building significant management and performance overhead into things by doing it this way. You much deal with the issues surrounding the security pass through, and, perhaps more importantly, you are running things through the connectitivity layer not once, but twice now (with all that associated overhead).

|||

I find this a really interresting Idea, but from a slightly different perspective. This would mean that you can have two (or more) kind of front end database servers handling receiving all the requests, and have the database servers located behind these. The frontend servers could be load balanced using NLB. This would potentially improve the security, as none of the clients needs to have access directly to the backend servers, only to the front-end, which does not contain any data.

I also see the advantage mentioned above regarding splitting the data. However, I can see an even greater improvement, it would simplify the process of migrating a database to new server. You do not need to do anything special on the client, nor the new server. You only have to update the reference, making it point to the new location. This would really be a nice feature, but unless the development team have started on this one already, I'm afraid it won't make it in SQL Server 2008.

|||That would also be a nice benefit, yes.

Regarding security passthrough - that's a very good point. Perhaps an explicit linked server connection or other trust relationship could be required for aliasing to take place.

The issue that made me ask about this is as follows:

Our primary application is based on an extremely complicated, industry-specific (retail) database standard. We have a metabase that defines more intuitive "objects" like Customers, Items, Discounts, etc, and bidirectional relationships between them all.

That database is often being added to, but is read only as far as the application is concerned. Unfortunately, we have database servers running locally on our dev systems, we have a multi-tiered distribution and testing system, we have sales giving demos, we have implementation staging and assisting with pilots, etc. We have to continuously backup and restore this database to keep things current, but it's hard to keep everything synced up. Replication adds a layer of complexity that we don't quite want. The application is extremely extensible - we can add complex new features without touching almost any code, provided we can keep the metabase up to date.

All we need is a way to say "hey server, this database, as far as you care, is located elsewhere". When it comes time to take a backup to restore on site, the central database will be our only concern. When a client is hosted in our datacenter, we can just point it to the linked database.
|||

Instead of replication, consider using log shipping. Essentially just utilize the log to forward the changes to the other copies of the database without the need for the low level monitoring and subtle changes that replication induces.

|||

As the other authors on this thread have already indicated, currently there is no support for this functionality. If you haven't already done so, please file a request via http://connect.microsoft.com. That way the requested functionality gets tracked as a customer request and can be considered for a future release.

Thank you.

Database Aliases

Is there currently a way to created "database aliases"? We have many instances of SQL Server running in our development environment, and have a few metabases that are supposed to be in sync across all instances. It would be extremely handy to have a database "X" on Server A actually point to database "X" on Server B.

As far as any application is concerned, Server A has a database called "X", but in reality the database only physically exists on Server B.

It would be incredibly useful to centrally manage a database that is meant to be shared among many server installs. It would also help for app hosting situations.

If this isn't possible, I'll enter it as a feature request for a later version of SQL Server.

Thanks,
Bryan Somerville
Software Engineer
Retail Anywhere, Inc.

Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

**unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

hope it helps.

|||

Ahsukal wrote:

Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

**unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

hope it helps.

The concept is something of a beginning on what he's looking for, but he's after much, much more. Essentially, he's looking for a database name to be a pointer and nothing else. To use a linked server, at least in its simple form, the calling app would still have to be aware of the linked server (just as a linked server instead of as a different connection). One way you could somewhat force this would be to create a different linked server for each database, and train the application to that. After that, whether the linked server is just another name for the local server, or serves as a passthrough to a different server is transparent to the calling application. You should start off with all databases on the same server, then move several off to another server without the applicaiton knowing (simply by changing the link associated with the linked server paired to that database). Note, however, that you are building significant management and performance overhead into things by doing it this way. You much deal with the issues surrounding the security pass through, and, perhaps more importantly, you are running things through the connectitivity layer not once, but twice now (with all that associated overhead).

|||

I find this a really interresting Idea, but from a slightly different perspective. This would mean that you can have two (or more) kind of front end database servers handling receiving all the requests, and have the database servers located behind these. The frontend servers could be load balanced using NLB. This would potentially improve the security, as none of the clients needs to have access directly to the backend servers, only to the front-end, which does not contain any data.

I also see the advantage mentioned above regarding splitting the data. However, I can see an even greater improvement, it would simplify the process of migrating a database to new server. You do not need to do anything special on the client, nor the new server. You only have to update the reference, making it point to the new location. This would really be a nice feature, but unless the development team have started on this one already, I'm afraid it won't make it in SQL Server 2008.

|||That would also be a nice benefit, yes.

Regarding security passthrough - that's a very good point. Perhaps an explicit linked server connection or other trust relationship could be required for aliasing to take place.

The issue that made me ask about this is as follows:

Our primary application is based on an extremely complicated, industry-specific (retail) database standard. We have a metabase that defines more intuitive "objects" like Customers, Items, Discounts, etc, and bidirectional relationships between them all.

That database is often being added to, but is read only as far as the application is concerned. Unfortunately, we have database servers running locally on our dev systems, we have a multi-tiered distribution and testing system, we have sales giving demos, we have implementation staging and assisting with pilots, etc. We have to continuously backup and restore this database to keep things current, but it's hard to keep everything synced up. Replication adds a layer of complexity that we don't quite want. The application is extremely extensible - we can add complex new features without touching almost any code, provided we can keep the metabase up to date.

All we need is a way to say "hey server, this database, as far as you care, is located elsewhere". When it comes time to take a backup to restore on site, the central database will be our only concern. When a client is hosted in our datacenter, we can just point it to the linked database.
|||

Instead of replication, consider using log shipping. Essentially just utilize the log to forward the changes to the other copies of the database without the need for the low level monitoring and subtle changes that replication induces.

|||

As the other authors on this thread have already indicated, currently there is no support for this functionality. If you haven't already done so, please file a request via http://connect.microsoft.com. That way the requested functionality gets tracked as a customer request and can be considered for a future release.

Thank you.