I've tried to search for this answer, but every answer takes me to a T-SQL or SSMS kind of answer without really addressing the YES or NO of the question. Perhaps its indirectly answering my question by virtue that I cannot find the direct answer.

QUESTION: Is it possible to shut down an instance of SQL Server (2017 Ent if this makes a difference) and simply copy (as in robocopy/Windows explorer or similar) the data files out to a different location and consider that a full backup?

Coming from an Oracle background, this was considered a viable strategy.

TIA

CodePudding user response：

Is it possible to shut down an instance of SQL Server (2017 Ent if this makes a difference) and simply copy (as in robocopy/Windows explorer or similar) the data files out to a different location and consider that a full backup?

Yes, but it's a bad idea and nobody does it that way. The capability is basically covered here: Move User Databases, and it technically possible and supported.

There was a significant period of time when Oracle didn't support online backups*, so a practice of taking cold backups persisted for a long time.

SQL Server has supported online backups since basically forever. Plus Windows allows you to make a point-in-time consistent copy of an online volume, and coordinate the copying of online volumes across the whole server using the Volume Shadow Copy Service.

So most people either

1. Use TSQL's Backup Database command (DBA's tend to favor this)
2. Use some infrastructure backup tool that takes volume snapshots and log backups (sysadmins tend to favor this)

So while it's technically possible to use cold backups in SQL Server, it's very easy not to and Cold Backups are worse in absolutely every way than SQL Server native backpus.

*Oracle historically ran on a large number of different OSs and filesystems, and so I imagine knowing which database blocks were safely persisted, and which might be in some sort of write-through cache was not always possible in the old days. SQL Server, through its tight integration with Windows, forces all IO writes to go directly to disk, so it knows exactly which pages a backup will get when it reads directly from the files, and what window of log records need to be added to the backup file to bring those pages-on-disk up to the point-in-time at which the backup completes.