question

johnjohn-0472 avatar image
0 Votes"
johnjohn-0472 asked AnuragSharma-MSFT commented

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

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:

141026-s1.png

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

141027-s2.png

Any advice ?

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

Thanks


azure-sql-database
s2.png (40.4 KiB)
s1.png (39.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @johnjohn-0472, just wanted to check if you need any other details. If any answer helped, you can choose to mark it as 'Accept Answer'

0 Votes 0 ·
ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered johnjohn-0472 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog thanks for the very useful info >> 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

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered ErlandSommarskog converted comment to answer

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://docs.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://docs.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-the-same-server



14150-image.pngRonen Ariely
Personal Site | Blog | Facebook | Linkedin




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@pituach 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?

Thanks

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog now we are using Microsoft Power automate Flow to do the extraction and this Flow is already configured to use the active database.. now if i drop the active DB and then restore it (even if i use the same name), this will break the connection between the MS flow and the active DB ... so what I need , is to take a copy/backup/snapshot of the active DB before the data extraction >> do the extraction on the active DB >> test the result of the extraction on the active DB >> if i found some errors >> to restore the active DB from the cop/snapshot/backup which i did before the extraction.. is this possible? if this is not possible then what are the alternatives that we have?

0 Votes 0 ·

I am completely unfamiliar with Microsoft Power Automate Flow, but I am a little surprised if it would succumb because you restored a backup under a new name, and then renamed the restored copy to the name of the original database. My naïve assumption is that it would not even notice. But as I said, I have heard of that product before, so I don't know what it is up to.

You may want to ask this question in a modified form with a suitable tag added for Power Automate Flow.

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Hi,

@pituach 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


14150-image.pngRonen Ariely
Personal Site | Blog | Facebook | Linkedin


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.