We're moving the QA enviornment to a new server and I want to restore the
database backups (including all jobs).
I know I have to restore the application DB's + model. I think I should be
restoring msdb, but I'm unsure about master.
What other "gotchas" are waiting?
Thanks,
JayYou may or may not need to restore the system dbs depending on what you want
to accomplish. If there is nothing in MSDB that you want to keep other than
the jobs I would just script them and run the scripts on the new server.
Have a look at these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408.aspx Moving system dbs
2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
http://support.microsoft.com/?id=307775 Disaster Recovery Articles for
SQL Server
http://support.microsoft.com/?id=320125 Moving a Diagram
http://support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uDQ03YzGIHA.4592@.TK2MSFTNGP02.phx.gbl...
> We're moving the QA enviornment to a new server and I want to restore the
> database backups (including all jobs).
> I know I have to restore the application DB's + model. I think I should be
> restoring msdb, but I'm unsure about master.
> What other "gotchas" are waiting?
> Thanks,
> Jay
>
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Moving SSIS Package
Hi,
I have to move my SSIS Project from Machine1 to Machine2 in a Development environment.when i open the Project in Machine2 .i have to restore all the connections in the connection manager. even after restoreing the connection sucessfuly .the package looks perfectly with out any error.but when i run the package.i am getting the following error
[Connection manager "Srv1.DBTest"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ".
How to solve the problem.
Thanks
Jegan
I'd say the Oracle connection has lost the password, which may be caused by your Protectionlevel property for the package. I'd recomened using DoNotSaveSensitive, and then use a configuration to provide the connection details. This makes it easier if any connection detaisl chance as you jsut edit (common) configurations rather than having to chaneg the packages.
Setting the Protection Level of Packages
(http://msdn2.microsoft.com/en-us/library/d4b073c4-4238-41fc-a258-4e114216e185.aspx)
Are you using Windows Authentication or Sql Server Authentication in your Oledb connection. Try using the user id /pass one which you were using in machine one ....second if you are using windows authentication log in the machine two with the same user id for which ur oledb connection manager is configured...
I think it should solve your problem.....:--)
|||Darrne, its working fine . thanks for the help.
Vikram Thanks for the suggestion.
Monday, March 12, 2012
Moving SQL Database to a new server
I am moving a sql database from a Windows 2000 server to a new windows 2003
server. I read where there is 3 ways to do this: ? Backup and Restore
? Sp_detach_db and Sp_attach_db stored procedures
? Import and Export Data (to copy objects and data between SQL Server
databases)
My question is after reading the descriptions on these I still don't know if
I should do a backup and restore or a import and export. There will not be
anyone using the database when I will be working on it. Any suggestions?
Thanks!If it's a small db, just one data file and one log file, just detach and
re-attach. Otherwise,
I'd perform a backup and restore.
Remember to re-apply any database logins, roles, and users
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> I am moving a sql database from a Windows 2000 server to a new windows
2003
> server. I read where there is 3 ways to do this: . Backup and Restore
> . Sp_detach_db and Sp_attach_db stored procedures
> . Import and Export Data (to copy objects and data between SQL Server
> databases)
> My question is after reading the descriptions on these I still don't know
if
> I should do a backup and restore or a import and export. There will not
be
> anyone using the database when I will be working on it. Any suggestions?
> Thanks!|||Keep in mind the following:
1. You also have to transfer your logins from the source server to the dest.
server
2. Your jobs
3. Your custom error msg (if any) reside on system database
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armando Prato" wrote:
> If it's a small db, just one data file and one log file, just detach and
> re-attach. Otherwise,
> I'd perform a backup and restore.
> Remember to re-apply any database logins, roles, and users
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> 2003
> if
> be
>
>
server. I read where there is 3 ways to do this: ? Backup and Restore
? Sp_detach_db and Sp_attach_db stored procedures
? Import and Export Data (to copy objects and data between SQL Server
databases)
My question is after reading the descriptions on these I still don't know if
I should do a backup and restore or a import and export. There will not be
anyone using the database when I will be working on it. Any suggestions?
Thanks!If it's a small db, just one data file and one log file, just detach and
re-attach. Otherwise,
I'd perform a backup and restore.
Remember to re-apply any database logins, roles, and users
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> I am moving a sql database from a Windows 2000 server to a new windows
2003
> server. I read where there is 3 ways to do this: . Backup and Restore
> . Sp_detach_db and Sp_attach_db stored procedures
> . Import and Export Data (to copy objects and data between SQL Server
> databases)
> My question is after reading the descriptions on these I still don't know
if
> I should do a backup and restore or a import and export. There will not
be
> anyone using the database when I will be working on it. Any suggestions?
> Thanks!|||Keep in mind the following:
1. You also have to transfer your logins from the source server to the dest.
server
2. Your jobs
3. Your custom error msg (if any) reside on system database
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armando Prato" wrote:
> If it's a small db, just one data file and one log file, just detach and
> re-attach. Otherwise,
> I'd perform a backup and restore.
> Remember to re-apply any database logins, roles, and users
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> 2003
> if
> be
>
>
Moving SQL Database to a new server
I am moving a sql database from a windows 2000 server to a new windows 2003
server. I read where there is 3 ways to do this: ? Backup and Restore
? Sp_detach_db and Sp_attach_db stored procedures
? Import and Export Data (to copy objects and data between SQL Server
databases)
My question is after reading the descriptions on these I still don't know if
I should do a backup and restore or a import and export. There will not be
anyone using the database when I will be working on it. Any suggestions?
Thanks!
If it's a small db, just one data file and one log file, just detach and
re-attach. Otherwise,
I'd perform a backup and restore.
Remember to re-apply any database logins, roles, and users
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> I am moving a sql database from a windows 2000 server to a new windows
2003
> server. I read where there is 3 ways to do this: . Backup and Restore
> . Sp_detach_db and Sp_attach_db stored procedures
> . Import and Export Data (to copy objects and data between SQL Server
> databases)
> My question is after reading the descriptions on these I still don't know
if
> I should do a backup and restore or a import and export. There will not
be
> anyone using the database when I will be working on it. Any suggestions?
> Thanks!
|||Keep in mind the following:
1. You also have to transfer your logins from the source server to the dest.
server
2. Your jobs
3. Your custom error msg (if any) reside on system database
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armando Prato" wrote:
> If it's a small db, just one data file and one log file, just detach and
> re-attach. Otherwise,
> I'd perform a backup and restore.
> Remember to re-apply any database logins, roles, and users
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> 2003
> if
> be
>
>
server. I read where there is 3 ways to do this: ? Backup and Restore
? Sp_detach_db and Sp_attach_db stored procedures
? Import and Export Data (to copy objects and data between SQL Server
databases)
My question is after reading the descriptions on these I still don't know if
I should do a backup and restore or a import and export. There will not be
anyone using the database when I will be working on it. Any suggestions?
Thanks!
If it's a small db, just one data file and one log file, just detach and
re-attach. Otherwise,
I'd perform a backup and restore.
Remember to re-apply any database logins, roles, and users
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> I am moving a sql database from a windows 2000 server to a new windows
2003
> server. I read where there is 3 ways to do this: . Backup and Restore
> . Sp_detach_db and Sp_attach_db stored procedures
> . Import and Export Data (to copy objects and data between SQL Server
> databases)
> My question is after reading the descriptions on these I still don't know
if
> I should do a backup and restore or a import and export. There will not
be
> anyone using the database when I will be working on it. Any suggestions?
> Thanks!
|||Keep in mind the following:
1. You also have to transfer your logins from the source server to the dest.
server
2. Your jobs
3. Your custom error msg (if any) reside on system database
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armando Prato" wrote:
> If it's a small db, just one data file and one log file, just detach and
> re-attach. Otherwise,
> I'd perform a backup and restore.
> Remember to re-apply any database logins, roles, and users
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:0FC783C5-0C29-4ED2-996E-3F86F6A47C55@.microsoft.com...
> 2003
> if
> be
>
>
Moving SQL 2005 database to SQL2000
I have a SQL Server 2005 RTM database that I need to move to SQL2000. Under
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...
Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...
|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>
>
|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just copy
> the data?
> - Steve
> "Adam Machanic" wrote:
|||That's up to you and what options you use but yes it can copy all the
database objects.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...
Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...
|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>
>
|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just copy
> the data?
> - Steve
> "Adam Machanic" wrote:
|||That's up to you and what options you use but yes it can copy all the
database objects.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
Moving SQL 2005 database to SQL2000
I have a SQL Server 2005 RTM database that I need to move to SQL2000. Under
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
> >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
> >Under
> > 2005 Beta 2 I was able to save the database under 2005 and then restore it
> > under 2000. Now with RTM I get an error 3205: "Too many backup devices
> > specified for backup or restore; only 64 are allowed."
> >
> > Is there a way of getting the database (tables and stored procs) into
> > 2000?
> >
> > Thanks...
>
>|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just copy
> the data?
> - Steve
> "Adam Machanic" wrote:
> > Yes, there is a way, but not via backup/restore or detach/attach. You'll
> > have to copy the data using a tool such as SSIS, DTS, linked servers, or
> > BCP.
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >
> > "Steve" <Steve@.discussions.microsoft.com> wrote in message
> > news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
> > >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
> > >Under
> > > 2005 Beta 2 I was able to save the database under 2005 and then restore it
> > > under 2000. Now with RTM I get an error 3205: "Too many backup devices
> > > specified for backup or restore; only 64 are allowed."
> > >
> > > Is there a way of getting the database (tables and stored procs) into
> > > 2000?
> > >
> > > Thanks...
> >
> >
> >|||That's up to you and what options you use but yes it can copy all the
database objects.
--
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
>> Yes, there is a way, but not via backup/restore or detach/attach. You'll
>> have to copy the data using a tool such as SSIS, DTS, linked servers, or
>> BCP.
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Steve" <Steve@.discussions.microsoft.com> wrote in message
>> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>> >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>> >Under
>> > 2005 Beta 2 I was able to save the database under 2005 and then restore
>> > it
>> > under 2000. Now with RTM I get an error 3205: "Too many backup devices
>> > specified for backup or restore; only 64 are allowed."
>> >
>> > Is there a way of getting the database (tables and stored procs) into
>> > 2000?
>> >
>> > Thanks...
>>
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
> >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
> >Under
> > 2005 Beta 2 I was able to save the database under 2005 and then restore it
> > under 2000. Now with RTM I get an error 3205: "Too many backup devices
> > specified for backup or restore; only 64 are allowed."
> >
> > Is there a way of getting the database (tables and stored procs) into
> > 2000?
> >
> > Thanks...
>
>|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just copy
> the data?
> - Steve
> "Adam Machanic" wrote:
> > Yes, there is a way, but not via backup/restore or detach/attach. You'll
> > have to copy the data using a tool such as SSIS, DTS, linked servers, or
> > BCP.
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >
> > "Steve" <Steve@.discussions.microsoft.com> wrote in message
> > news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
> > >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
> > >Under
> > > 2005 Beta 2 I was able to save the database under 2005 and then restore it
> > > under 2000. Now with RTM I get an error 3205: "Too many backup devices
> > > specified for backup or restore; only 64 are allowed."
> > >
> > > Is there a way of getting the database (tables and stored procs) into
> > > 2000?
> > >
> > > Thanks...
> >
> >
> >|||That's up to you and what options you use but yes it can copy all the
database objects.
--
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
>> Yes, there is a way, but not via backup/restore or detach/attach. You'll
>> have to copy the data using a tool such as SSIS, DTS, linked servers, or
>> BCP.
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Steve" <Steve@.discussions.microsoft.com> wrote in message
>> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>> >I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>> >Under
>> > 2005 Beta 2 I was able to save the database under 2005 and then restore
>> > it
>> > under 2000. Now with RTM I get an error 3205: "Too many backup devices
>> > specified for backup or restore; only 64 are allowed."
>> >
>> > Is there a way of getting the database (tables and stored procs) into
>> > 2000?
>> >
>> > Thanks...
>>
Friday, March 9, 2012
Moving SQL 2005 database to SQL2000
I have a SQL Server 2005 RTM database that I need to move to SQL2000. Under
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>
>|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just cop
y
> the data?
> - Steve
> "Adam Machanic" wrote:
>|||That's up to you and what options you use but yes it can copy all the
database objects.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
>
2005 Beta 2 I was able to save the database under 2005 and then restore it
under 2000. Now with RTM I get an error 3205: "Too many backup devices
specified for backup or restore; only 64 are allowed."
Is there a way of getting the database (tables and stored procs) into 2000?
Thanks...Yes, there is a way, but not via backup/restore or detach/attach. You'll
have to copy the data using a tool such as SSIS, DTS, linked servers, or
BCP.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>I have a SQL Server 2005 RTM database that I need to move to SQL2000.
>Under
> 2005 Beta 2 I was able to save the database under 2005 and then restore it
> under 2000. Now with RTM I get an error 3205: "Too many backup devices
> specified for backup or restore; only 64 are allowed."
> Is there a way of getting the database (tables and stored procs) into
> 2000?
> Thanks...|||Thanks Adam.
Will SSIS copy everything (including the stored procs) or will it just copy
the data?
- Steve
"Adam Machanic" wrote:
> Yes, there is a way, but not via backup/restore or detach/attach. You'll
> have to copy the data using a tool such as SSIS, DTS, linked servers, or
> BCP.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:6EE9FA6D-4CEE-4DF9-81AA-95535FA8C756@.microsoft.com...
>
>|||Hi Steve
As there may be compatibility issues you may be better of scripting the
stored procedures.
John
"Steve" wrote:
[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just cop
y
> the data?
> - Steve
> "Adam Machanic" wrote:
>|||That's up to you and what options you use but yes it can copy all the
database objects.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:52B70E78-A610-4B06-91A9-94F90C84871F@.microsoft.com...[vbcol=seagreen]
> Thanks Adam.
> Will SSIS copy everything (including the stored procs) or will it just
> copy
> the data?
> - Steve
> "Adam Machanic" wrote:
>
Wednesday, March 7, 2012
moving replication to new servers
I've got a publisher, distributor and subscriber on different servers and I
want to move all 3 to different servers. Is there a way to restore the
publishing database
on the new publisher, the subscribing database on the new subscriber and
then continue replication? knowing, of course, that I'd have to set up the
new distributor as well.
I know I can create the new publication, backup the database and restore
that on the new subscriber and initialize replication backup, but the
databases are fairly large and if I can restore both the publisher and the
subscriber at the same time, it would save much time. I need to minimize
down time.
Thanks!
There is no real way to do this. What Microsoft does support is restoring
master, distribution, msdb, and the publication database on a new server,
restoring the last tlog of the publication database on the new server with
the keep_replication switch, taking the first server offline, rebooting the
standby after renaming it as the original publisher name, and then issuing a
sp_replrestart to get everything working again.
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
"Steve" <Steve.sam@.hotmail.com> wrote in message
news:eb2nTSzaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> I've got a publisher, distributor and subscriber on different servers and
> I want to move all 3 to different servers. Is there a way to restore the
> publishing database
> on the new publisher, the subscribing database on the new subscriber and
> then continue replication? knowing, of course, that I'd have to set up
> the new distributor as well.
> I know I can create the new publication, backup the database and restore
> that on the new subscriber and initialize replication backup, but the
> databases are fairly large and if I can restore both the publisher and the
> subscriber at the same time, it would save much time. I need to minimize
> down time.
> Thanks!
>
want to move all 3 to different servers. Is there a way to restore the
publishing database
on the new publisher, the subscribing database on the new subscriber and
then continue replication? knowing, of course, that I'd have to set up the
new distributor as well.
I know I can create the new publication, backup the database and restore
that on the new subscriber and initialize replication backup, but the
databases are fairly large and if I can restore both the publisher and the
subscriber at the same time, it would save much time. I need to minimize
down time.
Thanks!
There is no real way to do this. What Microsoft does support is restoring
master, distribution, msdb, and the publication database on a new server,
restoring the last tlog of the publication database on the new server with
the keep_replication switch, taking the first server offline, rebooting the
standby after renaming it as the original publisher name, and then issuing a
sp_replrestart to get everything working again.
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
"Steve" <Steve.sam@.hotmail.com> wrote in message
news:eb2nTSzaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> I've got a publisher, distributor and subscriber on different servers and
> I want to move all 3 to different servers. Is there a way to restore the
> publishing database
> on the new publisher, the subscribing database on the new subscriber and
> then continue replication? knowing, of course, that I'd have to set up
> the new distributor as well.
> I know I can create the new publication, backup the database and restore
> that on the new subscriber and initialize replication backup, but the
> databases are fairly large and if I can restore both the publisher and the
> subscriber at the same time, it would save much time. I need to minimize
> down time.
> Thanks!
>
Moving Replicated Databases to a replacement SQL Server
Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
Thanks,
Dick Campbell
You can do either of the options. However there are pros and cons for each of the approaches. I am assuming here that you are doing backup/restore or copy of mdf/ldf of the publisher server (But it applies to the subscriber also).
1. Backup/restore.
You can backup and restore the databases on a different (secondary) server. However you cannot expect that replication will work automatically when your primary server goes down. You will need to resetup replication on ths secondary box.
2. Copy of mdf/ldf.
This can be done too. However the steps you will need to follow are:
a. Copy the files from prmary to a backup location
b. Lets assume your primary now goes down
c. Now you will need to rename the secondary server to the primary server, then install SQL Server and replace the mdf/ldf from on this machine with the ones from the backup location. Replication should then work fine.
My above recommendations defintely apply for Merge replication. They may also apply to Transactonal rpelication but there could be some caveats.
You should defintely try out the scenarios on your test machines.
Moving replicated database to a new machine
Hello.
I'm running SQL Server 2000 (sp3a) and am moving the installation to a new
machine one database at a time (by using Backup/Restore). The new machine
and SQL installation also has a new name that is being kept.
The databases that I've moved thus far are not replicated databases so it's
been easy.
I'm uncertain about this approach when it comes to replicated databases. I
can find no information on Microsoft's site when it comes to moving a
"replicated" database other than a mention of it in one of their KB
articles, and it says something like "Find and read more information about
moving replicated databases", and that's it.
All input is welcome.
Thanks in advance,
Mike
Mike,
are you moving the publisher or subscriber? Is the new box named the same?
Is it in the same domain/trust relationship? Are the service startup
accounts the same? These are all important variables in deciding which way
to do it, or if it is even possible.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Mike Lopez" <MichaelLopez@.Emmerel.com> wrote in message
news:eAaZ6gJnEHA.3564@.tk2msftngp13.phx.gbl...
> Hello.
> I'm running SQL Server 2000 (sp3a) and am moving the installation to a new
> machine one database at a time (by using Backup/Restore). The new machine
> and SQL installation also has a new name that is being kept.
> The databases that I've moved thus far are not replicated databases so
it's
> been easy.
> I'm uncertain about this approach when it comes to replicated databases. I
> can find no information on Microsoft's site when it comes to moving a
> "replicated" database other than a mention of it in one of their KB
> articles, and it says something like "Find and read more information about
> moving replicated databases", and that's it.
> All input is welcome.
> Thanks in advance,
> Mike
>
|||Hi Paul. Thanks for the response.
I'm moving the publisher. The new box is named differently. Same
domain/trust. Service startup accounts are the same (domain account)..
Furthermore the distributor is a separate machine. That machine acts as both
the distributor and subscriber machine.
In my humble opinion the difference in the machine names are the deal
breaker.
What's your thoughts?
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5V%23XrJnEHA.2864@.tk2msftngp13.phx.gbl...
> Mike,
> are you moving the publisher or subscriber? Is the new box named the same?
> Is it in the same domain/trust relationship? Are the service startup
> accounts the same? These are all important variables in deciding which way
> to do it, or if it is even possible.
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> "Mike Lopez" <MichaelLopez@.Emmerel.com> wrote in message
> news:eAaZ6gJnEHA.3564@.tk2msftngp13.phx.gbl...
> it's
>
|||Mike,
from experience I agree. Replication enters the servername in several system
tables, which then breaks it if you move the publisher to another,
differently named server. Most people script out the publication, move the
publishing database to the new server, modify the script with the new
servername and logins and recreate the publication on the new server. You
may be able to do a nosync initialization in this case, provided you
synchronize before moving the database and prevent any modifications.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi again, Paul.
Yeah, that's pretty much what I was thinking.
I didn't realize that it was possible to do a nosynch initialization. That's
good because two of these databases are huge, and I spent a weekend setting
them up for replication the first time.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%233Sy9ZKnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Mike,
> from experience I agree. Replication enters the servername in several
> system
> tables, which then breaks it if you move the publisher to another,
> differently named server. Most people script out the publication, move the
> publishing database to the new server, modify the script with the new
> servername and logins and recreate the publication on the new server. You
> may be able to do a nosync initialization in this case, provided you
> synchronize before moving the database and prevent any modifications.
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
I'm running SQL Server 2000 (sp3a) and am moving the installation to a new
machine one database at a time (by using Backup/Restore). The new machine
and SQL installation also has a new name that is being kept.
The databases that I've moved thus far are not replicated databases so it's
been easy.
I'm uncertain about this approach when it comes to replicated databases. I
can find no information on Microsoft's site when it comes to moving a
"replicated" database other than a mention of it in one of their KB
articles, and it says something like "Find and read more information about
moving replicated databases", and that's it.
All input is welcome.
Thanks in advance,
Mike
Mike,
are you moving the publisher or subscriber? Is the new box named the same?
Is it in the same domain/trust relationship? Are the service startup
accounts the same? These are all important variables in deciding which way
to do it, or if it is even possible.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Mike Lopez" <MichaelLopez@.Emmerel.com> wrote in message
news:eAaZ6gJnEHA.3564@.tk2msftngp13.phx.gbl...
> Hello.
> I'm running SQL Server 2000 (sp3a) and am moving the installation to a new
> machine one database at a time (by using Backup/Restore). The new machine
> and SQL installation also has a new name that is being kept.
> The databases that I've moved thus far are not replicated databases so
it's
> been easy.
> I'm uncertain about this approach when it comes to replicated databases. I
> can find no information on Microsoft's site when it comes to moving a
> "replicated" database other than a mention of it in one of their KB
> articles, and it says something like "Find and read more information about
> moving replicated databases", and that's it.
> All input is welcome.
> Thanks in advance,
> Mike
>
|||Hi Paul. Thanks for the response.
I'm moving the publisher. The new box is named differently. Same
domain/trust. Service startup accounts are the same (domain account)..
Furthermore the distributor is a separate machine. That machine acts as both
the distributor and subscriber machine.
In my humble opinion the difference in the machine names are the deal
breaker.
What's your thoughts?
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5V%23XrJnEHA.2864@.tk2msftngp13.phx.gbl...
> Mike,
> are you moving the publisher or subscriber? Is the new box named the same?
> Is it in the same domain/trust relationship? Are the service startup
> accounts the same? These are all important variables in deciding which way
> to do it, or if it is even possible.
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> "Mike Lopez" <MichaelLopez@.Emmerel.com> wrote in message
> news:eAaZ6gJnEHA.3564@.tk2msftngp13.phx.gbl...
> it's
>
|||Mike,
from experience I agree. Replication enters the servername in several system
tables, which then breaks it if you move the publisher to another,
differently named server. Most people script out the publication, move the
publishing database to the new server, modify the script with the new
servername and logins and recreate the publication on the new server. You
may be able to do a nosync initialization in this case, provided you
synchronize before moving the database and prevent any modifications.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi again, Paul.
Yeah, that's pretty much what I was thinking.
I didn't realize that it was possible to do a nosynch initialization. That's
good because two of these databases are huge, and I spent a weekend setting
them up for replication the first time.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%233Sy9ZKnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Mike,
> from experience I agree. Replication enters the servername in several
> system
> tables, which then breaks it if you move the publisher to another,
> differently named server. Most people script out the publication, move the
> publishing database to the new server, modify the script with the new
> servername and logins and recreate the publication on the new server. You
> may be able to do a nosync initialization in this case, provided you
> synchronize before moving the database and prevent any modifications.
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Saturday, February 25, 2012
Moving MSDE SQL Server with OSQL
I am a complete neophyt to SQL. I am attempting to restore a backup from a
MSDE 2000 SQL server from a peer to peer server to a server running Small
Business Server 2003 using OSQL. (In other words I’m moving the database to
a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
‘PATHNAME’ WITH REPLACE
OSQL responded with “RESTORE DATABASE successfully processed xxx pages” Now
I need to run some stored procedures to fix the orphaned user issue, but when
I attempt to login to the database with user “sa” no matter what password I
use I can’t get in. (I’m not sure there is a user “sa” in the restored
database). From what I’ve read, I believe a may need to run the
sp_changedbowner command, but I’m not sure what login I need to change to.
Also, I DID NOT move any "system files" ie master.mdf or model.mdf files. Do
these have to be moved as well? Any suggestions would be greatly
appreciated. Thanks in advance.
Mark G
First, you need to know the new SQL Server (on your Smaill Business
Server2003) has mixed security mode enabled (Only Windows security is
enabled by default installation). If not, you would obviously cannot use
"sa" ( and cannot use any username/password pair in that matter).
To use OSQL.exe tool, you do not have to use "sa" though, you can use -E
swicth with osql.exe to log into SQL Server with trusted connection (Windows
Authentication). Of course you need to log onto your computer as local
admin.
"Mark Grantom" <mgrantom@.swbell.net[no spam]> wrote in message
news:D218EA2E-9D0B-4372-8E22-A58E8FBA1F96@.microsoft.com...
>I am a complete neophyt to SQL. I am attempting to restore a backup from a
> MSDE 2000 SQL server from a peer to peer server to a server running Small
> Business Server 2003 using OSQL. (In other words I'm moving the database
> to
> a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
> 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx pages"
> Now
> I need to run some stored procedures to fix the orphaned user issue, but
> when
> I attempt to login to the database with user "sa" no matter what password
> I
> use I can't get in. (I'm not sure there is a user "sa" in the restored
> database). From what I've read, I believe a may need to run the
> sp_changedbowner command, but I'm not sure what login I need to change to.
> Also, I DID NOT move any "system files" ie master.mdf or model.mdf files.
> Do
> these have to be moved as well? Any suggestions would be greatly
> appreciated. Thanks in advance.
> --
> Mark G
|||hi Mark,
Mark Grantom wrote:
> I am a complete neophyt to SQL. I am attempting to restore a backup
> from a MSDE 2000 SQL server from a peer to peer server to a server
> running Small Business Server 2003 using OSQL. (In other words I'm
> moving the database to a dedicated server.) I performed a RESTORE
> DATABASE xxx FROM DISK= 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx
> pages" Now I need to run some stored procedures to fix the orphaned
> user issue, but when I attempt to login to the database with user
> "sa" no matter what password I use I can't get in. (I'm not sure
> there is a user "sa" in the restored database). From what I've read,
> I believe a may need to run the sp_changedbowner command, but I'm not
> sure what login I need to change to. Also, I DID NOT move any "system
> files" ie master.mdf or model.mdf files. Do these have to be moved
> as well? Any suggestions would be greatly appreciated. Thanks in
> advance.
chances are the problem is related to authentication... MSDE installs by
default only allowing trusted connections and not allowing mixed security
connections relating to SQL Server logins and password credentials.. if
this is the case, you can have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
info about how to "hack" the Windows registry to allow trusted and mixed
security authenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||OK, I forgot to say, I did log in with entrusted option to do the initial
RESTORE. I can change to the mixed login option by modifying the registry no
problem. Do I then move the "system files" or not. If so, how? Thanks
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> chances are the problem is related to authentication... MSDE installs by
> default only allowing trusted connections and not allowing mixed security
> connections relating to SQL Server logins and password credentials.. if
> this is the case, you can have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
> info about how to "hack" the Windows registry to allow trusted and mixed
> security authenticated connections..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> OK, I forgot to say, I did log in with entrusted option to do the
> initial RESTORE. I can change to the mixed login option by modifying
> the registry no problem. Do I then move the "system files" or not.
> If so, how? Thanks
why do you like to "move" system database as well? transferring system
databases between different instances is not a very safe operation.. I'd
not...
you've better "stay" with the new instance ones... you "only" have to
re-create all the "original" desired/required logins and remap them to your
user's databases via that system stored procedure you already mentionned,
so_change_users_login..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I went back and read my messages twice. I don't "like" to move system
databases. In fact I have NEVER moved a system database. I'm not entirely
certain WHAT a system database is although I do have my suspicions. Remember
I am a rank beginner with SQL. I did ask twice, whether I NEEDED to move
system databases. I take it from your response that the answer is NO? Also,
I checked my system and the "mixed" login mode was already enabled in the
registry. I don't believe that I have a user "sa". How do I create this
user? Thanks.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> why do you like to "move" system database as well? transferring system
> databases between different instances is not a very safe operation.. I'd
> not...
> you've better "stay" with the new instance ones... you "only" have to
> re-create all the "original" desired/required logins and remap them to your
> user's databases via that system stored procedure you already mentionned,
> so_change_users_login..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I went back and read my messages twice. I don't "like" to move system
> databases. In fact I have NEVER moved a system database. I'm not
> entirely certain WHAT a system database is although I do have my
> suspicions. Remember I am a rank beginner with SQL. I did ask
> twice, whether I NEEDED to move system databases. I take it from
> your response that the answer is NO?
you usually do not have to... when you installed the "new" SQL Server/MSDE
instance it installed with it's own system databases... so you are ok with
them..
> Also, I checked my system and
> the "mixed" login mode was already enabled in the registry. I don't
> believe that I have a user "sa". How do I create this user? Thanks.
"sa" login can not be created... it's created at installation time.. and you
have it for sure
what is the exact exception you are reported with when connecting using "sa"
credentials?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I get:
Login failed for user 'sa'.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> you usually do not have to... when you installed the "new" SQL Server/MSDE
> instance it installed with it's own system databases... so you are ok with
> them..
> "sa" login can not be created... it's created at installation time.. and you
> have it for sure
> what is the exact exception you are reported with when connecting using "sa"
> credentials?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I get:
> Login failed for user 'sa'.
with something else, like "not associated with a trusted connection"?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
MSDE 2000 SQL server from a peer to peer server to a server running Small
Business Server 2003 using OSQL. (In other words I’m moving the database to
a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
‘PATHNAME’ WITH REPLACE
OSQL responded with “RESTORE DATABASE successfully processed xxx pages” Now
I need to run some stored procedures to fix the orphaned user issue, but when
I attempt to login to the database with user “sa” no matter what password I
use I can’t get in. (I’m not sure there is a user “sa” in the restored
database). From what I’ve read, I believe a may need to run the
sp_changedbowner command, but I’m not sure what login I need to change to.
Also, I DID NOT move any "system files" ie master.mdf or model.mdf files. Do
these have to be moved as well? Any suggestions would be greatly
appreciated. Thanks in advance.
Mark G
First, you need to know the new SQL Server (on your Smaill Business
Server2003) has mixed security mode enabled (Only Windows security is
enabled by default installation). If not, you would obviously cannot use
"sa" ( and cannot use any username/password pair in that matter).
To use OSQL.exe tool, you do not have to use "sa" though, you can use -E
swicth with osql.exe to log into SQL Server with trusted connection (Windows
Authentication). Of course you need to log onto your computer as local
admin.
"Mark Grantom" <mgrantom@.swbell.net[no spam]> wrote in message
news:D218EA2E-9D0B-4372-8E22-A58E8FBA1F96@.microsoft.com...
>I am a complete neophyt to SQL. I am attempting to restore a backup from a
> MSDE 2000 SQL server from a peer to peer server to a server running Small
> Business Server 2003 using OSQL. (In other words I'm moving the database
> to
> a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
> 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx pages"
> Now
> I need to run some stored procedures to fix the orphaned user issue, but
> when
> I attempt to login to the database with user "sa" no matter what password
> I
> use I can't get in. (I'm not sure there is a user "sa" in the restored
> database). From what I've read, I believe a may need to run the
> sp_changedbowner command, but I'm not sure what login I need to change to.
> Also, I DID NOT move any "system files" ie master.mdf or model.mdf files.
> Do
> these have to be moved as well? Any suggestions would be greatly
> appreciated. Thanks in advance.
> --
> Mark G
|||hi Mark,
Mark Grantom wrote:
> I am a complete neophyt to SQL. I am attempting to restore a backup
> from a MSDE 2000 SQL server from a peer to peer server to a server
> running Small Business Server 2003 using OSQL. (In other words I'm
> moving the database to a dedicated server.) I performed a RESTORE
> DATABASE xxx FROM DISK= 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx
> pages" Now I need to run some stored procedures to fix the orphaned
> user issue, but when I attempt to login to the database with user
> "sa" no matter what password I use I can't get in. (I'm not sure
> there is a user "sa" in the restored database). From what I've read,
> I believe a may need to run the sp_changedbowner command, but I'm not
> sure what login I need to change to. Also, I DID NOT move any "system
> files" ie master.mdf or model.mdf files. Do these have to be moved
> as well? Any suggestions would be greatly appreciated. Thanks in
> advance.
chances are the problem is related to authentication... MSDE installs by
default only allowing trusted connections and not allowing mixed security
connections relating to SQL Server logins and password credentials.. if
this is the case, you can have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
info about how to "hack" the Windows registry to allow trusted and mixed
security authenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||OK, I forgot to say, I did log in with entrusted option to do the initial
RESTORE. I can change to the mixed login option by modifying the registry no
problem. Do I then move the "system files" or not. If so, how? Thanks
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> chances are the problem is related to authentication... MSDE installs by
> default only allowing trusted connections and not allowing mixed security
> connections relating to SQL Server logins and password credentials.. if
> this is the case, you can have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
> info about how to "hack" the Windows registry to allow trusted and mixed
> security authenticated connections..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> OK, I forgot to say, I did log in with entrusted option to do the
> initial RESTORE. I can change to the mixed login option by modifying
> the registry no problem. Do I then move the "system files" or not.
> If so, how? Thanks
why do you like to "move" system database as well? transferring system
databases between different instances is not a very safe operation.. I'd
not...
you've better "stay" with the new instance ones... you "only" have to
re-create all the "original" desired/required logins and remap them to your
user's databases via that system stored procedure you already mentionned,
so_change_users_login..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I went back and read my messages twice. I don't "like" to move system
databases. In fact I have NEVER moved a system database. I'm not entirely
certain WHAT a system database is although I do have my suspicions. Remember
I am a rank beginner with SQL. I did ask twice, whether I NEEDED to move
system databases. I take it from your response that the answer is NO? Also,
I checked my system and the "mixed" login mode was already enabled in the
registry. I don't believe that I have a user "sa". How do I create this
user? Thanks.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> why do you like to "move" system database as well? transferring system
> databases between different instances is not a very safe operation.. I'd
> not...
> you've better "stay" with the new instance ones... you "only" have to
> re-create all the "original" desired/required logins and remap them to your
> user's databases via that system stored procedure you already mentionned,
> so_change_users_login..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I went back and read my messages twice. I don't "like" to move system
> databases. In fact I have NEVER moved a system database. I'm not
> entirely certain WHAT a system database is although I do have my
> suspicions. Remember I am a rank beginner with SQL. I did ask
> twice, whether I NEEDED to move system databases. I take it from
> your response that the answer is NO?
you usually do not have to... when you installed the "new" SQL Server/MSDE
instance it installed with it's own system databases... so you are ok with
them..
> Also, I checked my system and
> the "mixed" login mode was already enabled in the registry. I don't
> believe that I have a user "sa". How do I create this user? Thanks.
"sa" login can not be created... it's created at installation time.. and you
have it for sure
what is the exact exception you are reported with when connecting using "sa"
credentials?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I get:
Login failed for user 'sa'.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> you usually do not have to... when you installed the "new" SQL Server/MSDE
> instance it installed with it's own system databases... so you are ok with
> them..
> "sa" login can not be created... it's created at installation time.. and you
> have it for sure
> what is the exact exception you are reported with when connecting using "sa"
> credentials?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I get:
> Login failed for user 'sa'.
with something else, like "not associated with a trusted connection"?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
Moving MSDB to new location in SQL2000--Urgent
HI all,
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
Suchi
I believe that KB 224071 has explicit instructions for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
Suchi
I believe that KB 224071 has explicit instructions for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
Moving MSDB to new location in SQL2000--Urgent
HI all,
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
SuchiI believe that KB 224071 has explicit instructions for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
SuchiI believe that KB 224071 has explicit instructions for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
Moving MSDB to new location in SQL2000--Urgent
HI all,
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
SuchiI believe that KB 224071 has explicit instructions for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
what is best way to move msdb to new loctaion (from F to E drive)on the
same system in sql server2000?
Shall I use restore command instead of attaching and detaching...
Thanks in advance..
SuchiI believe that KB 224071 has explicit instructions for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:F096E1C9-CDDF-4A6B-BFA2-62415245E612@.microsoft.com...
> HI all,
> what is best way to move msdb to new loctaion (from F to E drive)on the
> same system in sql server2000?
> Shall I use restore command instead of attaching and detaching...
> Thanks in advance..
> Suchi
>
Monday, February 20, 2012
Moving logshipping target database (sql2000) without recovery
We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
simply use SqlLiteSpeed's version of restore log with standby.
Our recipient databases of log shipping sit over a WAN on our old server.
Due to changing database sizes I needed to move one of the data files. I
assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
However I could not restore any logs after the sp_attach_db as this appears
to recover the database and leave it in read/write mode.
Luckily the first database I tried to move was the smallest and I will be
able to re-initialise the logshipping over a few hours.
Is there another way to move the data files without recovering the database?
Could it be done by starting up with -m and updating tables?
Regards
Paul CahillYou are out of luck.
What you are looking for is "Attach with NoRecovery", a feature that doesn't
exist yet.
I have a Connect item requesting this feature which was closed due to
duplication. Unfortunately, Microsoft has not seen fit to tell me the
duplicate Connect item.
I do have reason to believe it is being considered for the SQL 2008 release,
but that won't help you much now.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
> simply use SqlLiteSpeed's version of restore log with standby.
> Our recipient databases of log shipping sit over a WAN on our old server.
> Due to changing database sizes I needed to move one of the data files. I
> assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
> However I could not restore any logs after the sp_attach_db as this
> appears to recover the database and leave it in read/write mode.
> Luckily the first database I tried to move was the smallest and I will be
> able to re-initialise the logshipping over a few hours.
> Is there another way to move the data files without recovering the
> database? Could it be done by starting up with -m and updating tables?
> Regards
> Paul Cahill
>
>|||Cheers for the info Geoff. I might have to do this with drive letters. There
will be too much log build up to resync this easily. I sync'd the machines
when they were on the both on the lan.
Paul
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> You are out of luck.
> What you are looking for is "Attach with NoRecovery", a feature that
> doesn't exist yet.
> I have a Connect item requesting this feature which was closed due to
> duplication. Unfortunately, Microsoft has not seen fit to tell me the
> duplicate Connect item.
> I do have reason to believe it is being considered for the SQL 2008
> release, but that won't help you much now.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old server.
>> Due to changing database sizes I needed to move one of the data files. I
>> assumed sp_detach_db and sp_attach_db would be the easiest way to do
>> this. However I could not restore any logs after the sp_attach_db as this
>> appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will be
>> able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>|||If they are physically close, a USB drive might do the trick to transfer the
snapshot. SneakerNet on Steroids.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.hotmail.com> wrote in message
news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Cheers for the info Geoff. I might have to do this with drive letters.
> There will be too much log build up to resync this easily. I sync'd the
> machines when they were on the both on the lan.
> Paul
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>|||I'll have to get the motorcycle out one last time before winter bites.
Cheers Geoff
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ujhmrgYIIHA.3356@.TK2MSFTNGP02.phx.gbl...
> If they are physically close, a USB drive might do the trick to transfer
> the snapshot. SneakerNet on Steroids.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Cahill" <anon@.hotmail.com> wrote in message
> news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Cheers for the info Geoff. I might have to do this with drive letters.
>> There will be too much log build up to resync this easily. I sync'd the
>> machines when they were on the both on the lan.
>> Paul
>>
>>
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write
>> mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Anyone know if this would work?
Paul
An extract from http://support.microsoft.com/kb/224071/en-us
SQL Server 2005 and SQL Server 2000
In SQL Server 2005 and in SQL Server 2000, you cannot detach system
databases by using the sp_detach_db stored procedure. When you try to run
the sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c
option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL
Server from recovering any database except the master database.
Note You will not be able to access any user databases after you do this.
You must not perform any operations, other than the following steps, while
you use this trace flag. To add trace flag 3608 as a SQL Server startup
parameter, follow these steps: 1. In SQL Server Enterprise Manager,
right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-c -m -T3608
simply use SqlLiteSpeed's version of restore log with standby.
Our recipient databases of log shipping sit over a WAN on our old server.
Due to changing database sizes I needed to move one of the data files. I
assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
However I could not restore any logs after the sp_attach_db as this appears
to recover the database and leave it in read/write mode.
Luckily the first database I tried to move was the smallest and I will be
able to re-initialise the logshipping over a few hours.
Is there another way to move the data files without recovering the database?
Could it be done by starting up with -m and updating tables?
Regards
Paul CahillYou are out of luck.
What you are looking for is "Attach with NoRecovery", a feature that doesn't
exist yet.
I have a Connect item requesting this feature which was closed due to
duplication. Unfortunately, Microsoft has not seen fit to tell me the
duplicate Connect item.
I do have reason to believe it is being considered for the SQL 2008 release,
but that won't help you much now.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
> simply use SqlLiteSpeed's version of restore log with standby.
> Our recipient databases of log shipping sit over a WAN on our old server.
> Due to changing database sizes I needed to move one of the data files. I
> assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
> However I could not restore any logs after the sp_attach_db as this
> appears to recover the database and leave it in read/write mode.
> Luckily the first database I tried to move was the smallest and I will be
> able to re-initialise the logshipping over a few hours.
> Is there another way to move the data files without recovering the
> database? Could it be done by starting up with -m and updating tables?
> Regards
> Paul Cahill
>
>|||Cheers for the info Geoff. I might have to do this with drive letters. There
will be too much log build up to resync this easily. I sync'd the machines
when they were on the both on the lan.
Paul
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> You are out of luck.
> What you are looking for is "Attach with NoRecovery", a feature that
> doesn't exist yet.
> I have a Connect item requesting this feature which was closed due to
> duplication. Unfortunately, Microsoft has not seen fit to tell me the
> duplicate Connect item.
> I do have reason to believe it is being considered for the SQL 2008
> release, but that won't help you much now.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old server.
>> Due to changing database sizes I needed to move one of the data files. I
>> assumed sp_detach_db and sp_attach_db would be the easiest way to do
>> this. However I could not restore any logs after the sp_attach_db as this
>> appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will be
>> able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>|||If they are physically close, a USB drive might do the trick to transfer the
snapshot. SneakerNet on Steroids.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.hotmail.com> wrote in message
news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Cheers for the info Geoff. I might have to do this with drive letters.
> There will be too much log build up to resync this easily. I sync'd the
> machines when they were on the both on the lan.
> Paul
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>|||I'll have to get the motorcycle out one last time before winter bites.
Cheers Geoff
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ujhmrgYIIHA.3356@.TK2MSFTNGP02.phx.gbl...
> If they are physically close, a USB drive might do the trick to transfer
> the snapshot. SneakerNet on Steroids.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Cahill" <anon@.hotmail.com> wrote in message
> news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Cheers for the info Geoff. I might have to do this with drive letters.
>> There will be too much log build up to resync this easily. I sync'd the
>> machines when they were on the both on the lan.
>> Paul
>>
>>
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write
>> mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Anyone know if this would work?
Paul
An extract from http://support.microsoft.com/kb/224071/en-us
SQL Server 2005 and SQL Server 2000
In SQL Server 2005 and in SQL Server 2000, you cannot detach system
databases by using the sp_detach_db stored procedure. When you try to run
the sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c
option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL
Server from recovering any database except the master database.
Note You will not be able to access any user databases after you do this.
You must not perform any operations, other than the following steps, while
you use this trace flag. To add trace flag 3608 as a SQL Server startup
parameter, follow these steps: 1. In SQL Server Enterprise Manager,
right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-c -m -T3608
Subscribe to:
Posts (Atom)