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

[Gustav Brock]

Hi Mark

I believe I tried, but at the restore it claimed, that the server restoring to was not the same as that of the backup, which of course is true. I cannot rule out that I've missed something, though.

/gustav

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

Hello Gustav

Sorry if this is an annoying question.  Did you use the GUI in ssms to do the restore.

I only ever have to play with the file names if I am restoring a backup onto the machine I just did the back on, eg restoring mydb to mydb20150401

Anyway question is does the GUI take care of the restore.

Mark
On 1 Apr 2015 20:13, "Gustav Brock" <gustav at cactus.dk> 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