Unable to take a snapshot of a database which contain "-" in its name

Question

I have a SQL Server database named db-pwapp-lostandfound-dev, and I want to take a snapshot of it, but I am getting this error - seems like SQL Server does not like - in the name:

I tried to surround the name by [], but I still have the same issue:

Any advice ?

THE VERSION IS OF THE DATABASE IS "mICROSOFT sql AZURE rtm - 12.0.2000.8"

Thanks

Answer 1

The problem is not the hyphen. The problem is exactly the ON it's bickering about. When you create a database in SQL Azure, you cannot use the ON clause to specify names or location about the database files. All that is being taken care of you by the service.

Yes, the error message could have been clearer. If you try:

CREATE DATABASE db_snap AS SNAPSHOT OF yourdb

You at least the the error message:

Msg 40517, Level 16, State 1, Line 1
Keyword or statement option 'SNAPSHOT' is not supported in this version of SQL Server.

So, no, you cannot create a database snapshot on Azure SQL Database.

Answer 2

Hi,

so now my question is what is the best approach to take a copy/snapshot/backup of the existing azure sql database and its data of course + the ability to restore this copy if needed?thanks

I gave you the answer in stackoverflow :-)

Most of the people that help in the forums are the same in both system, but here is better in my opinion , as we can discuss and no limited to short poor text comments.

Please remember to close any thread that you open in all forums :-)

This is what I wrote there:

(1) Azure SQL databases are backed up automatically: full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes.

https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database#backup-frequency

You can view the backups and restore the database to any existing point in time: Go to the Azure logical SQL Server blade in the portal -> on the left menu click on "backups" -> in the list of databases, select the database which you want to restore -> fill the form and select the point in time which you want to get back

(2) In your case, according to your description, if you do not want to count on backups then your best option is probably simply to create a copy of the database -> make your test -> and if all works well as expeted then move back to the production version

To create a copy of the database you simply use the command (query which can execute directly from SSMS)

-- Execute on the master database to start copying  
CREATE DATABASE NewForTesting AS COPY OF LiveDatabaseme;  

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-the-same-server

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 3

so let say i create a copy >> perform the data extraction on the original Data >> test things on the original database>> if i found problems >> then can i restore back the original database from the copy?

I guess that the best alternative in that case is to use the Restore option in the portal where you can select to restore to a point in time, so you just make a note of when you started your extraction operation.

You will have to restore it under a new name and make the same configurations that you do when you create a new database. But I assume that you can drop the original database and rename the restored copy. With this approach, you would not use the copy you created with AS COPY OF.

But you could also drop you original database and then opt to restore a backup of the copy you took with AS COPY OF under the name of the original database.

But the option of a quick restore from a snapshot that you have on-prem is not available on Azure SQL Database.

Answer 4

Hi,

@Ronen Ariely so let say i create a copy >> perform the data extraction on the original Data >> test things on the original database>> if i found problems >> then can i restore back the original database from the copy?

I would probably do the opposite and make the test on your copy and not on the original database. Only if the test succeed, then I will do the same on the production.

In any case, you can always restore the database to the last point in time which the Azure created for you automatically and it is before you started the test - obviously this mean that if during the test you also had actions which are not part of the test then you will lose them.

(1) Make a copy of the DATABASE
(2) Make the test on the copy
(3) Id test succeed then make the same actions on the original database

Answering you question on stackoverflow, the answer is yes. You can do the test (NOT RECOMMENDED) on the original database using the following procedure (like you wrote)

(1) Make a copy of the DATABASE
(2) Do your test on the original DATABASE
(3) If test failed then DELETE original DATABASE and copy the copied database with the original name to create new DATABASE with the same name

But this make no send probably since your test and all this procedure will take more the 30 minutes probably so you can just use the restore option
(1) Do your test starting at time x and at time X+y
(2) Restore in time to the last backup before time X

All these options are not recommended in my opinion except the first one I wrote! Tests are never should be done on production. Make a copy -> test on the copy -> if succeed then do it on production

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin