Import and Export copies of .mdf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Import and Export copies of .mdf

Author

Topic

groth.j
Starting Member

6 Posts

Posted - 2008-02-06 : 15:37:51

I work for a Geographic Information Systems contractor and we are new to SQL 2005. One of our clients sent us a copy of an .mdf file. I have tried to attach the file but they did not send us the associated log file. What is the most efficient way for us to exchange copies of SQL 2005 databases with clients. Most of our clients are novices with SQL 2005 also.

Thanks,
Jeff

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-06 : 16:32:10

Do you want the whole database or just tables? One good way is to do a backup to a file and then you can take that file and restore it to any server. One problem that you might run into is that of database users. If you are using Windows Authentication it may not be an issue. I don't know how cross domain accounts will react, however. If you are using SQL Server authentication, you will have to sync the users. The following script should be sufficient for this purpose:

Declare @Users_stmt varchar(75)
Declare Users_Cursor Cursor For
Select 'sp_change_users_login ''auto_fix'', ''' + [Name] + ''''
From sysusers Where (Status = 2 Or Status = 14) And UID <> 1

Open Users_Cursor
Fetch Next From Users_Cursor Into @Users_stmt

While @@FETCH_STATUS = 0 Begin
Exec (@Users_stmt)
Fetch Next From Users_Cursor Into @Users_stmt
End
Close Users_Cursor
Deallocate Users_Cursor

Run this on the newly restored database.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-07 : 00:22:25

Tried attach it with sp_attach_single_file_db?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources