June 20, 2004 at 7:49 am
Hello,
is it ok, to use Detach and Attach for Backup and Deployment.
to deploy a database by copy the .mdf and .ldf file and simply run the sp_attach_db stored procedure on the target server? Or are there reasons why a Attach might fail? When does it fail? What are the requirements that it does run successfull?
Thank you, Markus
June 20, 2004 at 8:25 am
It is very important that you remember to detach the database before copying the mdf and ldf files to another server. It is a common mistake to just stop the server, copy the files, and attempt to attach the database. Aside from that, there are no other requirements. You must also note that the database will be unavailable to users during the time you are copying the files to the other server(s).
Did I mention that you must detach the database before you can attach them, on the same or different server?
Peter Yeoh
Need smaller SQL2K backups? Try MiniSQLBackup
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
June 20, 2004 at 8:41 am
Thank you Peter. But what are the restrictions? Different SQL-Server Versions and so?
June 20, 2004 at 11:03 am
Thus far, we have never encountered any errors when moving between SQL2K versions i.e. from Developer Edition to Std, to Enterprise, and vice-versa. But then again, our databases do not use any features specific to SQL2K versions e.g. replication etc.
If you do attach a SQL 7 database to SQL2K server, the database will be upgraded automatically.
Peter Yeoh
Need smaller SQL2K backups? Try MiniSQLBackup
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
June 20, 2004 at 11:43 pm
For reverse deployment I've seen this done a number of places (like taking a copy of live db's and putting them into a dev environment). The only thing that I've had a problem with is logins getting out of sync (the UserID is different on the different SQL Servers).
fixing that problem is as easy as running:
sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern = 'TheSqlUsernameYouWantToFix'
As far as backing up, I would use a proper SQL backup routine scheduled to run regularly. If your not sure on how to do that, checkout the SQL Maintenance Wizard.
Julian Kuiters
juliankuiters.id.au
June 21, 2004 at 8:15 am
I have heard (at this site and other tech sites) that the 'new' server has to have the same level of patches as the 'old' server.
So if it's coming from a SQL Server 2000 with SP2, the new server has to have SQL Server 2000 with SP2 also.
-SQLBill
June 21, 2004 at 6:09 pm
Or to make sure your SQL Server isn't taken out by an old worm, make sure you have the latest patch SP3a installed
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Julian Kuiters
juliankuiters.id.au
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply