Cannot see backups

SQL Server Administration
1

Hello, I installed SQL Server 2016 on my local workstation. I am trying to restore some databases so I can test the new software, however when I try to navigate (SSMS wizard for restore) to the folder where the backups are it does not show even when I change to "All Files". It does note even show the sub folders
Hey, I am a rebel and have the backups in "C:\SQLBackup", instead of "C:\Program Files\Microsoft SQL Server\MSSQL13.TEST2016A\MSSQL\Backup"

It does work when the file is in the ...MSSQL\Backup location.

So am I doing something wrong or is the a "feature"?

Thank you,
DJJ

2

I don't use SSMS much, certainly not for Restore, so I just gave it a go.

I can see a bunch of Backup (Full Diff AND TLog) files which I copied to a "just in case" folder some time back, so they aren't in the "expected" location. They are named "*.BAK" though.

Might be permissions? (although why copying to "C:\Program Files\Microsoft SQL Server\MSSQL13.TEST2016A\MSSQL\Backup" would change that I don't know - maybe they inherit "better" permissions if you stuff a file in that location?

3

I'm sure you know all this, but if you want to use a SQL Restore command here's my template:

-- Get Details of Logical names
RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'

RESTORE HEADERONLY FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'

USE master	-- (Can't sit in the database whilst its being Restored / Dropped!)
GO

ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

-- Restore Full Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'
	WITH
		REPLACE
**** Choose one: !!!
--		RECOVERY	-- Use if NO more file to recover
--		NORECOVERY	-- Use if there are DIFFs and/or T/Logs to recover
--		STANDBY = 'x:\MSSQL\DATA\MyDatabaseName.STB'	-- Use for STANDBY mode
		, STATS = 10	-- Show progress (every 10%)
**** Change the Logical Names here
	, MOVE 'MyDatabaseName_Data' TO 'x:\MSSQL\DATA\MyDatabaseName.mdf'
	, MOVE 'MyDatabaseName_Log' TO 'x:\MSSQL\DATA\MyDatabaseName.ldf'
GO

-- Optional restore Differential Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_Diff.BAK'
	WITH
**** Choose one: !!!
--		RECOVERY	-- Use if NO more file to recover
--		NORECOVERY	-- Use if there are T/Logs to recover
--		STANDBY = 'x:\MSSQL\DATA\MyDatabaseName.STB'	-- Use for STANDBY mode
		, STATS = 10	-- Show progress (every 10%)
GO

-- Optional restore Transaction Log Backup
RESTORE LOG MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_yyyymmdd_hhmm_Trans.BAK'
	WITH
**** Choose one: !!!
--		RECOVERY	-- Use if NO more T/Logs to recover
--		NORECOVERY	-- Use if more T/Logs to recover
--		STANDBY = 'x:\MSSQL\DATA\MyDatabaseName.STB'	-- Use for STANDBY mode
--		, STATS = 10	-- Show progress (every 10%)
-- On the LAST TLog restore optionally use:
--		, STOPAT = '19991231 23:59:59.999'
GO
-- REPEAT FOR EACH Transaction Log Backup file - in chronological order

-- Now activate the database (NOT required if RECOVERY was used earlier)
RESTORE DATABASE MyDatabaseName WITH RECOVERY
GO

-- Rename logical names (only needed if restoring from a backup for a Different database):
ALTER DATABASE MyDatabaseName 
**** Change the Logical Name here
		MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabaseName_data')
GO
ALTER DATABASE MyDatabaseName 
**** Change the Logical Name here
		MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabaseName_log')
GO

-- Optional - if backup-file was from a database set to SINGLE_USER or READ_ONLY
ALTER DATABASE MyDatabaseName SET MULTI_USER, READ_WRITE
GO
1 Like
4

Thanks @Kristen. I know I should use TSQL instead of the wizard but I do not do it often enough... (so much for weekly testing :flushed: )

I keep forgetting that just because you are on your local PC with admin rights, does not mean you have rights to do anything.

I will save your script in my toolbox.

5

When you installed SQL Server - the services were setup to use service SIDs. So even if the service is running as local system - it really is authenticated using the service SID account (e.g. NT SERVICE\MSSQLSERVER).

Permissions to the folders is assigned to the service SID account - and since that account does not have access to C:\SQLBackup it cannot find the files. Note: if you use a script it will error with an access denied message.

6

If the permissions on the files in C:\SQLBackup are set to "Everyone" doesn't that solve it?

I was kinda assuming that the files had come-from somewhere with tighter permissions, and the permissions had travelled with them ... but thinking about it that would mean that they wouldn't be accessible anywhere if the permissions were that "foreign"!

I've never had a problem accessing backup files in non-standard folders (i.e. on drives actually attached to the SQL box), and I can't remember having to fiddle with the permissions on them either. (Not that that matters in this case, just curious how I have not got caught out by SQL Server's Service Account permissions)

7

Well when I installed SQL2008R2 it could access the folder, but 2016 nope. SQL 2016 can see the folders that I did not create, so MS must have tightened the way security works. Now to set the permission on the folder and hope that works.

8

Okay, I really need a vacation. I found a solution, I set the default location (properties\database settings) to the desired location. I can now see the folder information.

9

I assume you mean "with no permission changes"? If so that's nuts isn't it!

But I've never liked doing this type of thing through the GUI ...

... I remember a very similar thread, when a much older version of SQL was first launched, and someone had a very similar "it has to be THIS way" brick-wall encounter with SSMS back then ...

10

@Kristen, I had a senior moment while trying your code. Could not get it to work. Tried several things. Finally realized I was connected to a different server. :sleeping:

1 Like