Database Movement

Hello, I normally use the database backup and restore commands when I am
moving one database for one computer to another. This time however, I
brought over the raw files for the log and data. How can I get sql server
to incorporate these files into the server that I am placing them on. This
server does not have that database at all. I moved the files to the
location it normally places the log and datafiles, but when I brought SQL
server up, it did not show me those files in enterprise manager.
Any ideas how to utilize these files in SQL server?
Thanks in advance for your assistance!!!!!!Look up sp_attach_db and sp_detach_db in BOL (Books On-Line).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C_Silin" <OverwhelmedButStillTrying@.hotmail.com> wrote in message
news:Xns9485989199631CSilinhotmailcom@.207.46.248.16...
> Hello, I normally use the database backup and restore commands when I am
> moving one database for one computer to another. This time however, I
> brought over the raw files for the log and data. How can I get sql server
> to incorporate these files into the server that I am placing them on.
This
> server does not have that database at all. I moved the files to the
> location it normally places the log and datafiles, but when I brought SQL
> server up, it did not show me those files in enterprise manager.
> Any ideas how to utilize these files in SQL server?
> Thanks in advance for your assistance!!!!!!

Database Movement

Hello, I normally use the database backup and restore commands when I am
moving one database for one computer to another. This time however, I
brought over the raw files for the log and data. How can I get sql server
to incorporate these files into the server that I am placing them on. This
server does not have that database at all. I moved the files to the
location it normally places the log and datafiles, but when I brought SQL
server up, it did not show me those files in enterprise manager.
Any ideas how to utilize these files in SQL server?
Thanks in advance for your assistance!!!!!!Look up sp_attach_db and sp_detach_db in BOL (Books On-Line).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C_Silin" <OverwhelmedButStillTrying@.hotmail.com> wrote in message
news:Xns9485989199631CSilinhotmailcom@.20
7.46.248.16...

quote:

---

> Hello, I normally use the database backup and restore commands when I am
> moving one database for one computer to another. This time however, I
> brought over the raw files for the log and data. How can I get sql server
> to incorporate these files into the server that I am placing them on.

---

This

quote:

---

> server does not have that database at all. I moved the files to the
> location it normally places the log and datafiles, but when I brought SQL
> server up, it did not show me those files in enterprise manager.
> Any ideas how to utilize these files in SQL server?
> Thanks in advance for your assistance!!!!!!

---

Database mirroring and log file growth

Is there a way to shrink the log file while using database mirroring?
We have a 20 GB database with a log file about 80 GB.
Transaction log backup is done every 30 minutes, full backup each night, but the log file is not truncated.

Since it seems that the log file grows about 1 GB each day (we have migrated from sql2000 about 50 days ago) we are going to run out of disk space in a few weeks!

Ivan

refer this link,

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=377087

they have suggested to ,issue checkpoint and then truncate log......

USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO

|||

Is the Database also part of replication ?

Is the Database Mirroring Active , STATE = SYNCRONIZED ?

|||just try the above method and get back if its not possible you need to remove mirroring

refer, http://technet.microsoft.com/en-us/library/ms189112.aspx then try to truncate the tran log and try to shrink using dbcc shrinkfile........i am not sure which of the above 2 will work out.........|||

Removing and Reestablishing mirroring for a large database might be some work , again it depends how quickly you want to fix it , however what i will suggest ; check if the scheduled TLOG backups are successfull ? if the database is acting as a publisher in replication topology ; also if you could post the mode of mirroring Sync or Async ?

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strategy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
Chris
Which mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris
|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strate
gy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
ChrisWhich mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strategy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
ChrisWhich mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chrissql

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2?
I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>sql

Database mirroring

Hi,

Is there a way to automatically mirror/backup a SQL Express database with a SQL Server 2005 licenced system?

They will be on seperate machines on the internet.

I have done manual backup and restores but would like to allow constant updates to allow both databases to be synchronized.

I read that the SQL Server 2005 version has a mirroring facility but I also read that this is not possible in the Express version.

What other (automated) options do I have?

Thanks Andrew

Hi Andrew,

SQL Express does not support Mirroring but it can serve as a Witness for other Mirrored servers.

You can automate backup, or any other scriptable action, by generating a .sql script file and then using Windows Task Schedule to create a Task to run the script using SQLCmd. Jasper Smith wrote an article about doing this and also offers a tool called ExpressMaint that you can use as part of your script to run backups.

Mike

Database Mirroring

Hello Everyone,
I need to know all about SQL server 2005 Database Mirroring.
For your information,I want to have a server backup,that if my original
server is hanged then another backup server start to work.
Thanks,
Nassa
Database mirroring can do this. As a start to learn about how to do it you
might want to check out some of the database mirroring webcasts.
[url]http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032290575&EventC ategory=5&culture=en-US&CountryCode=US[/url]
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nassa" <nassim.czdashti@.gmail.com> wrote in message
news:1168754222.894158.146980@.m58g2000cwm.googlegr oups.com...
> Hello Everyone,
> I need to know all about SQL server 2005 Database Mirroring.
> For your information,I want to have a server backup,that if my original
> server is hanged then another backup server start to work.
> Thanks,
> Nassa
>

Database Mirgration

