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

[Mark Breen]

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
>
>
>
> 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>