SQL Server Tips

Getting Started with ASP.NET MVC - Part 6: ASP.NET MVC and Entity Framework

First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.


	Just Learned

Upload Image

Close it

Select File

Learned something today? Share it, or learn from what others have learned today


	Like

Invite Friends

Tips · Submit A Tip · Members (427) · Preferences · Feedback · Most Liked · Most Learned · Most Discussed · Silver Rated

Be aware that the database is detached when copy database wizard is used

Feb 17 2012 12:00AM by vanne040

Earlier this week, one of the developers encountered issues when he tried to make a copy of the database on the server instance with a different name. He used the copy database wizard to perform this task. I was under the impression that he used the backup/restore process(which I normally use and hence forgot about copy wizard). When I checked the logs turned, I found that SSIS packages failed and wondered what they were. Later I confirmed if the developer was using copy wizard to setup a new database.

Copy database wizard was failing as it tried to detach the database when user connections were active. Personally, I don't recommend using this wizard and always used/use the backup and restore process.

During the investigation I found the following interesting things about copy database wizard -

Do not run the Copy Database Wizard if the database is suspect or if any other problems exist with the database that may prevent it from detaching or reattaching.
Problems such as I/O errors (for example, error 823), data integrity errors (for example, Table Corrupt), or any known hardware issues (for example, SCSI port errors or controller errors in the system event log), to name a few, may be an impediment to a successful reattachment of the database.
Make sure that the source database is not marked 'read only', 'offline', 'suspect', 'emergency', or 'loading' or the detach will fail. The detach would fail because the detach procedure must update the statistics in the database; if the database is in any way not accessible for writes, the statistics update fails.
The following error message is generated if the detach fails due to the database being in any unwritable status: "Failed to detach the database "

One good thing about this wizard is that the, Copy Database Wizard alleviates the issue of manually moving logins. The wizard identifies the logins corresponding to the database users and provides you with the option to move or copy those as part of moving or copying the database. Both SQL Server Standard authentication logins and Microsoft Windows NT and Microsoft Windows 2000 authentication logins are moved by the wizard.

To know more follow the useful link submitted with this post!

5 Comments

The Copy Database wizard can be run in 2 modes. The first is the attach/detach method, which may encounter the issues you highlight in this tip. The second method uses SMO and the source database remains online in this method. (http://beyondrelational.com/blogs/nakul/archive/2012/02/06/0132-sql-server-ssms-tutorial-part-07-database-administration-and-maintenance-productivity-enhancing-features-l200.aspx). Beware that the SMO is a much slower method when compared to the attach/detach method.

commented on Feb 18 2012 5:40AM .

Nakul Vachhrajani

6 · 26% · 5935

thanks for adding Nakul and also pointing out that SMO is a much slower method.

commented on Feb 18 2012 12:19PM .

vanne040

98 · 2% · 457

Folks, is it possible to backup a PROD database and restore the backup in another copy (different name)?

commented on Feb 21 2012 12:44PM .

sukhan

1205 · 0% · 10

@sukhan - you sure can! make sure that you change the mdf and ldf file names and locations during the restore if its on the same server.

commented on Feb 21 2012 5:25PM .

vanne040

98 · 2% · 457

@sukhan, yes, it is possible.

If you are continuously backing up your prod database with scheduled differential or transaction log backups, it's best to copy the BAK (and if using them, TRN) files, then restoring from the copied files. This will prevent a failed backup of your production database if you are restoring from a file that the prod backup expects to use.

Another option is to take a fresh backup using the COPY_ONLY option. This will not break the log chain for your regular backups.

If you are restoring your copy to the same server, you don't have to worry about logins. If you are restoring your copy to another server, you have to make sure that the appropriate logins are created on the target server. To discover the logins required by your database:

SELECT
    db.name as [db user], 
    sp.name as [login]
FROM 
    sys.database_principals db 
    	INNER JOIN sys.server_principals sp 
    	ON db.sid = sp.sid

When using Contained Databases under SQL Server 2012, this login step may not be necessary

commented on Feb 22 2012 6:54AM .

Marc Jellinek

119 · 2% · 359

Your Comment

Sign Up or Login to post a comment.

"Be aware that the database is detached when copy database wizard is used" rated 5 out of 5 by 5 readers