Hi,
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>
The database users and roles will migrate, however the SQL Server logins
will not. You will have to recreate your logins (Windows Authenticated
and/or SQL Authenticated) on the new server.
The Windows Authenticated users will automatically match the id's in the
database, the SQL Authenticated users however will not. You will need to
take a look at the sp_change_users_login in BOL to get those SQL
Authenticated users to match up with the newly created SQL Logins.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||When you attach/detach backup/restore a database, the users and database
roles are of course moved across, as they are stored within the database.
But the logins these database users are linked to, will not be moved, as
they are in the master database.
For more info, on how to troubleshoot this issue, see:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
Hi,
When I do the database migration from one server to another using attach
deattach /backup Restore, do all the users and roles of the database
automatically be migrated as well.
I am under the impression that the users and roles are in Master database.
Do I need to use Transfer Login Task to do the Login Migration?
Thanks
Ed|||yes! all the users and roles are migrated to a new server.
but, the logins are not migrated
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ed" wrote:

> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master databas
e.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>|||Hi,
If you migrate Master database as well then all the Logins, users , Roles
will be migrated to new server. Incase if you are migrating the
Master database then Logins will not get migrated. In that case you could
use Transfer Login Task to do login migration.
Thanks
Hari
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2AE42947-4BAA-4654-BAF7-02C340286E2C@.microsoft.com...
> Hi,
> When I do the database migration from one server to another using attach
> deattach /backup Restore, do all the users and roles of the database
> automatically be migrated as well.
> I am under the impression that the users and roles are in Master
> database.
> Do I need to use Transfer Login Task to do the Login Migration?
> Thanks
> Ed
>sql

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time o
f
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) o
r
maybe exist a better way to do it.
What can you advice me?
Thanks
RodrigoBackups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
David Portas
SQL Server MVP
--|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:

> Have a look at this article: http://vyaskn.tripod.com/moving_sql...skn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in messag
e
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
>
>sql

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time of
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) or
maybe exist a better way to do it.
What can you advice me?
Thanks
Rodrigo
Backups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
David Portas
SQL Server MVP
|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo
|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:

> Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
>
>

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time of
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) or
maybe exist a better way to do it.
What can you advice me?
Thanks
RodrigoBackups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
--
David Portas
SQL Server MVP
--|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> > Hello.
> >
> > I'm going to migrate an entrie SQL Server to a new box. The original plan
> > was to backup and restore all DBs on the new server, but due the long time
> > of
> > the backup I'd like to try other way.
> >
> > I'm thinking of simply offline->copy->attach .MDF to the new server, but
> > as
> > the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> > a
> > simple attach is risk-free (basically in terms of systables/sysprocedures)
> > or
> > maybe exist a better way to do it.
> >
> > What can you advice me?
> >
> > Thanks
> > Rodrigo
>
>sql

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
HansEither use your favorite API (like ADO, ADO.NET etc) and just execute the SQ
L statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLD
MO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to i
nvest too much effort
into this API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to
invest too much effort
> into this API.
>|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:

> Either use your favorite API (like ADO, ADO.NET etc) and just execute the
SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQ
LDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to
invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
>
>

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
HansEither use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
into this API.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
>|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:
> Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> > Hi,
> >
> > I want to import, export and backup databases through my application.
> >
> > can anybody tell me the best way to program database management?
> >
> > Thanx for any help!
> > Hans
>
>

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
Hans
Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
into this API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans
|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
>
|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:

> Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
>
>

Database Maintenance problem

Hi again,
i was wondering if anyone has encountered this problem:
I make a database maintenance plan (through the wizard) because i want to
backup the
server files to another computer on my network
I have made a map to the other computer (as G:),
and i then go to the tab "Complete Backup" and then i select "Use this
directory" option
to select the directory for the baclup files in the other computer.
But, the listbox presented at me there, displays only the locals C:\ and D:\
drives,
not the other mapped one (G:).
I manually enter the full path name and of cource the job cannot execute.
Has anyone any solution to that ?
anthonybI use UNC paths and it works fine for me. Remember that YOU are not going
to be executing this plan it will be SQL Server Agent so G may have
absolutely no meaning to it whatsoever.
Allan
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anthony Boudouvas" <anthonyb@.mediatel.gr> wrote in message
news:eK88Tv0VDHA.2252@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> i was wondering if anyone has encountered this problem:
> I make a database maintenance plan (through the wizard) because i want to
> backup the
> server files to another computer on my network
> I have made a map to the other computer (as G:),
> and i then go to the tab "Complete Backup" and then i select "Use this
> directory" option
> to select the directory for the baclup files in the other computer.
> But, the listbox presented at me there, displays only the locals C:\ and
D:\
> drives,
> not the other mapped one (G:).
> I manually enter the full path name and of cource the job cannot execute.
> Has anyone any solution to that ?
> anthonyb
>

Database Maintenance Plans for Backup

Hello

We created maintenance plans for Backup, we configured as:

1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)

2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.

please advice. Thanks, Jay

If you are using a single backup device (whether it's a tape, file location...it's the same thing), overwrite means it will replace the backup on that device with the current backup. So that device will just hold the one backup. Append means it will add the current backup to the device. That device will hold multiple backups.

If you use a single device, set it to overwrite and the backup has not expired and you have selected the option to check the expiration date of the backup, you will get an error. It is designed to prevent you from accidentally overwritting a backup that you want to keep.

-Sue