1

We are migrating our SQL Server databases (various versions) from source Windows servers to new Windows servers. These are to be done by taking snapshot backups of of the servers. Does this server backup method take in a consistent copy of a SQL Server instance and its databases?

Is there any work that will be required to complete restoration of the SQL Server databases or does everything get restored as is on the source server?

I do not have experience in this area, what I know is to backup and restore the database, I would appreciate some guidance on this method of migration please.

Improve this question
4
  • HI, welcome to the forum. Please tag your SQL Server version.
    – McNets
    Commented Mar 9, 2021 at 22:20
  • What do you mean by snapshot backups? Snapshots and backups are usually two different things. What tool are you using to do that process? Are you migrating from physical server to virtual server?
    – Ronaldo
    Commented Mar 10, 2021 at 0:23
  • Do you mean BACKUP database WITH SNAPSHOT?
    – MBuschi
    Commented Mar 10, 2021 at 13:39
  • 1
    In general I would recommend either building new SQL Servers and transfering the databases with SQL Server backup/restore, or using a Virtualization tool transform the source servers into Virtual Machines. That way you can move them between hosts at will.
    – David Browne - Microsoft
    Commented Mar 11, 2021 at 17:44

2 Answers 2

Reset to default
1

A backup of the entire server will be inclusive of the SQL Server instance and the database files as a snapshot in time at that moment. This is assuming that the database files live on the server itself that you're taking a backup of, as it is possible to setup the database's such that their files live on remote shares.

You can verify the location of your databases' files in SQL Server Management Studio, by right clicking on a specific database, clicking Properties, and then navigating to the Files page, then looking at the Path and File Name properties.

For example: Database File Properties Example

In the above, my Test database's files are stored in the default SQL instance folder. Please ensure the drives that your database files live on are part of the server / snapshot being taken of it, as even mapped drives can have drive letters but point to remote shares.

There shouldn't be any additional work on the destination server, but you may want to make sure the appropriate SQL services (in the Windows Services) are started and running just the same as on the source server.

For example: Windows SQL Services

The above is my test instance which probably has a lot more components of SQL Server installed than yours will, so don't take this as a definitive list, rather it's an example. Compare against your source server to the destination server.

The only other thing I can think of is to ensure all the same Windows permissions are setup to the correct accounts on your destination server as its respective source server. For example, if you're using the SQL Agent, or a feature like Replication, those might leverage Windows accounts that currently have special permissions setup on your source server which need to be replicated on your destination server. (This is a little outside the scope of DBA.StackExchange though, and might be more of a ServerFault question, if you need help with that.)

Improve this answer
11
  • 1
    this is extremely helpful and very much appreciated, thank you.
    – PTL_SQL
    Commented Mar 9, 2021 at 23:47
  • J.D. you mentioned databases could be located on a share on the network, but @PTL_SQL could also be using iSCSI connections and remote disks would appear to be local disks on the server. If that would be the case, the snapshots or backups of the server could not contain the database files.
    – Ronaldo
    Commented Mar 10, 2021 at 0:20
  • 1
    @Ronaldo Yup, precisely. I'll update my answer to mention that as it really depends on where the database files live and the server backup tools being used.
    – J.D.
    Commented Mar 10, 2021 at 0:33
  • 1
    @J.D, just seen your comment, thank you.
    – PTL_SQL
    Commented Apr 6, 2021 at 22:47
  • 1
    @Ronaldo, thanks for your comments.
    – PTL_SQL
    Commented Apr 6, 2021 at 22:49
1

So after the "restoration" you have an exact image of the source? Absolutely no changes, whatsoever? Then from a SQL Server point of view, there's nothing to do. It is, after all, the same machine!

However, you don't want to produce the snapshot while SQL Server is running unless your "snapshot software" (whatever you are using) communicated with SQL Server so SQL server know it will be a snapshot takes and will stop doing I/O while the snapshot is being established. You will see messages in the errorlog file, something like "I/O was frozen for Database A", etc and then also "...resuming..." messages after a few seconds.

If you rename your machine you should at least run sp_dropserver and sp_addserver so that your SQL server understand the new name. There can be other things as well, depending on what SQL server stuff you are using (RS, AG, replication etc).

Improve this answer
1
  • thanks for your response, appreciated. I don't know if you got my previous acknowledgement
    – PTL_SQL
    Commented Apr 7, 2021 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.