July 26, 2018 at 8:13 am
Hi, everyone.
Is there difference in performance, if I restore database from full backup in existing database. And if I delete database, delete .mdf, .ldf files then click on Databases > Restore > Database and then SSMS will create new database and restore it from backup. And what the difference between this two methods?
July 26, 2018 at 8:16 am
The second SQL has to go and create the files, which takes time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2018 at 12:41 pm
And the delete, while fast, is not free, so it's also adding a little to the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 26, 2018 at 11:20 pm
Thanks for answers.
And the second question, if I restore existing database, for example we have prod and test. Test database is old, it's not update every day. Data in it for 10-15 ago. And I want to restore database to this day from backup from prod database. My actions only Right click on database -> Tasks -> Restore database -> Choose backup location -> Insert values about database and mark OVERWRITE EXISTING DATABASE? Or I must do other actions to restore database without errors and problems? And must I take offline database or it will be go offline and nobody can't connect during restore?
Sorry for so simple question, I'm new in DBA.
July 27, 2018 at 1:33 am
I'd also advise checking that the account that is doing the restore has the ability to 'Perform Volume Maintenance', this will enable instant file initialisation.
A quick search for perform volume maintenance will show you how to add an account.
July 27, 2018 at 1:38 am
To answer you question about overwriting existing database, it will automatically overwrite it and the database will be in restoring state until complete.
But it will not do the restore if the database is in use, there is a 'Close existing connection' option just use that to ensure all connections are closed.
Finally not sure if your database is in the FULL recovery model in PROD but on DEV and TEST you probably want it in SIMPLE mode to avoid log file expansion due to no backups on DEV and TEST.
Just run this to change the recovery model.
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
July 27, 2018 at 2:15 am
In DEV and TEST many time I restore by sa user, but sometimes with Windows account. There isn't any problems during restoration. It's easy and secure for me, to restore from existing database, because when I delete database and data files manually, I can do mistake and delete other database files.
I restored from existing database and how you say, it said me that this database in use, but in activity monitor there aren't any connections. I took database offline and then did restore and everything went good.
About modes, you right. It restore in FULL mode, but sometimes when I have time, I check databases and change them to SIMPLE mode in DEV and TEST.
July 27, 2018 at 6:17 am
farik013 - Thursday, July 26, 2018 11:20 PMThanks for answers.And the second question, if I restore existing database, for example we have prod and test. Test database is old, it's not update every day. Data in it for 10-15 ago. And I want to restore database to this day from backup from prod database. My actions only Right click on database -> Tasks -> Restore database -> Choose backup location -> Insert values about database and mark OVERWRITE EXISTING DATABASE? Or I must do other actions to restore database without errors and problems? And must I take offline database or it will be go offline and nobody can't connect during restore?
Sorry for so simple question, I'm new in DBA.
Not a problem asking questions. It's what this place exists for. Happy to help.
Yes, you've got it right.
Now, the real trick, learn the T-SQL to do the restore. Then automate that through SQL Agent so that it just occurs without your manual intervention. Single best piece of advice I can give you, learn how to automate everything. Use the GUI only while learning. Do most of the work through T-SQL code or PowerShell code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 27, 2018 at 7:15 am
Yes, it will be good to do it by scripts. Because I'm new, I'm afraid of code and afraid to forget to check some options. And now for me GUI is better, because I can see what I do. Anyway I hope after some time I will write scripts.
Thanks a lot to everybody )
July 27, 2018 at 5:48 pm
farik013 - Friday, July 27, 2018 7:15 AMYes, it will be good to do it by scripts. Because I'm new, I'm afraid of code and afraid to forget to check some options. And now for me GUI is better, because I can see what I do. Anyway I hope after some time I will write scripts.
Thanks a lot to everybody )
When you are doing some of the work in the GUI, look up at the top towards the left and there is a Script button. You can click on that before you hit OK and it will generate the script of what you did in the GUI. That can help you learn more and in time, you may want to start using the scripts that get generated. You can just script and then hit Cancel and run the script instead.
Sue
July 28, 2018 at 11:24 pm
farik013 - Thursday, July 26, 2018 11:20 PMThanks for answers.And the second question, if I restore existing database, for example we have prod and test. Test database is old, it's not update every day. Data in it for 10-15 ago. And I want to restore database to this day from backup from prod database. My actions only Right click on database -> Tasks -> Restore database -> Choose backup location -> Insert values about database and mark OVERWRITE EXISTING DATABASE? Or I must do other actions to restore database without errors and problems? And must I take offline database or it will be go offline and nobody can't connect during restore?
Sorry for so simple question, I'm new in DBA.
You should also change select the exact database file on which you are going to overwrite.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply