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

[Gustav Brock]

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