[dba-SQLServer] Moving database (was: Schemas for separation of clients?)

[Gustav Brock]

Hi John

I think they define the physical location (local path) and file names of the mdf and ldf files.

If moving/copying a database from one server to another, the path may very well be different.
If copying a database to a new on the same server, either the path(s) or the filename(s) has to be different.

/gustav

-----Oprindelig meddelelse-----
Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af John W. Colby
Sendt: 1. april 2015 16:04
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Moving database (was: Schemas for separation of clients?)

Gustav,

Not sure how you are going to get simpler.  What exactly do the two moves do?

John W. Colby

On 4/1/2015 9:12 AM, Gustav Brock wrote:
> Hi Mark
>
> In SSMS the full version number is also displayed when the connect to the server instance:
>
>      The AWS server is not 2014 but 10.50..4297
>      My in-house server is 10.50.2550
>
> It brings some kind of relief and helps not to lose ones self-esteem to learn, that I'm not the first to find this process to be overly complicated:
>
>      
> http://stackoverflow.com/questions/2043726/best-way-to-copy-a-database
> -sql-server-2008
>
> but that and this:
>
>      https://msdn.microsoft.com/en-us/library/ms190447.aspx
>
> pushed me in "a" direction that turned into success:
>
> So what I did was these three steps:
>   - create a full backup of the database on the local server
>   - on the AWS machine, copy this file from the RDP connected drive from my LAN to the backup folder on the AWS machine
>   - run this script in SSMS 2008 R2 on the AWS machine:
>
>      RESTORE database MyDatabaseName FROM disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ MyDatabaseName.bak'
>      with recovery,
>      move 'MyDatabaseName ' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ MyDatabaseName.mdf',
>      move 'MyDatabaseName_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabaseName_log.ldf'
>
> The first step is fast, the third step is very fast, while the second step takes some time determined by your upload speed.
>
> With no further actions I could relink my tables to the database running on the AWS instance.
>
> However, I would still like to learn of any simpler method.
>
> /gustav
>
>
> -----Oprindelig meddelelse-----
> Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] 
> På vegne af Mark Breen
> Sendt: 30. marts 2015 20:44
> Til: Discussion concerning MS SQL Server
> Emne: Re: [dba-SQLServer] Schemas for separation of clients?
>
> Hello Gustav
>
> definitely not.  That is a real hassle and you are never sure you have 100% of what you had previously.
>
> 1) check your db version again "Select @@Version"
> 2) check your compatibility mode on your two machines
> 3) spin up another amazon box and see can you restore to 2014 and work 
> down from there
>
> it will work
>
>
>
> On 31 March 2015 at 01:40, Gustav Brock <gustav at cactus.dk> wrote:
>
>> Hi Mark
>>
>> That's what I thought. But the straight backup complains that it is 
>> restoring to a different server (which is correct), then stops - and 
>> another method claims that it cannot restore to a 2005 or newer(!) 
>> version, which is nonsense but I binged that to be a way of telling 
>> you, that it will not backup data from a Standard version to an Express version.
>>
>> I can copy the data, but then all indexes and relations are lost.
>>
>> A third method (can't recall which at this confusing moment) stops 
>> because it cannot write the timestamps to read-only fields.
>>
>> Do I really need to fire up the data migration Tool?
>>
>> /gustav
>>
>> ________________________________________
>> Fra: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> på 
>> vegne af Mark Breen <marklbreen at gmail.com>
>> Sendt: 30. marts 2015 20:28
>> Til: Discussion concerning MS SQL Server
>> Emne: Re: [dba-SQLServer] Schemas for separation of clients?
>>
>> Hello Gustav,
>>
>> if your db is really the same version, it will simply restore a 
>> backup.  I am 100 % sure about that.  SQL standard to sql express is perfectly fine.
>> I have done it many times.  As you probably know, you cannot restore 
>> a newer version to an older version.
>>
>> Can you try the backup and restore using standard sql server backup.
>>
>>
>>
>> On 31 March 2015 at 00:21, Gustav Brock <gustav at cactus.dk> wrote:
>>
>>> Hi Mark
>>>
>>> How do you copy a database from an in-house SQL Server 2008 Standard 
>>> to the hosted SQL Server 2008 Express?
>>> I can copy and create the schema, but have had no luck finding a 
>>> method to copy either the data to the created schema or the complete database.
>>>
>>> I have used both SSMS 2008 on the AWS machine as well as SSMS 
>>> 2012/2014 on my own machines.
>>>
>>> There has to be a simple and proven method.
>>>
>>> /gustav